CODEWITHSUNDEEP
chartsssrs-2008-r2
salvationishere
salvationishere

Reputation: 3511

How to create a percent on a column graph chart?

I have a column graph chart in SSRS 2008 R2 and now I want to display the values as a percent. Currently it just displays the sum totals. My dataset looks like:

people_count    facility    met_reqs
12  Chattanooga, TN 0
9   Clarksville, TN 0
6   Columbia, TN    0
51  Chattanooga, TN 1
22  Clarksville, TN 1
28  Columbia, TN    1

As you can see, each city has two rows: the first row is the count of persons who did not meet requirements and the second row is for each city which did meet reqs.

Where my last query is:
select 
count(distinct people_id) as people_count,
facility,
case when total_los/total_visits *3/7 >= 1 then 1 else 0 end met_reqs
from #final
group by facility, case when total_los/total_visits *3/7 >= 1 then 1 else 0 end

Currently I have this chart to display the sum of people_count for Chart Values, Category Group = facility, and Series Group = met_reqs.

This looks like: enter image description here

But now for the Y-axis I want this to instead display percentage of persons/facility who met the requirements. How can I do this? So the Y-axis should instead have a range of 0-100%.

Upvotes: 0

Views: 5053

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

Have you considered a 100 % Stacked Column chart?

enter image description here

If you change your existing setup this seems to achieve your requirements:

enter image description here

enter image description here

enter image description here

If this isn't what you're after, can you please update your questions with some more details?

Edit after comment

OK, to meet the specific requirement of displaying % Meeting Requirements only. With the same Dataset, use a Chart type of Column (i.e. the first option) and remove the Series Group:

enter image description here

enter image description here

Change the Values expression to:

=Sum(IIf(Fields!met_reqs.Value = 1, Fields!people_count.Value, Nothing))
    / Sum(Fields!people_count.Value)

i.e. getting the % of total people_count where met_reqs = 1 compared to the total people_count in each group.

For the sake of completeness I changed the number formatting on the Y axis to be percentage.

This looks OK to me:

enter image description here

Hopefully this help get a bit closer to the goal.

Upvotes: 1

Related Questions