Menu
I have an SSRS report that looks like this (with additional columns such as sales) with rows grouped by region and location.
The goal for the locations is the max for each location as in my query the goal appears on each sales record that I'm summing.
=Max(Fields!goal.Value)
The goal for the regions is the sum of the max for each location in that region group.
=Sum(max(Fields!goal.Value, "LocationName"), "region")
Those first two are no problem, but I'm having difficulty getting the grand total for the report which would be the sum of each region total.
=Sum(Fields!goal.Value, "region")
I can't reference the region group as the total is outside that group - "scope parameter that is not valid for an aggregate function". How do I get the sum of each region (7,000) in this instance?
823
In the tablix data region row group area, right-click a cell in the row group area for which you want totals, point to Add Total, and then click Before or After. A new row outside the current group is added to the data region, and then a default total is added for each numeric field in the row.
Right click on the Amount column data -> Add Total -> Before or After. This will display the sub-total for the Supplier group in the same Amount column after/before as you selected.
You use groups. At the top click the insert menu, then table, then Table Wizard. Pick your dataset and hit next. Now drag the column for the different types of items you want a distinct sum of into the Row Groups section.
In both of the total textboxes, region and Grand Total, you can use the same expression:
=Sum(max(Fields!goal.Value, "LocationName"))
Because the expression is calculated in the current Scope of the textbox, at the Region Group level this will be aggregating the Max goal value in all LocationName groups in the region, and at the Grand Total level this will be aggregating the Max goal value in all LocationName groups in all regions.
You can see this in action. With data like:
(I've doubled up the rows to make it obvious if the end totals are correct)
and a simple table:
The same expression gives the correct result in both the Region and Grand Total Scope:
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
