Eclipse Community Forums: BIRT » HowTo sum (accumulate) aggregate values in chart BIRT Eclipse

Home » Archived » BIRT » HowTo sum (accumulate) aggregate values in chart BIRT Eclipse(HowTo sum aggregate values in BIRT Eclipse)

HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008427]

Tue, 12 February 2013 07:49

Eclipse User

Hi

I am new to the BIRT world and please sorry for the following questions (maybe they have obvious solutions) Smile

This is my setup:
BIRT version 2.2.1. I cannot update to a new version since I use it to get data from ClearQuest 7.1.2

I have a table that has two columns:

Date, ID

I have added a group based on Date and an aggregation on the number of items per group date.

The result is that at each date group I have the number of item per month saved in the aggregate value.

Now I need to accumulate per month to show in a chart (accumulated item per month in a chart)

Let´s say:
DATE ID
01/04/2012

01/04/2012 013
01/04/2012 023
----------
2 (aggregate value: Number of items in the month)

01/06/2012

01/06/2012 024
01/06/2012 035
01/06/2012 037
----------
3

I want to have a chart that have in X-axis : Date and in the Y- axis the running sum of the aggergate value of the table

following the above example: x: 01/04/2012 y: 2
x: 01/06/2012 y: 5 (the sum of the aggrgate value)
and so on.

Could you please help me on that

Thanks

Alberto

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008513 is a reply to message #1008427]

Tue, 12 February 2013 14:25

Eclipse User

Couldn't you just use the aggregation capability of the chart to do this? Put the date on the x-axis, set your grouping to what you want, then, set the aggregation on the y-series to "count". This would display the count of values for your date grouping. Let me know.

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008595 is a reply to message #1008513]

Wed, 13 February 2013 03:57

Eclipse User

Hi Michael,

Thanks for the info.

I think I miss some of the steps.

I have put the date in the x-axis but then I cannot understand what you mean with:

"set your grouping to what you want". Where do I set the grouping in the chart?

What does "set the aggregation on the y-series to "count" " mean?

What should I put in the "Value (Y) Series" ?

What I need again is a kind of running sum (accumulate) of the number of items (ids) per month.

Sorry for the my questions again but I would really appreciate if you can be more specific on you suggestion since I am new to BIRT Smile

Many thanks

Alberto

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008694 is a reply to message #1008595]

Wed, 13 February 2013 10:44

Eclipse Guest

Messages: 93
Registered: February 2013
Location: Vienna

Member

In the 'Select Data' screen of your chart, for Category (X) Series select the DATE field, as you probably already did. To the right of this field, right of 'fx' you can select the X axis grouping. There you can set the aggregation period to one month.

In the Value (Y) Series field, put ID (even DATE would work I think). To the left of the Value (Y) Series field, there is a dropdown menu with a sum icon on it. Expand the dropdown list and choose 'Aggregate Expression: Running Count'.

I hope this will do the trick for you. Cheers, Jakob

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008953 is a reply to message #1008694]

Thu, 14 February 2013 02:54

Eclipse User

Hi Jakob,

Thank you for your help.

I have tried to do as you suggested:

1) In the 'Select Data' screen of your chart, for Category (X) Series select the DATE field : DONE

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, there is a dropdown menu with a sum icon on it. Expand the dropdown list and choose 'Aggregate Expression: Running Count': In this case expanding the dropdown menu I cannot see the "Running Count".
Maybe this is due that I am using BIRT version 2.2.1 as I have mentioned at the first message I cannot update it since I am using it with ClearQuest version 7.1.2 and I cannot update it.

So I think I cannot use the chart capability to do the Running Sum. Is there a way to do it with a bit coding . I have read on internet that there is the possibility to use Java script (e.g. Total.sum). Is there maybe a way to use it to solve my issue?

Please help Smile

Thanks

Alberto

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009297 is a reply to message #1008953]

Thu, 14 February 2013 16:06

Eclipse User

For step 2, I don't think you need to change the aggregation to count. You can leave it as sum, but I don't think it matters, in this case.
For step 3, any field will do.
For step 4, use "count". It should be there.

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009494 is a reply to message #1009297]

Fri, 15 February 2013 03:28

Eclipse User

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

Attachment: Chart_Date_Grouping.png
(Size: 108.59KB, Downloaded 729 times)

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009497 is a reply to message #1009494]

Fri, 15 February 2013 03:30

Eclipse User

Hi Again,

Sorry for the previous attached picture.

I hope this is better Smile

/Alberto

Attachment: Chart_Date_Grouping.png
(Size: 13.85KB, Downloaded 895 times)

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009859 is a reply to message #1009497]

Fri, 15 February 2013 16:50

Eclipse User

Ah. My mistake. I must've skipped right over that part. Since the designer doesn't have an option for this, back in your version, you should be able to script it. I'll make an example and attach.

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009874 is a reply to message #1009859]

Fri, 15 February 2013 17:46

Eclipse User

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;
}
}
}

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1010779 is a reply to message #1009874]

Mon, 18 February 2013 03:20

Eclipse User

Hi Michael,

Thanks for your info.

I have added the script as you suggested. Attached you find the picture of the sutup.

In the "Series 1" I have put "row.__rownum". This is because I use "RowNum" in the series 1 in the Chart setup.

But the chart is the same as in the attached picture in my previous message.

What I would like to do is to understand what is the script is doing.

First I can see that the script is executed onRender. Is it correct?

Than I can see that vals[i] has the sum of the values of the "series 1". Do I need to use it some way?

Please let me know

Thanks

Alberto

Attachment: Script_setup.png
(Size: 16.29KB, Downloaded 648 times)

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1010941 is a reply to message #1010779]

Mon, 18 February 2013 09: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

Attachment: running_count_scripted.rptdesign
(Size: 47.96KB, Downloaded 1551 times)

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1011128 is a reply to message #1010941]

Mon, 18 February 2013 17:50

Eclipse User

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.

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1011555 is a reply to message #1011128]

Tue, 19 February 2013 14:40

Eclipse User

Hi Michael and Jakob,

yes it is working!!!

You are simply amazing!!!!

I have implemented the Michael solution and it is working. I didn´t try the Jakob solution but really, really thanks for your help.

I have attached a picture with the chart that is now displaying the accumulated values.

Thanks again both of you Smile

Alberto

Attachment: Chart_Date_Grouping_Accumulated.png
(Size: 47.97KB, Downloaded 771 times)

Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1012093 is a reply to message #1011555]

Wed, 20 February 2013 16:39

Eclipse User

Great! Glad to hear it's working! Let us know whenever you have questions! Smile

Previous Topic:	Table setColSpan
Next Topic:	Some columns are not exported in Excel

Goto Forum:

-=] Back to Top [=-

Current Time: Thu Mar 27 02:29:33 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter