SSRS Summing with Groups

Question

I have created an SSRS report that will display multiple Orders and the Items that are in the Order. It looks something similar to:

Order #  |  Order Amount   |   Item   |   Item Cost   |   Item Qty
------------------------------------------------------------------
1000              $5,000
                               Foo           $1,000              2
                               Bar           $3,000              1 
1001              $6,000       Foo           $1,000              6

It will list the Order on the first row and the details of the order on the rows underneath it.

I have my report set to group on my Order Id. The first row would be a header row for the group, and then the order item rows are basically details rows in the group.

The query I have to return this data is like this:

SELECT * FROM Orders LEFT JOIN OrderItems ON Order_Id = Order_Id

The data returned from the query will look like:

OrderId      | OrderAmount      | ItemName     | ItemCost     | ItemQuantity
----------------------------------------------------------------------------
1000           5000               Foo            1000           2  
1000           5000               Bar            2000           1
1001           6000               Foo            1000           6

In the report I do a group on the OrderId - the header row displays Order Amount. The details rows display the item name, cost and quantity. Everything works great until I want to put a total for all of the orders on the bottom of the report. If an order has more than one item then it will count the order amount multiple times (which is expected since the data is there multiple times).

Is there a better way that I could design the report or the query?

Answer 1

Add a footer row to your datagrid and add a sum of the DATASET OrderAmount column, not of the FIELDS!OrderAmount. That should do it.

Answer 2

In your table footer, use the ItemCost * ItemQty and SUM on that. Use this:

=sum(Fields!itemcost.Value * Fields!ItemQty.Value)

I set up a small example with your data and this works, with what you have displayed.