reporting-services - How to write SSRS 2017 expression for calculating percentages when report contains Row Groups level hierarchy data

Question

I'm trying to show monthly data for products. A month can have any number of products from A to Z depends on availability of products for that month. For example, January 2020 has products A, B, and C. Total is sum(amount) for these products.

I have taken Column Groups hierarchy as MonthYear and Product; Row Groups hierarchy as City, State, Region, and Country.

I'm able to show Total column at State, Region,and Country level by adding up at group level.

But I'm facing difficulty in showing Product% column figures especially at State, Region,and Country level.

For Product%, I'm trying to write something like sum(amount)/[sum(amount) for ALL Products].

Could you please help me in writing SSRS expression for achieving Product% figures at State, Region,and Country level.

标签： reporting-servicesssrs-tablix

Answer 1

Assuming you have a row group called 'groupCity' then this should work.

=SUM(Fieldsamount.Value) / SUM(Fields!amount.Value, "groupCity")

You will need to change the expression to match the rowgroup name for each additional instance that you need it (so probably state, region and country)