Monday, 28 November 2011

Reporting Services Win/Lose Sparkline - Setting the Axis Line

Win/lose sparklines are a useful way of displaying performance over time, providing you can boil-down performance to a binary 'win or lose' result.

Sean Boon has a good example of doing this here but I struggled to get the Axis line to display in the right place. I thought I'd share what worked for me.

What I found was after setting the axis to visible, as per Seans blog post, that initially the Axis is drawn at the bottom like so:

This just looks daft and gives the incorrect or at least confusing impression of where '0' is.

The answer to this is buried well within the SSRS interface.

1. First select the sparkline chart. Then press 'F4' to see the Properties grid.

3. Find the ChartAreas property and select the tilde to the right:

4. Within the 'ChartArea Collection Editor' select the 'CategoryAxes' property and click the tilde to the right of that:

5. Within the 'ChartAxis Collection Editor' make sure that the 'CrossAt' property is set to '0':

6. Click OK to get back to the 'ChartArea Collectino Editor' and then click the tilde on the right of 'ValueAxes' and repeat step 5.

7. You should now find that the Axis for your win/lose sparkline goes through the middle of your chart where you'd expect. You'll probably also want to remove the tick marks from the Axis by ticking the 'Hide major tick marks' box in the horizontal axis properties.