SSRS - Get group total within record scope
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 开发者_如何学Go| 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.
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.
精彩评论