| Multiple data sets pie/bar charts [message #229749] |
Fri, 23 March 2007 03:42  |
Eclipse User |
|
|
|
Originally posted by: indianlakesolutions.rogers.com
In an ongoing project, I've built several reports that have approximately
10 rows each. Each row, has it's own label and it's own data set. The last
column of the row displays a total. My problem, is that I need to include
with each report, either a bar or pie chart. I can't seem to figure out
how to insert just the totals from each row, and also include the relevant
labels for each row for the either type of chart.
I'll answer the first question - At this point in time, I can't add or
modify any columns in the db. And, I didn't design the dB either.
Any help would be greatly appreciated.
Thanks.
|
|
|
| Re: Multiple data sets pie/bar charts [message #229800 is a reply to message #229749] |
Fri, 23 March 2007 09:25  |
| Eclipse User |
|
|
|
What do you mean, each row has its own dataset? One dataset consists of
multiple rows.
Thanks,
David
"Norm Garand" <indianlakesolutions@rogers.com> wrote in message
news:f81256bec9aac9efb3d57ce9b7b7aae0$1@www.eclipse.org...
> In an ongoing project, I've built several reports that have approximately
> 10 rows each. Each row, has it's own label and it's own data set. The last
> column of the row displays a total. My problem, is that I need to include
> with each report, either a bar or pie chart. I can't seem to figure out
> how to insert just the totals from each row, and also include the relevant
> labels for each row for the either type of chart.
>
> I'll answer the first question - At this point in time, I can't add or
> modify any columns in the db. And, I didn't design the dB either.
>
> Any help would be greatly appreciated.
>
> Thanks.
>
|
|
|
| Re: Multiple data sets pie/bar charts [message #229899 is a reply to message #229800] |
Fri, 23 March 2007 12:10 |
| Eclipse User |
|
|
|
Originally posted by: indianlakesolutions.rogers.com
I realize that normally a dataset has normally more than 1 row. But in
this instance, it doesn't. The following sql below generates 1 row. It
finds the number of occurrences for the ages between 1 & 10 and displays
them as occurrences per month. The situation is, that there are 9
different sql statements to grab each age group. I have to insert a label
in the display table for each row (i.e. Age 1 - 10). What I need to
display in either pie or bar chart is the final total per row and it's
corresponding label. Hopefully this is clearer than my original post.
SELECT
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 01
THEN 1 ELSE NULL END) AS "Jan",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 02
THEN 1 ELSE NULL END) AS "Feb",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 03
THEN 1 ELSE NULL END) AS "Mar",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 04
THEN 1 ELSE NULL END) AS "Apr",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 05
THEN 1 ELSE NULL END) AS "May",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 06
THEN 1 ELSE NULL END) AS "Jun",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 07
THEN 1 ELSE NULL END) AS "Jul",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 08
THEN 1 ELSE NULL END) AS "Aug",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 09
THEN 1 ELSE NULL END) AS "Sep",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 10
THEN 1 ELSE NULL END) AS "Oct",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 11
THEN 1 ELSE NULL END) AS "Nov",
COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 12
THEN 1 ELSE NULL END) AS "Dec",
COUNT (EXTRACT ( MONTH FROM public.incident.incident_date ) ) AS "Total"
FROM public.incident
WHERE ( public.incident.person_age BETWEEN 1 AND 10 );
|
|
|
| Re: Multiple data sets pie/bar charts [message #229923 is a reply to message #229899] |
Fri, 23 March 2007 13:34 |
| Eclipse User |
|
|
|
Can you do a union in your sql statements to have them return all the rows
in one single query?
Thanks,
David
"Norm Garand" <indianlakesolutions@rogers.com> wrote in message
news:cbca4e8c321d6048b2b1dfa8bc34aa94$1@www.eclipse.org...
>I realize that normally a dataset has normally more than 1 row. But in this
>instance, it doesn't. The following sql below generates 1 row. It finds the
>number of occurrences for the ages between 1 & 10 and displays them as
>occurrences per month. The situation is, that there are 9 different sql
>statements to grab each age group. I have to insert a label in the display
>table for each row (i.e. Age 1 - 10). What I need to display in either pie
>or bar chart is the final total per row and it's corresponding label.
>Hopefully this is clearer than my original post.
>
> SELECT
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 01
> THEN 1 ELSE NULL END) AS "Jan",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 02
> THEN 1 ELSE NULL END) AS "Feb",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 03
> THEN 1 ELSE NULL END) AS "Mar",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 04
> THEN 1 ELSE NULL END) AS "Apr",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 05
> THEN 1 ELSE NULL END) AS "May",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 06
> THEN 1 ELSE NULL END) AS "Jun",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 07
> THEN 1 ELSE NULL END) AS "Jul",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 08
> THEN 1 ELSE NULL END) AS "Aug",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 09
> THEN 1 ELSE NULL END) AS "Sep",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 10
> THEN 1 ELSE NULL END) AS "Oct",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 11
> THEN 1 ELSE NULL END) AS "Nov",
> COUNT (CASE EXTRACT ( MONTH FROM public.incident.incident_date )WHEN 12
> THEN 1 ELSE NULL END) AS "Dec",
> COUNT (EXTRACT ( MONTH FROM public.incident.incident_date ) ) AS "Total"
> FROM public.incident
> WHERE ( public.incident.person_age BETWEEN 1 AND 10 );
>
>
|
|
|
Powered by
FUDForum. Page generated in 0.03784 seconds
Home