Multiple clause where for line chart

[Pollux Us]

Hi,

I'm new to JasperReports, here is my problem :

I have one table SALE with three columns price [bigDecimal], id_product [integer], the_date [Date].

 

My query :

SELECT sum(price), the_dateFROM SALEWHERE id_product = $P{my_parameter}GROUP BY the_date[/code]

The query works, but i want to get four datasets (one for each case of my_parameter). I would like to build a lineChart with 4 lines.

For each line :

Category : $F{the_date}

Value : $F{sum(price)}

Series : $P{key_parameter}

 

Line 1 => Query 1 => my_parameter = 1

Line 2 => Query 2 => my_parameter = 2

Line 3 => Query 3 => my_parameter = 3

Line 4 => Query 4 => my_parameter = 4

 

I want the four line in the same time on my chart (At the moment i just get one line).

Is this possible ? If this is possible can u explain me how to do this please ?

Thanks for help in advance !

 

PS : I apologise for my rought english.

 

[Pollux Us]

Hi,

I found how to do this, if someone is interested, but i'm not sure it's the best way.

 

In my java code i created a TreeMap<Date,List<BigDecimal>>

I initialized this TreeMap by the Date i want with a list of 0 for each product :

(Date)	List(0) <=> product 1	List(1) <=> product 2	List(2) <=> product 3	List(3) <=> product 4
02-July-2014	0	0	0	0
01-july-2014	0	0	0	0

then i created jdbc connection and i executed all my requests from my java code  :

Request  1 :

SELECT SUM(price), the_dateFROM SALEWHERE id_product = 1AND the_date > TO_DATE('31/05/2014', 'DD/MM/YYYY')AND the_date < TO_DATE('03/07/2014', 'DD/MM/YYYY')GROUP BY the_date</pre>[/code]

Request 2 :

SELECT SUM(price), the_dateFROM SALEWHERE id_product = 2AND the_date > TO_DATE('31/05/2014', 'DD/MM/YYYY')AND the_date < TO_DATE('03/07/2014', 'DD/MM/YYYY')GROUP BY the_date[/code]

Request 3 :

SELECT SUM(price), the_dateFROM SALEWHERE id_product = 3AND the_date > TO_DATE('31/05/2014', 'DD/MM/YYYY')AND the_date < TO_DATE('03/07/2014', 'DD/MM/YYYY')GROUP BY the_date[/code]

 

Request 4 :

SELECT SUM(price), the_dateFROM SALEWHERE id_product = 4AND the_date > TO_DATE('31/05/2014', 'DD/MM/YYYY')AND the_date < TO_DATE('03/07/2014', 'DD/MM/YYYY')GROUP BY the_date[/code]

then i got the results and filled my TreeMap :

(Date)	List(0) <=> product 1	List(1) <=> product 2	List(2) <=> product 3	List(3) <=> product 4
02-July-2014	100	0	180	0
01-july-2014	150	200	0	60

With this TreeMap i created an array of bean, then a JRDataSource, i passed my JRDataSource to my report in my java code and it's works.

 

Like i said before i'm think it's not the best solution, if someone have a better solution i would like to know what it is :)