Stacked column in SSRS Charts

Question

I have a table which has the following data:

DECLARE @Plan_Summary TABLE (Col1 INT,Col2 INT,Col3 INT)    

INSERT INTO @Plan_Summary VALUES (20,10,10)
INSERT INTO @Plan_Summary VALUES (20,10,10)
INSERT INTO @Plan_Summary VALUES (20,10,10)
INSERT INTO @Plan_Summary VALUES (8,10,10)
INSERT INTO @Plan_Summary VALUES (8,10,10)
INSERT INTO @Plan_Summary VALUES (8,10,10)
INSERT INTO @Plan_Summary VALUES (8,10,10)
INSERT INTO @Plan_Summary VALUES (4,10,10)
INSERT INTO @Plan_Summary VALUES (4,10,10)
INSERT INTO @Plan_Summary VALUES (4,10,10)
INSERT INTO @Plan_Summary VALUES (4,10,10)
INSERT INTO @Plan_Summary VALUES (4,10,10)
INSERT INTO @Plan_Summary VALUES (-10,10,10)
INSERT INTO @Plan_Summary VALUES (-20,10,10)
INSERT INTO @Plan_Summary VALUES (-2,10,10)
INSERT INTO @Plan_Summary VALUES (-7,10,10)
INSERT INTO @Plan_Summary VALUES (-15,10,10)

I have to chart these values into a bar graph with the following requirements:

For Col1 when
- value is between 15 and 25 - Green color
- value is between 6 and 14 - Blue color
- value is between 0 and 5 - Yellow color
- less than 0 red color.
For Col2 and col3 always red.

I used the following code in the dataset:

SELECT SUM(CASE WHEN Col1 BETWEEN 15 AND 25 THEN 1 ELSE 0 END) AS Green,
       SUM(CASE WHEN Col1 BETWEEN 6 AND 14 THEN 1 ELSE 0 END) AS Blue,
       SUM(CASE WHEN Col1 BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS Yellow,
       SUM(CASE WHEN Col1 < 0 THEN 1 ELSE 0 END) AS Red,
       COUNT(Col2) AS Col2,COUNT(Col3) AS Col3
INTO #TMP
FROM @Plan_Summary

SELECT Val,Col
FROM (SELECT Green,Blue,Yellow,Red,Col2,Col3
      FROM #TMP) CSP
      UNPIVOT (VAL FOR COL IN(Green,Blue,Yellow,Red,Col2,Col3)) AS CSP

I added a stacked column chart type in SSRS - but issue is how would I combine all the Green, Blue, Yellow, Red columns into a single stacked column and make sure they get the correct colors?

Thanks.

R. Richards R. Richards · Accepted Answer · 2017-05-16T15:10:13

In this situation, don't UNPIVOT your data. Just do this:

SELECT SUM(CASE WHEN Col1 BETWEEN 15 AND 25 THEN 1 ELSE 0 END) AS Green,
       SUM(CASE WHEN Col1 BETWEEN 6 AND 14 THEN 1 ELSE 0 END) AS Blue,
       SUM(CASE WHEN Col1 BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS Yellow,
       SUM(CASE WHEN Col1 < 0 THEN 1 ELSE 0 END) AS Red,
       COUNT(Col2) AS Col2,COUNT(Col3) AS Col3
FROM @Plan_Summary

Then, add each column as a value in the chart. See below:

Stacked column in SSRS Charts

1 Answers