June 01, 2011
SSRS Charting: How to Align the Scale on Primary and Secondary Axes Using the Invisible Series
I’m writing this to help the poor person who might get stumped when putting together a fancy chart involving a primary and secondary axis where they just don’t seem to line up. The intended audience is people who are not faint of heart and have done a report or two in their day. I’ve come across this problem a couple of times now.
The example here is a chart with (originally) 3 different series, two of which were calculated values. The problem was that the red bar chart representing actual sales should be lower than the combined value of Tier 1 and Tier 2.
Tier 1 and Tier 2 are plotted on the Primary axis and Invoiced is plotted on the Secondary axis, which is not shown by design. It looks like Invoiced outperforms goal, which is not the case here. So how do I align the values? The answer was adding another series which would also be plotted on the secondary axis. The formula here, in hopefully plain English, is: If gold – red is less than 0, then use 0, otherwise return gold – red. (Note that the IIF or SWITCH functions could be used here). That will add the difference on top of the red bar. Here’s the trick: make this series invisible. This is done by not showing a legend and making the color value “No Color” in the series properties. The result is here for the same data with this invisible series: