How to apply aggregation on aggregation in a group?

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

Find more posts tagged with

Comments

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

mwilliams

Kapil,

So, the parameters for the column headings come from the scripted dataSource? What is the reason for not being able to make them static labels?

KapsHere

Michael,

The main reason is internationalization and to maintain single properties file on the Java side which can be translated by language experts. So, for example, for our customers who understand English language, the column heading will say 'Count', but it would say something else for customers who understand French. But again, the column headings are fixed for a type of report, only the language in which they will be rendered, will change.

Regards,
Kapil

mwilliams

Kapil,<br />
<br />
Sorry for the delay in my response. With the holiday last week and meetings all this week, I've not been able to get in here much. If you could create a sample scripted dataSet report for me to look at that's set up exactly like the one you can't send me, only with non-company data, along with the internationalization setup, I can then see exactly what you're seeing with your setup and see if there is anything I can do to help. If we don't get it then, it'd definitely be something I would log as a <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>bug</a>.

KapsHere

Hi Michael,

Sorry for the late response. I was actually busy with issues related to some other reports(for some of them i had posted other queries to which you had replied) and put this issue on a back burner. But now, I am only left with the 'pending issues' and this is one of them.

I am attaching the sample report, which uses scripted data source and is almost the same as the one created for my project. To recap, the problem is still the same:
1) If i use aggregation on an aggregated column, in a group, it uses just the first value in the group. In the attached report the column 'Cumulative Percent' uses aggregation on column 'Percent' which uses aggregation on column 'Frequency'.
2) If i try to use a binding to calculate the value of 'Percent' column as mentioned in one of your solutions with sample data, the column headings, which in my case are report parameters, vanish!

Please let me know if there is any solution/work around you can come up with?

Also, aren't both the points mentioned above bugs in BIRT??

Regards,
Kapil

mwilliams

Kapil,

What values do I need to put in the report parameters to run the report? There are no default values.

KapsHere

You may use any value you like. I don't have default values in there cause thats how it is supposed to be in my case.

Regards,
Kapil

mwilliams

Kapil,

What values do you use? If I use random values, I get a bunch of errors.

KapsHere

For all the report parameters you may use any default String value. For the data set, i am not sure what all options you have. You can look at the Script Data Source and Script Data Set events, in the outline view, to get an idea of how we are using it in our project.

Basically try and pass some values for all columns in the Data Set and modify the event handling javascript code if required.

Regards,
Kapil

mwilliams

Kapil,

Is there a class file you're not giving me? The code I'm having problems with is:

reportDAO = reportDAOObject;
freqData = reportDAO.getFreqDistDataControlling();

KapsHere

oh! I am sorry! i guess i did not make myself clear.

I cannot give you the class files because of security reasons and i am not very sure of how BIRT is configured for our project. All i can tell you is it a JEE project and the passing of values for report parameters and the call to the Java classes is made at run time. If possible, just use any class file and method call and get the data populated in the Data Set columns at run time. It could be any data, just that the frequency column should have a integer in it.

The getFreqDistDataControlling() method returns an ArrayList<String[]>.

Please let me know if you would like more details from my side.

Regards,
Kapil

mwilliams

Kapil,

Ok. Can you just give me a sample set of fake data to plug into the scripted datasource to make my dataSet look like yours would? I don't need a class file to do a scripted dataSet, just data to make it look right for your situation. Thanks.

KapsHere

Michael,

Heres some fake data that i just typed in:

Controlling Element Data Element Frequency

1 20 5
1 30 14
1 45 6

2 60 12
2 65 19
2 74 21

3 40 8
3 45 15
3 55 21

Let me know if you need anything else.

Regards,
Kapil

KapsHere

Hi Michael,

Any updates on this issue?

Regards,
Kapil

mwilliams

Kapil,

Sorry for the delayed response. I plugged your values into an array to create a scripted dataset with those values. With the report setup as is, the aggregation on an aggregation in the grouping didn't work. I replaced the frequencypercent aggregation with a data element of type float with the expression row["frequency"]/row["frequency_sum_aggregation"]. I then changed the aggregation that was based off of the original aggregation and based it off this data binding instead and it worked as expected. I think this might be the way I described to do it earlier, but I'm not sure. Let me know if it works for you.

KapsHere

Hi Michael,

Yes, we have already tried this solution. As i had mentioned earlier, if we do this, the data is correct, but the column headings vanish. The column headings in my report are dynamic values passed as report parameters at run time.

Can you try the same solution with dynamic column headings?

Regards,
Kapil

mwilliams

Kapil,

My mistake. I forgot about that. I will give it a try.