SSRS bar charts tips and tricks

SQL Server 2008R2 | SQL Server 2012

This post shows some tricks with SSRS report charts that I stumbled over some weeks ago.

I start with a very simple bar chart showing some values along a full year (12 months):

Here are some variants of this chart together with some details how the chart was created using SQL Server Reporting Services:

	Highlight plan values Here the last three months contain the plan values. The shading of the bars is done by an expression. The vertical line is not the vertical axis, but a line chart series. How can this line be a true vertical? Like with xy scatter charts, you can specify each point’s x and y coordinates separately. The x coordinate goes into the category field for the series. So in this case there are two points: (9.5,0) and (9.5,1). This line is on the secondary vertical axis with min/max set to 0 and 1. Since the labels on the horizontal axis are automatically shown as scalar values, if one of the chart’s series contains x/y data, I had to create these labels for the horizontal axis using a second chart which is placed below the bar chart.
	Another option for highlighting plan values In this chart I didn’t use a line to separate the planning values but instead I inserted some space. One option to do so is to add an extra row of data with value zero. But by doing so, the gap between the two regions becomes too wide (in the example on the left, the gap is smaller than the width of a bar). The gap can be created by correcting the category value for the data rows that correspond to the planning data. In my case, I hard-coded the start for the planning data (usually it would depend on other data columns or report parameters). My category expression looks like this: =Fields!Month.Value+ iif(Fields!Month.Value>9,0.3,0) Note, that for all bars that correspond to the planning data, a value of 0.3 is added to the horizontal position. In order to get a proper placement of the labels, the labels again are created using a separate chart as the data labels for bars of height 0. In order to explain this, the following screenshot enlarges the bars and shows the axis of the second chart:
	Plan values as a stepped line The plan values are shown as a stepped line here. Bars that rise above the stepped line are showing a better actual compared to the plan. The stepped line is a simple basic series type. However, usually the steps would be located in the center of the bars like this: In order to correct this, you can simply take off 0.5 from the x coordinates of this series, so in my case the category field’s expression looks like this: =Fields!Month.Value – 0.5 In order to create a proper stepped line for the last bar, the last row of data for the stepped line has to be duplicated (one data point more than for the bar chart needed).
	Shaded areas in the chart background This effect is relatively simple. I used an area chart in the background for the months with the plan values. Again, the area chart’s x coordinates have to be corrected by 0.5 in order to fit to the bar chart’s positions. The formula for the background area chart results in blank for all months without the shading (don’t use zero, because then you would create a linear slope between September and October in this example).
	Colored horizontal axis This chart type was once proposed by Prof. Hichert for a visual separation of actual and plan values. Here, the horizontal axis is hidden. The colored axis is formed by two area chart series. In order to prevent overlapping with the bar chart, they have to be on the same vertical axis as the actual values but with a negative value which as to be scaled depending on the maximum of the values for the bar chart.
	Empty horizontal axis This is just a variation of the previous chart and again one of the examples of Prof. Hichert. Instead of using two different colors for the area chart forming the horizontal axis, here I’m using different fill colors.