SSRS Stacked Column Chart - Multiple column overlap

Question

In SQL Server Reporting Services 2008 R2, I have the following dataset.

Date   | Value 1 | Value 2
--------------------------
Week 1 | 52      | 57
Week 2 | 49      | 63
Week 3 | 88      | 71

I have a Stacked Column Chart with the X axis of Date, and the Y axis of Value. The columns in the chart currenly shows Value 2 on top of Value 1. This shows the column as the total of Value 1 and Value 2. So for Week 1, it is 109 etc.

Now my question is, how would I get the chart to show the total of each column to be the highest Value in the dataset? This would still show both values but would have the entire column of the lowest value with the remaining value on top. So for week 1, the total would be 57. The column for Value 1 would be 52 and the column for Value 2 would be 5.

This may be confusing so I have added a dummy image of what I intend the final graph to look like.

enter image description here

jrouquie jrouquie · Accepted Answer · 2012-11-13T17:22:36

Maybe you can write a query like this:

SELECT LEAST(value1, value2) as value1, GREATEST(value1, value2) as value2
  value1 < value2 as color
FROM ...

SSRS Stacked Column Chart - Multiple column overlap

1 Answers