SSRS Only Totaling Middle Rows in Sum of Amounts by Customer

Question

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?

score 0 · Accepted Answer · answered Dec 16 '20 at 11:03

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.

Create new report
Add datasource and dataset query
Add a table (tablix)
Drag your State and Customer fields from the datsset to the table
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
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.

SSRS Only Totaling Middle Rows in Sum of Amounts by Customer

1 Answers1