Reporting Services Sum of Inner Group in Outer Group
- by Spoonybard
I have a report in Reporting Services 2008 using ASP.net 3.5 and SQL Server 2008. The report has 2 groupings and a detail row.
This is the current format:
Outer Group
Inner Group
Detail Row
The Detail Row represents an item on a receipt and a receipt can have multiple items. Each receipt was paid with a certain payment method. So the Outer Group is grouped by payment type, the Inner Group is grouped by the receipt's ID, and the Detail Row is each item for the given receipt.
My raw data result set has two important columns: The Amount Received and the Amount Applied. The Amount Received is how much money in total was collected for all the items on the receipt. The Amount Applied is how much money each item got from the total Amount Received.
Sample Result Set:
ReceiptID Item ItemID AmountReceived AmountApplied Payment Method
------------------------------------------------------------------------------------------
1 Book 1 $200.00 $40.00 Cash
1 CD 2 $200.00 $20.00 Cash
1 Software 3 $200.00 $100.00 Cash
1 Backpack 4 $200.00 $40.00 Cash
The Inner Group displays the AmountReceived correctly as $200. However, the Outer Group displays the AmountReceived as $800, because I believe that it is going off each detail row which in this case is a count of 4 items.
What I want is to see in the Outer Group that the Amount Received is $200. I tried restricting the scope in my SUM function to be the Inner Group, but I get the error "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset."
Does anyone have any suggestions on how to solve this issue?
Thanks.
