Jasper ireport query design

Question

I need to draw a chart in Jasper iReport that can sum the results based on user selection. Let's say I have the fruits table:

id (pk)       town          bananas          orange          cherry 
1            boston           5                 0              11              
2            paris            100               18             12              
3            bucharest        10                3              9
4            barcelona        9                 4              12

From a Jasper web server input control with an ireport parameter associated (java.util.collection) user will select the fruit/fruits.

I need to sum all the fruits selected by the user and plot the chart.

For example, when then user selects both 'bananas' and 'oranges' the result would be

              town          result  
1            boston           5 
2            paris            118 
3            bucharest        13 
4            barcelona        13

The problem is:

I can't do select sum(bananas,orange) as result from fruits group by town.

mysql sum function doesn't work this way, but this is the way jasper input control return the user selection back to report query.

I've tried SELECT (SELECT REPLACE('bananas,orange', ',', '+')) as result from fruits group by town, but MySQL doesn't recognize the replace statement as column name. I get this:

              town          result  
1            boston           bananas+orange 
2            paris            bananas+orange
3            bucharest        bananas+orange
4            barcelona        bananas+orange

also, SELECT sum(SELECT REPLACE('bananas,orange', ',', '+')) as result from fruits group by town will return 0 as result

Any idea? Maybe this can be done from ireport..?

Rob Kielty Rob Kielty · Accepted Answer · 2012-07-05T16:56:13

You could dynamically create the following query (using a stored procedure) based on the input parameters that your user specifies. Then the stored procedure would become your data source within the JRXML.

select id, town, sum(bananas) + sum(oranges) as result from fruits group by town

I now think that this is messy.

An alternative would be to re-factor the FRUIT_SALES table as follows

CREATE TABLE "SEEDY_FOODS"."FRUIT_SALES"
  (
    "ID"            NUMBER NOT NULL ENABLE,
    "TOWN"          VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "FRUIT_TYPE_ID" NUMBER,
    "QTY_SOLD"      NUMBER NOT NULL ENABLE,
    CONSTRAINT "FRUIT_SALES_PK" PRIMARY KEY ("ID") 
  )

Sample data

FRUIT_SALES
 ID TOWN    FRUIT_TYPE_ID  SALES
 1  boston  1              5
 2  boston  2              0
 3  boston  3              11
 4  paris   1              100
 5  paris   2              18
 6  paris   3              12

Then have a reference table such as.

CREATE TABLE "SEEDY_FOODS"."FRUIT_TYPES"
  (
    "ID"   NUMBER NOT NULL ENABLE,
    "NAME" VARCHAR2(20 BYTE)
  )

Sample data

FRUIT_TYPE
 ID NAME
 1  bananas
 2  orange
 3  cherry

The above DDL SQL was created for Oracle using SQL Developer. You will need to verify this syntax on MySQL

Now the target query to act as a data source for your Jasper Report becomes

select town, sum(qty_sold)
from fruit_sales
where $X{IN,FRUIT_TYPE_ID,COUNTED_FRUIT_TYPES}
group by town

Here we make us of the JasperReports feature $X{IN,<column>,<PARAMETER>} to only select those rows which meet the criteria of fruit specified in the the parameter $P{COUNTED_FRUIT_TYPES} which is of type java.util.Collection

Here's the start of the JXRML

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" 
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" 
              name="FruitSalesByTown" 
              language="groovy" pageWidth="595" pageHeight="842" columnWidth="535" 
              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" 
              uuid="9cc45ae0-15a8-4f13-8e02-b0a4379f7019">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
 :
 :
snip style tags 
 :
 :
    <parameter name="COUNTED_FRUIT_TYPES" class="java.util.Collection"/>
    <queryString>
        <![CDATA[select town, sum(qty_sold)
from fruit_sales
where $X{IN,FRUIT_TYPE_ID,COUNTED_FRUIT_TYPES}
group by town]]>
    </queryString>

(ref. Page 80 of the Jasper Reports Ultimate Guide)

Jasper ireport query design

1 Answers