SSRS display sum for sub category group by value.

Question

Post reply

SSRS display sum for sub category group by value.

joydev.dey 30829

Mr or Mrs. 500

Points: 546
More actions
August 24, 2016 at 3:31 am

#320905

I have a table in below format
criteria Year count
A 2014 6
A 2015 24
B 2014 2
B 2015 72
I have a stacked chart with
values =count
categorygroup=criteria
categorygroup1=year
Now I have to display the data in the chart as percentage.
The percentage should sum upto to 100 for a particular year.
for example for 2014 it should be 6/(2+6) = 75% for criteria A and
2/(2+6) = 25% for criteriaB.
similarly for 2015 it should be 24/(24+72) = 25% for criteria A and
72/(24+72) = 75% for criteriaB.
The number of years is variable but no of criteria is 2(fixed).
This percentage value should be displayed in the bars of column chart.
Please help me to come out of this scenario.
Thanks,
Joy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Spiff SSCertifiable Points: 7766 More actions · Answer 1

Hi Joydev,

You would use a "100% Stacked Column" chart to achieve this.

Put the Year in the Category groups and the Criteria in the Series groups.

You can use Show Data Labels to get the percentages to display on the bars.

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Spiff (8/24/2016)
Hi Joydev,
You would use a "100% Stacked Column" chart to achieve this.
Put the Year in the Category groups and the Criteria in the Series groups.
You can use Show Data Labels to get the percentages to display on the bars.

If you select only Show Data Lables, It will not work. Again you have to right click on Data Lables and select Series Lables Properties and in Lable data dropdown option select #PERCENTAGE.

Thnx.

joydev.dey 30829 Mr or Mrs. 500 Points: 546 More actions · Answer 3

The approach is good but it only fulfill 50 percent of my requirement.

Please check the image attached of my chart

Spiff SSCertifiable Points: 7766 More actions · Answer 4

Charts are like a matrix so the category and series groups in charts are just that: Groups. Therefore you can use them as a scope for aggregations.

Change your chart back to a normal column chart and put the Criteria in the Category Group and the Year in the Series Group.

So each bar is now the Count grouped by the Year and Criteria.

So get the yearly total you'd want the count grouped by year only.

To get the group name for years first click on the series group then press F4. In the properties window expand Group and make a note of the name (it will be something like Chart1_SeriesGroup1), this is the group name for your series group, or years.

You can then use this in an expression for your data labels. Divide the value at that datapoint by the aggregate scoped scoped over just the Years.

E.g.

=Sum(Fields!Count.Value) / Sum(Fields!Count.Value,"Chart1_SeriesGroup1")

This will divide the datapoint (i.e. the count of the Criteria and Year by the Year total). You can then format the label as a percentage.