Creating Pie Chart in iReport (4.6.0)

chartsireportjasper-reports

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?

Best Answer

Put the chart in the Title or the Summary band. Make sure its evaluation time is set to 'Report'.

Related Solutions

Display value in bar chart when using iReport and JasperReport

If IReport "show label" option already checked then unchecked it and check again so that it works. Worked for me with IReport 5.1.0

Jasper Report (iReport) Pie Dataset from main dataset

You can solve this issue without programming following this steps:

sort data by category field and by user field (add sorting via iReport or adding ORDER BY clause to the query in case having deal with jdbc datasource);
add two groups: the 1st group - for category field and the 2nd group - for user field (via iReport). Note: It is not necessarily to add GROUP BY clause to the query;
add Pie Chart component to the Group Header band for the category field;
set evaluationTime and evaluationGroup properties for this pieChart.

Here is working sample for demonstrating how to build Pie Chart. In this sample I've used the csv file as datasource.

The chart shows the quantity of users for every category. I used variable usersCount for counting users in every group, but it is was possible to use built-in variable $V{userGroup_COUNT}.

Here is my jrxml file:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <field name="category" class="java.lang.Integer"/>
    <field name="user" class="java.lang.String"/>
    <field name="status" class="java.lang.String"/>
    <sortField name="category"/>
    <sortField name="user"/>
    <variable name="usersCount" class="java.lang.Integer" resetType="Group" resetGroup="userGroup" calculation="Count">
        <variableExpression><![CDATA[$F{user}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <group name="categoryGroup">
        <groupExpression><![CDATA[$F{category}]]></groupExpression>
        <groupHeader>
            <band height="135">
                <pieChart>
                    <chart evaluationTime="Group" evaluationGroup="categoryGroup">
                        <reportElement x="158" y="0" width="200" height="135"/>
                        <chartTitle/>
                        <chartSubtitle/>
                        <chartLegend/>
                    </chart>
                    <pieDataset>
                        <dataset resetType="Group" resetGroup="categoryGroup" incrementType="Group" incrementGroup="userGroup"/>
                        <keyExpression><![CDATA[$F{user}]]></keyExpression>
                        <valueExpression><![CDATA[$V{usersCount}]]></valueExpression>
                        <labelExpression><![CDATA[String.valueOf($V{usersCount})]]></labelExpression>
                    </pieDataset>
                    <piePlot>
                        <plot/>
                        <itemLabel/>
                    </piePlot>
                </pieChart>
                <textField>
                    <reportElement x="0" y="0" width="100" height="20"/>
                    <textElement/>
                    <textFieldExpression><![CDATA["Category: " + $F{category}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <group name="userGroup">
        <groupExpression><![CDATA[$F{user}]]></groupExpression>
    </group>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
</jasperReport>

Here is my csv file (data file - datasource):

category,user,status
1,user1,WORK
1,user1,HOLIDAY
1,user2,SICK
2,user1,WORK
2,user2,WORK
2,user2,HOLIDAY
2,user3,HOLIDAY
2,user4,SICK
3,user1,HOLIDAY
3,user3,HOLIDAY
3,user5,HOLIDAY
4,user1,WORK
4,user1,HOLIDAY
4,user2,SICK
4,user2,WORK
4,user2,SICK
4,user3,HOLIDAY
4,user4,HOLIDAY
4,user5,HOLIDAY

The result will be (via iReport preview):

The result

Related Topic

PHP MySQL Google Chart JSON – Complete Example