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?
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.
State
andCustomer
fields from the datsset to the tabledetails
row group, right-click this and go to properties, change the grouping to group by State and CustomerAmount
field from the dataset to the tableThis 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.