Search code examples
reporting-servicesssrs-tablixsql-server-2016

Moving subtotal to columns

I believe my problem has to do with the grouping structure.

This is my original report structure where there are detail rows and then a subtotal row:

enter image description here

I need to move the subtotals to columns Hdr Qty. Ordered, Hdr Qty. Remaining, etc. but because it's in the same groupings as the rest of the data it simply repeats that data although I put them into sum functions. I also need to show the details rows still which will end up duplicating the subtotals but that's what I need.

enter image description here

Solution

  • Delete the "table1_Details_Group" group. Make sure you select "Delete group only" so that it doesn't delete the whole row. You want that row to be grouped by Vendor, not by details. Then the sum function in each row will be able to aggregate the values at the vendor level.

    EDIT: If you still want to see the details, you could specify the scope for the aggregate. Like this:

    =Sum(Fields!QTYORDERED.Value, "VENDNAME")

    If you're still not getting what you expected, you may want to consider a subquery to get the aggregates at that scope.