How to intergrate group data into ONE chart ?

0

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

Suppose the following data set:

Code:
TimeInterval   Direction    Count
-----------------------------------
00:00-00:30    DialIn       100
00:00-00:30    DialOut      101
00:30-01:00    DialIn       102
00:30-01:00    DialOut      103
01:00-01:30    DialIn       104
01:00-01:30    DialOut      105
.......
.......

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

Code:
TimeInterval   DialIn_Count DialOut_Count
-----------------------------------
00:00-00:30    100          101
00:30-01:00    102          103
01:00-01:30    104          105

(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 !

lovetide's picture
Joined: Jul 17 2006 - 11:19pm
Last seen: 9 years 2 weeks ago

7 Answers:

0

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:
<seriesExpression>$F{direction}</seriesExpression>


HTH,
Lucian

lucianc's picture
7439
Joined: Jul 17 2006 - 1:10am
Last seen: 19 hours 52 min ago
0

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]

giulio's picture
46972
Joined: Jan 2 2007 - 4:15pm
Last seen: 1 year 11 months ago
0

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:
<seriesexpression>$F{direction}</seriesexpression>

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]
Post edited by: lovetide, at: 2006/07/18 17:24

lovetide's picture
Joined: Jul 17 2006 - 11:19pm
Last seen: 9 years 2 weeks ago
0
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 [b]seriesExpression[/b] can produced moret than 1 series before. My data set is a little different to the supposing data set, and [b][color=#FF0000]maybe I misunderstanding the usage of Group[/color][/b], so I can't get what I wanted in chart. See the following two data sets: Data set 1: [code:1] [color=#800080][i]This data set is supposed as the previous post[/i][/color] 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#' -> [color=#FF6600]GROUP BY Date, Direction[/color]; +-----------+------------+-------+ | Direction | Date | Count | +-----------+------------+-------+ [color=#008000]| 1 | 2006-07-02 | 20822 |[/color] [color=#0000FF]| 2 | 2006-07-02 | 8133 |[/color] [color=#008000]| 1 | 2006-07-03 | 17582 |[/color] [color=#0000FF]| 2 | 2006-07-03 | 6413 |[/color] [color=#008000]| 1 | 2006-07-04 | 16585 |[/color] [color=#0000FF]| 2 | 2006-07-04 | 5845 |[/color] [color=#008000]| 1 | 2006-07-05 | 16218 |[/color] [color=#0000FF]| 2 | 2006-07-05 | 5897 |[/color] [color=#008000]| 1 | 2006-07-06 | 16201 |[/color] [color=#0000FF]| 2 | 2006-07-06 | 5791 |[/color] [color=#008000]| 1 | 2006-07-07 | 16586 |[/color] [color=#0000FF]| 2 | 2006-07-07 | 6317 |[/color] [color=#008000]| 1 | 2006-07-08 | 15779 |[/color] [color=#0000FF]| 2 | 2006-07-08 | 6065 |[/color] [color=#008000]| 1 | 2006-07-09 | 16333 |[/color] [color=#0000FF]| 2 | 2006-07-09 | 6019 |[/color] [color=#008000]| 1 | 2006-07-10 | 14623 |[/color] [color=#0000FF]| 2 | 2006-07-10 | 5812 |[/color] [color=#008000]| 1 | 2006-07-11 | 5208 |[/color] [color=#0000FF]| 2 | 2006-07-11 | 2246 |[/color] [color=#008000]| 1 | 2006-07-12 | 3755 |[/color] [color=#0000FF]| 2 | 2006-07-12 | 1570 |[/color] [color=#008000]| 1 | 2006-07-13 | 3324 |[/color] [color=#0000FF]| 2 | 2006-07-13 | 1456 |[/color] [color=#008000]| 1 | 2006-07-14 | 3236 |[/color] [color=#0000FF]| 2 | 2006-07-14 | 1437 |[/color] [color=#008000]| 1 | 2006-07-15 | 3623 |[/color] [color=#0000FF]| 2 | 2006-07-15 | 1662 |[/color] [color=#008000]| 1 | 2006-07-16 | 3917 |[/color] [color=#0000FF]| 2 | 2006-07-16 | 1787 |[/color] [color=#008000]| 1 | 2006-07-17 | 3789 |[/color] [color=#0000FF]| 2 | 2006-07-17 | 1666 |[/color] [color=#008000]| 1 | 2006-07-18 | 3482 |[/color] [color=#0000FF]| 2 | 2006-07-18 | 1562 |[/color] [color=#008000]| 1 | 2006-07-19 | 3866 |[/color] [color=#0000FF]| 2 | 2006-07-19 | 1801 |[/color] [color=#008000]| 1 | 2006-07-20 | 1958 |[/color] [color=#0000FF]| 2 | 2006-07-20 | 792 |[/color] +-----------+------------+-------+ 38 rows in set (18.97 sec) [/code:1] Data set 2: [code:1] [color=#800080][i]This data set is what I actually using[/i][/color] 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#' -> [color=#FF6600]GROUP BY Direction, Date[/color]; +-----------+------------+-------+ | Direction | Date | Count | +-----------+------------+-------+ [color=#008000]| 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 | [/color][color=#0000FF]| 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 | [/color]+-----------+------------+-------+ 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 [color=#008000]green group[/color](direction=1) and the [color=#0000FF]blue group[/color](direction=2) are grouped via "GROUP BY" SQL syntax, so I create a DirectionGroup in report, and got a wrong result, [color=#FF0000][i][b]Am I misunderstanding the usage of Group ?[/b][/i][/color]
lovetide's picture
Joined: Jul 17 2006 - 11:19pm
Last seen: 9 years 2 weeks ago
0
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
lucianc's picture
7439
Joined: Jul 17 2006 - 1:10am
Last seen: 19 hours 52 min ago
0
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 !
lovetide's picture
Joined: Jul 17 2006 - 11:19pm
Last seen: 9 years 2 weeks ago
0

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]

atanupanda's picture
Joined: Dec 21 2006 - 9:51pm
Last seen: 15 years 1 month ago
Feedback