SSRS - Get group total within record scope

Question

Sorry if I don't explain this well - reporting services isn't my area of expertise. I believe this should be a simple one (hopefully).

I have a very simple report where I have a row group called 'group1' and then within that group I just have some records (which include a numeric field). Within the 'group1' header, I have put a sum of the numeric field record. What I am now wanting to do is calculate the percentage that each numeric field represents as a total of the group. So, for example, I currently have the following table.

-----------------------------------------
- GROUP 1                    100     
-----------------------------------------
- Field 1 | sadasaadasdads | 10 | 10% <== This is what I am trying to calculate
-----------------------------------------
- Field 2 | sadasaadasdads | 20 | 20%
-----------------------------------------
- Field 3 | sadasaadasdads | 50 | 50%
-----------------------------------------
- Field 4 | sadasaadasdads | 20 | 20%
-----------------------------------------

I am guessing I should be able to create an expression, however I need to know how to get the group total. Any help/guidance on how to calculate the percentage is appreciated.

Adriano Carneiro Adriano Carneiro · Accepted Answer · 2011-09-08T13:58:28

For your dataset, this is what you are looking for:

select field1, field2, value, 
       100.0 * value / SUM(value) OVER(PARTITION BY 1) 
                      /* the SUM gets the total for the whole dataset. If actually 
                         have groups, yous should do PARTITION BY GroupField 
                       */
from yourtable

Now, you just have to work on the formatting.

SSRS - Get group total within record scope

1 Answers