I need help making only one (complete) Pie Chart show up when using GROUP BY in my query (SQL) using iReport 4.6.0.
I need to create a report using iReport (4.6.0; I am using the standalone version, not the NetBeans Plug-in) using data pulled from an SQL database. Basically, I'm trying to create a Pie Chart of what percentage of people in the DB are male and what percentage are female.
At first I tried doing two separate queries to extract this information:
SELECT COUNT('person_id') FROM 'table' WHERE 'SEX' = 'M';
SELECT COUNT('person_id') FROM 'table' WHERE 'SEX' = 'F';
I tried putting both of these queries into the Report Query dialog box, but iReport apparently only handles one query at a time. I also tried using UNION.
I then attempted (and am currently using):
SELECT COUNT('person_id') FROM 'table' GROUP BY 'sex'
This has given me limited success. It creates the chart that I need, but also creates additional, superfluous charts with only part of the data. It appears that each time iReport hits a new "group" from the query, it creates a new chart with the information with that group's data in addition to any data from previous groups (ie. in this case, it created 2 Pie Charts, one which showed only the female members of the DB and one which showed both male and female members). This theory was also tested by adding a new category in the DB (M, F, U) to see whether it would create 3 charts; it does (first with just female, second with male and female, third with male, female, and unknown).
The chart which interests me is the last one, the one which shows the data from all the groups. Can you guys help me in figuring out how to get only that last, complete Pie Chart?
