Calculate in Report Footer

[cubsguy]

Currently I have a report that subtotals then has a grand total in the report footer. The subtotals are according to a field [Group Size] that has three possibilities, Large, Small, or ASO. The subtotal and grand total calculate on a cash field [Monthly Earned].
What I would like to do in the grand total is add the Large and Small then subtract the ASO cash amounts. (L+S)-A. I was thinking I need to do an IIf statement or can I use the subtotals for each grouping? Right now all 3 groups are just being added together using Sum([Monthly Earned]). Thanks in advance.

 

[KenHigg]

In the underlying query for the report, do another fld and do something like:

myASOFlds: iif([groupsize] = "ASO", [Monthly Earned], 0)


This should give you a fld with 0 in every record except those that have "ASO" as the group size.

Then you can simply subtract sum([myASOFlds]) from sum([Monthly Earned])

Would this work?

 

[cubsguy]

That's not a bad idea, however I think I over simplified my report. There are 7 fields that are subtotaled and totaled. All the detail is included in the report, about 40 pages. I need to have the ASO records still be included and still subtotal. I'm not sure how to include my report, I can't even get the data to look good in this post. Here's a chunk of the data. I have the data sepreated and subtotaled by group size then in the report footer I have a grand total that currently adds all 3 subtotals but I need it to add Large and Small then subtract ASO in the grand total for all fields.
If I created extra fields in my query I'm not sure how I would be able to display the ASO records the same way I display Large and Small group records.



Group Name Group Size Unearned Monthly Earned Y-T-D Earned Billed Monthly YTD Billed Monthly Receipts YTD Receipts
ACME Large Group 0 132167 660836 132167 $66,836.00 132167 660836
ACME Large Group 0 697015.42 3113490.25 697015.42 $3,113,490.25 615996.65 3097615.1
ACME Large Group 76435.6 73954.16 375578.24 76435.6 $377,100.48 73954.16 375578.24
ACME Small Group 7896.6 9538.97 40320.92 7896.6 $41,471.06 7896.6 41471.06
ACME Small Group 7063.07 7684.85 26116.79 7063.07 $28,576.23 7684.85 26116.79
ACME Small Group 4112.91 4112.91 20204.57 4112.91 $20,564.55 4112.91 20564.55
ACME Small Group 3602.58 3602.58 18012.9 3602.58 $18,012.90 3602.58 18012.9
ACME ASO 0 438 2067 438 $2,067.00 438 2067
ACME ASO 0 114 561 114 $561.00 114 561
ACME ASO 0 168 831 168 $831.00 168 831
ACME ASO 0 222 222 222 $222.00 222 222

 

[cubsguy]

I did it. I created a subreport from 2 queries each calculating one of the 2 parts of my equation. Then creating the report to calculate the difference between the 2 queries. It may have been the long way around, extra queries and an extra report, but it works.

Thanks for your suggestion KenHigg, that sent me down this path.