Aggregate child table values in Birt

Question

I have this Birt report that I inherited from another developer, consisting of a child table inside a master table. For each row in the master table, the child table lists items belonging to the current master row item.

The two tables are fed from different data sets, the child table dataset taking a parameter indicating the master item whose child items to fetch.

Now, what I need to do is add a SUM aggregate to the bottom of the master table, showing the total (for all master items) of a certain field in the child table.

Consider, for example, the following data:

MasterItem1
            ChildItem1 SomeValue
            ChildItem2 SomeValue
            ChildItem3 SomeValue
MasterItem2
            ChildItem1 SomeValue
            ChildItem2 SomeValue
            ChildItem3 SomeValue
--------------------------------
                       Total

(Why wasn't this done with grouping instead? Short answer: There are in fact two child tables to each master row, containing different numers and types of fields, so the previous developer probably didn't figure out a way to accomplish this with grouping.)

At first I thought I could simply add another child table inside the Total field, with an aggregate summing up the values from the child dataset. That didn't work, however, since the child dataset requires a parameter indicating the master item whose children to fetch, so there is no way to get ALL values from the child dataset at once.

I'm thinking there might be a way to create an expression that references the SomeValue fields in the child table directly, instead of going through the child data set.

Any suggestions are greatly appreciated.

Answer 1

It should be possible to declare a global variable at the start of the report, then add each of the child values to it in one of the child table row events and output it at the end of the reports - if you're comfortable writing Javascript, this is probably the quickest solution.

If you're not comfortable writing Javascript (I'm not) or if the above technique doesn't work out, you could try either:

• creating a third dataset, combining the master data items from the main report with the child data items from the subreport and outputting the total in a new data table, or
• combining the two existing child data value tables via a union (so that if the master table is A, the main child table is B and the subreport table is C, you have AB union AC), replacing the subreport table and the existing detail rows with new detail rows conditional on child row type, and a total at the end of the report based on the AC values.

Obviously, the latter of these approaches is more complicated - but I think it should be easier to understand and maintain.

Answer 2

The Global Variable is the way to go. For each row in the child table, add the required value to the global variable and then access it for display at the bottom of the table. There is not any hard JavaScript:

var Sum = reportContext.getPersistentGlobalVariable( "RunningAggregate" );
Sum = Sum + row["column Name"];
reportContext.setPersistentGlobalVariable( "RunningAggregate", Sum );

You can then access the Global Variable in the footer of your table via a Dynamic Text item.

Good Luck!

Answer 3

Thanks Mark and Mystik, both your answers led me on the right path!

My final solution is as follows:

1) Declare the sum-variable in the initialize method for the report:

var total = 0;

2) Add each row's value to the sum-variable in the onReder method of the data field containing the values:

total += parseInt(this.getValue());

3) Use the sum-variable as expression in the total-field.

Works like a charm.

---

Update: Found a bug in my solution: the last line was left out of the sum. I think the value of the total-cell in the table footer is being defined before the last line has been rendered.

Fix:

1. Moved summing code from onRender method to onCreate

2. Added the following code to the total-cell's onRender method:

   this.setDisplayValue(total);