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.
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.
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...