Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 12:49 Go to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
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 19:25 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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.

Michael

Developer Evangelist, Silanis
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1008595 is a reply to message #1008513] Wed, 13 February 2013 08:57 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
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 15:44 Go to previous messageGo to next message
Eclipse GuestFriend
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 07:54 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
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 21:06 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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.


Michael

Developer Evangelist, Silanis
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009494 is a reply to message #1009297] Fri, 15 February 2013 08:28 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
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 #1009497 is a reply to message #1009494] Fri, 15 February 2013 08:30 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
Hi Again,

Sorry for the previous attached picture.

I hope this is better Smile

/Alberto
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009859 is a reply to message #1009497] Fri, 15 February 2013 21:50 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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.


Michael

Developer Evangelist, Silanis
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1009874 is a reply to message #1009859] Fri, 15 February 2013 22:46 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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 #1010779 is a reply to message #1009874] Mon, 18 February 2013 08:20 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
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
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1010941 is a reply to message #1010779] Mon, 18 February 2013 14:16 Go to previous messageGo to next message
Eclipse GuestFriend
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:



  1. make sure your data is retrieved ordered by date (add "ORDER BY DATE" to your sql query)
  2. add a computed column to your dataset, name it "running_count", set type to integer, and expression to 0. click ok to save
  3. while the dataset is selected, click the script button to open scripting for that dataset
  4. in "before open", type:
    i=0;
  5. in "onFetch", type:
    i++;
    row["running_count"] = i;
  6. 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)
  7. 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 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1011555 is a reply to message #1011128] Tue, 19 February 2013 19:40 Go to previous messageGo to next message
Alberto Morando is currently offline Alberto MorandoFriend
Messages: 9
Registered: February 2013
Junior Member
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
Re: HowTo sum (accumulate) aggregate values in chart BIRT Eclipse [message #1012093 is a reply to message #1011555] Wed, 20 February 2013 21:39 Go to previous message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1925
Registered: July 2009
Senior Member

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

Michael

Developer Evangelist, Silanis
Previous Topic:Table setColSpan
Next Topic:Some columns are not exported in Excel
Goto Forum:
  


Current Time: Fri Oct 22 12:35:45 GMT 2021

Powered by FUDForum. Page generated in 0.01858 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top