|
|
|
|
|
|
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009494 is a reply to message #1009297] |
Fri, 15 February 2013 08:28 |
Alberto Morando Messages: 9 Registered: February 2013 |
Junior Member |
|
|
Hi Michael,
Thank you for the info.
I will try to explain step by step what I did and attach the chart I have got.
I have a table with Date, ID (2 columns). I use it in the chart (i.e. Select Data field is "USe Report Item" and I choose the table with the 2 columns).
I create the chart and:
1) In the 'Select Data' screen of your chart, for Category (X) Series select the DATE field
2) right of 'fx' you can select the X axis grouping : at this step I have choosen:
- Data Sorting: Ascending
- Grouping: Enabled
- Type: DateTime
- Unit: Month
- Interval: 1
- Aggregation Expression: Count
3) In the Value (Y) Series field, put ID: In this case I have put row._rownum (number of items in a month)
4) To the left of the Value (Y) Series field, in the dropdowm menu I choose "Count" as you suggested.
The result is attached picture.
AS you can see we have the number of items for each month. (e.g. 04/2011, 1; 06/2011, 2; 07/2011, 3; and so on).
BUT what I would like to achieve is to have a sum of them (accumulated per month) (e.g. for the example above I would like to have a chart showing : 04/2011, 1; 06/2011, 3; 07/2011, 6; and so on).
Could you please help me on achieving this? I think I miss the little final step to get it.
I really appreciate your help.
Thanks
Alberto
|
|
|
|
|
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009874 is a reply to message #1009859] |
Fri, 15 February 2013 22:46 |
|
Try adding a script like this to your chart's script. Make sure you change "Series 1" in the script to whatever you've named your series.
function afterDataSetFilled(series, dataSet, icsc)
{
if(series.getSeriesIdentifier() == "Series 1"){
temp = 0;
vals = dataSet.getValues();
for(i=0;i<vals.length;i++){
temp = temp + parseInt(vals[i]);
vals[i] = temp;
}
}
}
Michael
Developer Evangelist, Silanis
|
|
|
|
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1010941 is a reply to message #1010779] |
Mon, 18 February 2013 14:16 |
Eclipse Guest Messages: 93 Registered: February 2013 Location: Vienna |
Member |
|
|
Okay, I wasn't aware of that the running count is not available in earlier BIRT versions.
I created a scripted version of the running count by adding a computed column to the dataset. Of course this adds some redundant data to the processing, but that's what comes to my mind and which works... I will attach the report but in case you are not able to view it, here are the steps:
- make sure your data is retrieved ordered by date (add "ORDER BY DATE" to your sql query)
- add a computed column to your dataset, name it "running_count", set type to integer, and expression to 0. click ok to save
- while the dataset is selected, click the script button to open scripting for that dataset
- in "before open", type:
i=0;
- in "onFetch", type:
i++;
row["running_count"] = i;
- in your chart, for Value (Y) Series, select the row["running_count"]
(it will not appear in the data selection, so you have to edit your data bindings or re-select the datasource or recreate the chart with the updated dataset)
- as aggregate function for Value (Y) Series, select MAXIMUM or LAST
Finally I hope that BIRT 2.2.1 supports the aggregate function MAXIMUM or LAST... if not then I don't know anymore :S
|
|
|
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1011128 is a reply to message #1010941] |
Mon, 18 February 2013 22:50 |
|
Series 1 is what the series is named. If you go to the series section of the format chart tab in the chart editor, you'll see a text box for the series name. Enter a series name here and use this as your "Series 1".
What the script above does is checks that you're dealing with the wanted series, "Series 1", then steps through that series' values and sums them. Each step through the series values, the current running sum replaces the current value in the chart's dataSet. The computed columns approach, offered by Jakob, would work, too.
Michael
Developer Evangelist, Silanis
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.05744 seconds