Eclipse Community Forums: BIRT » Passing sub report totals to expressions

Help

Home

Home » Archived » BIRT » Passing sub report totals to expressions

Show: Today's Messages :: Show Polls :: Message Navigator

Passing sub report totals to expressions [message #931311]

Wed, 03 October 2012 04:58

Jeff Stouse

Messages: 28
Registered: May 2012

Junior Member

TIA,

BIRT 3.7.2

(fairly new to BIRT - as you will see below)

I have a settlement report I am trying to generate. In it, I have several queries that pull out single field values, which then need to be added/subtracted from other values, or from a sum of values from a subreport table. I know I am missing the obvious, but how do I:

--> Assign the sum of a related (child) table's rows to a variable that can then be accessed in an expression which subtracts/adds it to values from the master table?

For example, I have a created a query which pulls out two preliminary payments (first and third) and then I add them together. This value then needs to have the sum of expenses (I have a child table with an aggregate of the expenses nested in the master table) subtracted from it. I cannot figure out how to access the sum of the nested tables expenses in the expression.

Any and all help greatly appreciated - please point out what I've been missing/don't understand so far.

Report message to a moderator

Re: Passing sub report totals to expressions [message #931829 is a reply to message #931311]

Wed, 03 October 2012 15:09

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

There are several ways this can be done. One way is to implement an oncreate script for each of the data items and store the value using reportContext.setGlobalVariable. You can then use this value later. Look at the attached example. I created two datasets. Both are just queries on the orderdetails sample table. In the outer table I added three table footer rows. In the middle footer row I inserted an inner table. Look at the oncreate script for the three data items with red background.

BTW another option is to use both datasets in a cube and then display the results in a crosstab.

Jason

Attachment: nestedtableaggregate.zip
(Size: 3.40KB, Downloaded 361 times)

Report message to a moderator

Re: Passing sub report totals to expressions [message #932072 is a reply to message #931829]

Wed, 03 October 2012 20:14

Jeff Stouse

Messages: 28
Registered: May 2012

Junior Member

Jason,

I unpacked and ran the report. The first total quantity ordered was 142. The second quantity ordered total was 80. The difference between quantitys was 1. When I looked at the expression for the [diff] field, it contained only '1'.

Could you repost or explain why the answer is not '62'?

Also, the finished format for the report is supposed to be in a pdf. Will that limit how I set this up? I've not used an oncreate script...

Thanks,

Jeff

Report message to a moderator

Re: Passing sub report totals to expressions [message #932969 is a reply to message #932072]

Thu, 04 October 2012 16:13

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Sorry Jeff, I should have used a persisted global. Take a look at the revised example. Note that the 1 is a dummy value. I am changing it in an onrender script.

Jason

Attachment: twotableaggregate.zip
(Size: 3.43KB, Downloaded 709 times)

Report message to a moderator

Re: Passing sub report totals to expressions [message #937520 is a reply to message #932969]

Tue, 09 October 2012 03:08

Jeff Stouse

Messages: 28
Registered: May 2012

Junior Member

Jason,

I am on 3.6.2. When I try to open your reportdesign I get the following:

Warning: The report file of version 3.2.23 is not supported (click OK)

Then the report design appears to load, but then, when I click on the Preview tab, I get:

ERROR: The report file of version 3.2.23 is invalid for it is greater than the latest supported one. (Click OK)

Report message to a moderator