Reporting Services Sum of Inner Group in Outer Group

Question

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.

Answer 1

From the data shown, I'd expect the inner group to show a sum of AmountReceived as $800.00, as there are 4 rows each with ReceiptID 1 and AmountReceived $200.00.

This suggests that you are either displaying the value of AmountReceived or the sum of AmountApplied in the inner group. If AmountApplied always adds up to AmountReceived, the simplest way to show this in the outer group would be to use the sum of AmountApplied.

Answer 2

Are you sure you've got the name of the inner group correct in the scope of the sum function? It needs to be enclosed in double quote characters ("), and it needs to match the report's name for the group, which is typically something like table1_Group2, by default - try editing the group, to check.