How to intergrate group data into ONE chart ?

[lovetide]

First, congratulations on the new home of JasperReports & iReport & JasperIntelligence !
(This question is original posted at sf.net)

Suppose the following data set:

Code:

I want the following result/goal:
(1).

Code:

(2). A chart contains two series: 1st is DialIn_Count series, 2nd is DialOut_Count series.

Before I use crosstab, I can use report group to group out DialIn and DialOut separately, and each group contains a chart which contains 1 series. So, none of them meet my requirements.

After I used crosstab, I achieved the first result/goal, but I still can't get 2 series in ONE chart, so, is there a way to achieve it ?

Thanks !

[lucianc]

What kind of chart do you want to obtain?

Generating distinct series based on the direction can be achieved by simply setting the direction as the series expression:

Code:

HTH,
Lucian

[Giulio Toffoli]

Hi liu,

attached there is the solution to your problem
solved using a chart (absolutly unuseful) and
a crosstab (that is what I think you need).

Attached there is a CSV file to test the jrxml
using a CSV datasource.

Please note that the crosstab is at the end of the report.

Giulio [file name=lovetide-9cc4990a9b4615427bc9221130a36a40.zip size=16740]

[lovetide]

gtoffoli wrote:

Hi liu, attached there is the solution to your problem solved using a chart (absolutly unuseful) and a crosstab (that is what I think you need). ...

Thanks a lot Giulio :laugh: , it seems your solution meet my requirements, I will learn it tomorrow(i mean today, it's 01:00am now here).



lucianc wrote:

What kind of chart do you want to obtain? Generating distinct series based on the direction can be achieved by simply setting the direction as the series expression: Code: HTH, Lucian

I want to obtain a TimeSeriesChart, like the image shows(I use a LineChart instead), my original solution is put TimeSeriesChart in DirectionGroupHeader/DirectionGroupFooter, the Group is group by diretion like your suggestion (see the .zip attachment), see the report sample in the attachment: I can only get one series separately in each group. I will learn Giulio's solution which put chart in SUMMARY band, and use TimeGroup instead of DirectionGroup.

by the way: please delete my previous duplicate post, that's post is generated by my mistake refresh operation from browser.
[file name=CallCount_100865x_Hourly_Report.zip size=39446]

comment of edit:
I make a PNG snapshot, it exceed the pixel limitation, after I resized it, the file size even more larger then the original one :ohmy: size=400]http://www.jasperforge.org/components/com_joomlaboard/uploaded/images/sample_chart.png
Post edited by: lovetide, at: 2006/07/18 17:24

[lovetide]

After I tried many times, I found lucianc's sulution is ok and may be the simplest and the best solution. I don't know seriesExpression can produced moret than 1 series before.

 

 

My data set is a little different to the supposing data set, and maybe I misunderstanding the usage of Group, so I can't get what I wanted in chart.

 

 

See the following two data sets:

 

 

Data set 1:

[code:1]

This data set is supposed as the previous post

mysql> SELECT Direction, Date(CallBeginTime) AS Date, COUNT(*) AS Count

-> FROM CallLog

-> WHERE Extract(YEAR_MONTH FROM CallBeginTime)=Extract(YEAR_MONTH FROM '2006-07-19') AND DNI

S='186053#'

-> GROUP BY Date, Direction;

+-----------+------------+-------+

| Direction | Date | Count |

+-----------+------------+-------+

| 1 | 2006-07-02 | 20822 |

| 2 | 2006-07-02 | 8133 |

| 1 | 2006-07-03 | 17582 |

| 2 | 2006-07-03 | 6413 |

| 1 | 2006-07-04 | 16585 |

| 2 | 2006-07-04 | 5845 |

| 1 | 2006-07-05 | 16218 |

| 2 | 2006-07-05 | 5897 |

| 1 | 2006-07-06 | 16201 |

| 2 | 2006-07-06 | 5791 |

| 1 | 2006-07-07 | 16586 |

| 2 | 2006-07-07 | 6317 |

| 1 | 2006-07-08 | 15779 |

| 2 | 2006-07-08 | 6065 |

| 1 | 2006-07-09 | 16333 |

| 2 | 2006-07-09 | 6019 |

| 1 | 2006-07-10 | 14623 |

| 2 | 2006-07-10 | 5812 |

| 1 | 2006-07-11 | 5208 |

| 2 | 2006-07-11 | 2246 |

| 1 | 2006-07-12 | 3755 |

| 2 | 2006-07-12 | 1570 |

| 1 | 2006-07-13 | 3324 |

| 2 | 2006-07-13 | 1456 |

| 1 | 2006-07-14 | 3236 |

| 2 | 2006-07-14 | 1437 |

| 1 | 2006-07-15 | 3623 |

| 2 | 2006-07-15 | 1662 |

| 1 | 2006-07-16 | 3917 |

| 2 | 2006-07-16 | 1787 |

| 1 | 2006-07-17 | 3789 |

| 2 | 2006-07-17 | 1666 |

| 1 | 2006-07-18 | 3482 |

| 2 | 2006-07-18 | 1562 |

| 1 | 2006-07-19 | 3866 |

| 2 | 2006-07-19 | 1801 |

| 1 | 2006-07-20 | 1958 |

| 2 | 2006-07-20 | 792 |

+-----------+------------+-------+

38 rows in set (18.97 sec)

[/code:1]

 

Data set 2:

[code:1]

This data set is what I actually using

mysql> SELECT Direction, Date(CallBeginTime) AS Date, COUNT(*) AS Count

-> FROM CallLog

-> WHERE Extract(YEAR_MONTH FROM CallBeginTime)=Extract(YEAR_MONTH FROM '2006-07-19') AND DNI

S='186053#'

-> GROUP BY Direction, Date;

+-----------+------------+-------+

| Direction | Date | Count |

+-----------+------------+-------+

| 1 | 2006-07-02 | 20822 |

| 1 | 2006-07-03 | 17582 |

| 1 | 2006-07-04 | 16585 |

| 1 | 2006-07-05 | 16218 |

| 1 | 2006-07-06 | 16201 |

| 1 | 2006-07-07 | 16586 |

| 1 | 2006-07-08 | 15779 |

| 1 | 2006-07-09 | 16333 |

| 1 | 2006-07-10 | 14623 |

| 1 | 2006-07-11 | 5208 |

| 1 | 2006-07-12 | 3755 |

| 1 | 2006-07-13 | 3324 |

| 1 | 2006-07-14 | 3236 |

| 1 | 2006-07-15 | 3623 |

| 1 | 2006-07-16 | 3917 |

| 1 | 2006-07-17 | 3789 |

| 1 | 2006-07-18 | 3482 |

| 1 | 2006-07-19 | 3866 |

| 1 | 2006-07-20 | 1961 |

| 2 | 2006-07-02 | 8133 |

| 2 | 2006-07-03 | 6413 |

| 2 | 2006-07-04 | 5845 |

| 2 | 2006-07-05 | 5897 |

| 2 | 2006-07-06 | 5791 |

| 2 | 2006-07-07 | 6317 |

| 2 | 2006-07-08 | 6065 |

| 2 | 2006-07-09 | 6019 |

| 2 | 2006-07-10 | 5812 |

| 2 | 2006-07-11 | 2246 |

| 2 | 2006-07-12 | 1570 |

| 2 | 2006-07-13 | 1456 |

| 2 | 2006-07-14 | 1437 |

| 2 | 2006-07-15 | 1662 |

| 2 | 2006-07-16 | 1787 |

| 2 | 2006-07-17 | 1666 |

| 2 | 2006-07-18 | 1562 |

| 2 | 2006-07-19 | 1801 |

| 2 | 2006-07-20 | 792 |

+-----------+------------+-------+

38 rows in set (1.89 sec)

[/code:1]

 

 

For data set 2, I created a DirectionGroup in reports, the expression of DirectionGroup is $F{Direction}. I want a TimeSeriesChart, the TimeSeriesChart will contains two series according different Direction, so I set the SeriesExpression of chart to $F{Direction}, set PeriodExpression to $F{Date}, set ValueExpression to $F{Count}, set IncrementType to Group, set IncrementGroup to DirectionGroup... Although it can generate two series, but only the value of the last date (2006-07-20) displayed, two series becomes two dots.

 

 

If i changed data set to data set 1, the result is ok.

 

 

See data set 2, the green group(direction=1) and the blue group(direction=2) are grouped via "GROUP BY" SQL syntax, so I create a DirectionGroup in report, and got a wrong result, Am I misunderstanding the usage of Group ?

[lucianc]

What I think you misunderstand is the chart dataset increment logic.

 

If you set incrementType="Group" and incrementGroup="DirectionGroup" for a chart dataset, the dataset will only get incremented when the direction group breaks. This is usefull in the chart should display group totals, which is not your case. You need to increment (i.e., feed data to) the chart on each row.

 

With the first data set, the direction group breaks at each row (the direction changes at each step), therefore using group increment is equivalent to using row increment. In the second case, the group breaks only two times (when the direction changes and when the data ends), and only the last value is fed to the chart dataset.

 

Change incrementType to "None" (which is the default, and maybe poorly named) and the charts will be fed data at each row.

 

HTH,

Lucian

[lovetide]

lucianc wrote:

What I think you misunderstand is the chart dataset increment logic. If you set incrementType="Group" and incrementGroup="DirectionGroup" for a chart dataset, the dataset will only get incremented when the direction group breaks. This is usefull in the chart should display group totals, which is not your case. You need to increment (i.e., feed data to) the chart on each row. With the first data set, the direction group breaks at each row (the direction changes at each step), therefore using group increment is equivalent to using row increment. In the second case, the group breaks only two times (when the direction changes and when the data ends), and only the last value is fed to the chart dataset. Change incrementType to "None" (which is the default, and maybe poorly named) and the charts will be fed data at each row. HTH, Lucian

:laugh: Exactly !

I really misunderstood the chart dataset increment logic, your expert conclusion makes me understand the usage and the mechanism of it very clearly now.

 

Thank you very very much Lucian !

[atanupanda]

Hi

First of all I want to tell you that please don't mind if there is any problem regarding to this topic opening.
I am attaching a file for this.
I am very new in iReport. As I show in this picture
I want to create this line chart according to this values.I want to mean that in X axis there will be value from t1 -t2-t3-t4-t5 columns . Also there will be upper value and lower value limit and their corresponding line. If any value close to this upper line or lower line it will show yellow alert as shown in this picture. If any value cross this upper line or lower line it will show red alert as shown in this picture. Also I want to draw line corresponding this uppper value and lower value.

Please any one help me.
If any one don't understand what i want please reply.

[file name=test_2eb6e0b070efd73f83e242f67fac14c1.doc size=98816]