开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜