Hamza Bensaid Hamza Bensaid - 6 months ago 18
SQL Question

Calculating Percentage of Percentage in SSRS

Figure A

Figure A


Figure B

Figure B


Figure C

Figure C


I have created the table (figure A) that is giving me the result (figure B) where the subcategory percentages are taken from the TOTALS (example: for 1/16/2016 | 3.04% + 11.13% + 0.02% = 14.19%).

I need the subcategory percentages to be taken from the respective category total making it the new 100%, as displayed in figure C: the desired result (example: for 1/16/2016 | 21.40% + 78.42% + 0.17% = 100%).

Answer

In your example you want to refer to that cell with a total of 584 for Category B. SSRS doesn't have the option for you to refer to a value within multiple groups like that. You can only provide one scope override. To get this functionality you can add a subquery to your dataset that aggregates those values in a new column.

So for example your dataset should end up looking like this:

CategoryName    SubcategoryName    Number    CategorySubtotal
Category B      subcategory a      125       584
Category B      subcategory b      458       584
...

Now you can easily calculate the percent of total for each category in the report.

Comments