Search code examples
sql-servervisual-studioreporting-servicesssdt-2019

SSRS Only Totaling Middle Rows in Sum of Amounts by Customer

I'm new to SSRS/SSDT (2019) and I assume I'm missing something simple?

I have a simple table that looks something like this:

State Customer Amount
CA Stack 12
CA Stack 5
CA Stack 3
CA Stack 15
ID Notstack 20
ID Notstack 9
ID Notstack 11
ID Notstack 7
ID Notstack 14

When I put it in SSRS, the amount would show just the first row for that customer, but separate the customers, like this:

State Customer Amount
CA Stack 12
ID Notstack 20

I right-clicked on the Amount field in the Design page and selected "Add Total", which ALMOST adds the total of the Amount column per customer, BUT, it only sums the MIDDLE ROWS? So:

State Customer Amount
CA Stack 8
ID Notstack 27

What am I missing here?? And what's the SOP for asking questions about SSRS? Do you need my query? Or?

Solution

  • If you only want to show the totals and not the detail rows then typically you would follow these steps, the first few you have obviously already done but are included for the sake of completeness.

    1. Create new report
    2. Add datasource and dataset query
    3. Add a table (tablix)
    4. Drag your State and Customer fields from the datsset to the table
    5. Under the main design panel, you will see a row group panel with a details row group, right-click this and go to properties, change the grouping to group by State and Customer
    6. Finally drag the Amount field from the dataset to the table

    This final step should set the expression to be =SUM(Fields!Amount.Value) as it's in a grouped context.

    There are many different ways of achieving the above, this is just one of them. The key is understanding how grouping and the context of a textbox is affected by its scope (which row and/or column group it belongs to).

    You could edit your current report by change the amount expression to that shown above and then deleting the details group, leaving just you totals group but if you get stuck follow the steps above (done from memory so may not be 100%) and you should get close to what you want.