How to apply aggregation on aggregation in a group?

[KapsHere]

My BIRT report consists of table which contains the following columns: Frequency, FrequencyPercent, CumulativeFrequency, CumulativePercent. There are two scenarios to be dealt with:
1) No grouping of data. The entire data is placed in a one(1) table.
2) The data is grouped by another column.

The columns stated above are dependent upon each other, e.g. FrequecencyPercent is based on Frequency, CumulativeFrequency is based on Frequency and CumulativePercent is based on FrequencyPercent/CumulativeFrequency. In case of scenario #1 mentioned above, this is achievable using Aggregation builder, but in case of scenario # 2, BIRT does not display any data for CumulativePercent column, which is based on FrequencyPercent column .

Any idea on why is this not working and how can i get it to work?

Thanks,
Kapil

[mwilliams]

Hi Kapil,

Can you attach your report design so I can see the visual of everything you said in your first post? What version of BIRT are you using?

[KapsHere]

Hi Michael,

Thanks for the prompt reply!

I can't really attach the report design because of the Information Security policies of the company i work for!

I can try to be more clear and provide more details about the same though. Here they are:

1) I am using scripted data source
2) The main issue is: i am using an aggregate function, using the aggregation builder, on a column, which itself has been computed using the aggregation builder (lets call this 'aggregation on aggregation'). This works when the data in the report is displayed using only a table, without any groups. But when i try to group the data on some data set output column, the column which has 'aggregation on aggregation' does not show any values.

Please let me know if you want me to elaborate more,
Thanks,
Kapil

[KapsHere]

the BIRT version is 2.3.1.

[mwilliams]

Kapil,

So, you're using aggregations to create columns for your table rather than the computed column section in your "Edit Data Set" box? If there is any way for you to create a similar example using the sample database so that I can see what you're doing, that would be the easiest. If not, I'll just keep asking questions til I figure it out .

[KapsHere]

Hi Michael,

Thanks again for taking time out for my query, inspite of the vagueness and lack of clarity in it!

To make things simple, i have created two BIRT report design files (.rptdesign) which reflect the problem I am facing with my reports. Both reports use the sample DB that comes with BIRT. Both reports have the following columns:

1) FirstName: comes from the Database

2) CreditLimit: comes from the Database

3) CreditLimitPercent: Calculated using Aggregation Builder function "PercentSum" on the field 'CreditLimit'

4) CreditLimitCumulativePercent: Calculated using Aggregation Builder function "RunningSum" on the field 'CreditLimitPercent'

Both reports have the columns described above in the sequence shown above. The only difference is, one of the reports groups the data on the first character of the FirstName and the other doesnt.

The issue is, the report which groups the data, doesn't give correct results for the column 'CreditLimitCumulativePercent'. If possible, kindly let me know what am i doing incorrectly.

Please find attached the report design files and let me know if you need any more information from my side on the same.

Regards,
Kapil

[mwilliams]

Kapil,

In the grouped report, do you want the cumulative% to show the cumulative percent for the group or do you still want the cumulative % for the entire report?

[KapsHere]

Michael,

In the grouped report, i want the report to show the Cumulative% for the group and NOT the entire report. This is the reason, I have chosen the group while creating this binding using Aggregation Builder.

Regards,
Kapil

[mwilliams]

Kapil,

Attached is a working copy of the report with groups. It seems that if you create an aggregation on an aggregation in groups, it only takes the first one of the group, so I figured the CreditLimitPercent column with a data element instead and then used another aggregation on that data element for the cumulative percent column. Hope this is what you're looking for.

[KapsHere]

Michael,

Thanks for the option, but i have already tried this(using a data element instead of the first aggregation). When i make this change, the values in the table are correct and as expected, BUT, the column headings vanish!!

There are two main differences between the sample report that i sent you on this forum, and the actual report. In the actual report:
1) I am using a scripted data source.
2) My column headings are not static labels, but are Report Parameters that are passed to the report at run time from Java side.
3) The column names are different and are in different order

I cannot attach the original .rptdesign file i am using, but i can provide a partial screenshot of the report which is getting generated after i make the changes made in your last post. Please find attached the screenshot with this post.

Some more details about the report:

1) In the screenshot, the 2nd column is Frequency, the 3rd column is the FrequencyPercent column and the last column is the CumulativeFrequencyPercent column. The screenshot shows a partial section of the report when i make the change as mentioned in your last post.

2) This problem with column heading remains even if i remove the last column. After doing some hit and trial with this problem, i have come to the conclusion that this is because BIRT has some issues if you try to mix normal binding and binding created using aggregation when using scripted data and report parameters.

3) I am using a scripted data source, named as ReportDAO. On the open() event of the data source, i call the ReportDAO.getData() method, which is actually implemented on the Java side and basically returns me an ArrayList of String arrays. On the fetch() event of data set, i get the data in the current position in the ArrayList and increment the position counter by 1. Ofcourse i also check for end of ArrayList using its size() method.

If possible, please let me know your thoughts on why could this be happening. Could you recreate this scenario using scripted data source so that i can come to know whether it is a bug in BIRT or is it something that i need to change.

Also, since 'aggregation on aggregation' is not working on grouped reports with static labels and sample DB, does this mean it is a bug in BIRT? Can this be reported?

Regards,
Kapil