SSRS Reports - Pie Chart with 50, 60, 70, …100

Question

I have the following dataset:

For the sake of simplicity, let's say these are test scores. Each test score belongs to a student.

I would like to put these values in a pie chart on an SSRS report. I would like the pie chart to display them by percentage. Scores will be separated in groups of percentage.

90%-100%

80%-90%

70%-80%

etc.

The pie chart will display these groups as slices of the pie. I will then display the number of occurrences for each section on the pie chart itself.

Using the above dataset, the expected result would be:

90% - 100%      10
80% - 90%        5
70% - 80%        2
60% - 70%        1

Any idea how I would accomplish this?

I would just recommend choosing a different type of chart. A stacked bar chart (or even a normal bar chart) would be a much clearer way to show the data. Stephen Few has a very good explanation as to why: perceptualedge.com/articles/visual_business_intelligence/… — Mike D.

Trubs Trubs · Accepted Answer · 2014-10-20T20:39:47

assuming the data is in a table called #temp

SELECT Score
INTO #temp 
FROM (
SELECT SCORE=0.630500 UNION all
SELECT 0.777000 UNION all
SELECT 0.798166 UNION all
SELECT 0.806400 UNION all
SELECT 0.844800 UNION all
SELECT 0.871400 UNION all
SELECT 0.878400 UNION all
SELECT 0.885300 UNION all
SELECT 0.945600 UNION all
SELECT 0.949200 UNION all
SELECT 0.951200 UNION all
SELECT 0.952900 UNION all
SELECT 0.984850 UNION all
SELECT 1.000000 UNION all
SELECT 1.000000 UNION all
SELECT 1.000000 UNION all
SELECT 1.000000 UNION all
SELECT 1.000000  )X

Then you just have to work out the grouping to get the data into percentiles - The inner select does that - the outer select just creates the display grouping

SELECT StartRange
        ,CAST(StartRange AS VARCHAR)+ '% - ' + Cast(StartRange+10 AS VARCHAR)+ '%'AS RANGE
        ,Total
FROM (
        SELECT  CAST(CASE WHEN SCORE=1 THEN .9 ELSE SCORE END * 10 AS INT)*10 AS StartRange
                ,COUNT(*) AS total
        FROM #temp
        GROUP BY CAST(CASE WHEN SCORE=1 THEN .9 ELSE SCORE END * 10 AS INT)*10
    )x

gives the results

StartRange  RANGE       Total
60          60% - 70%   1
70          70% - 80%   2
80          80% - 90%   5
90          90% - 100%  10

SSRS Reports - Pie Chart with 50, 60, 70, …100

1 Answers