This is usually the time of the year when I get to my laziest best. The cold weather and the approaching holiday season brings out the sloth in me and I have a huge backlog of articles I need to read as well as tend to. But somehow I have pulled up my socks and managed to come up with this article on how to add a target line to a horizontal bar chart in SSRS.
For the benefit of people who are not so familiar with the horizontal bar charts in SSRS, there is no out-of-the-box functionality for achieving a target line (unlike the column charts, where we can implement a line chart as detailed here). So when this question was raised in the MSDN forums, I put on my thinking cap and suggested a solution based on having the image of a line as a marker, something like shown below
The steps are mentioned in the forum discussion here. But a disadvantage of this approach was that it wouldn’t accommodate an increase or decrease in the number of members in the Y axis. For e.g., if the line image was designed for 5 members, then an increase in the number of members would mean that the image would spill over the X axis. Similarly, a decrease in the number of members would mean that the lines fail to reach the X axis. So I came up with an alternate solution using striplines and the steps are given below:-
1) Make a simple bar chart with a category and measure. In this example, I have used Year as the category field and Cnt as the measure value.
2) Click on the X axis to select it, then right click and unselect the option ‘Show Major Gridlines’. This will remove the gridlines on the chart.
3) Click on the X axis to select it, and then select the collections button in the StripLines property
4) Click on the Add button in the ChartStripLine Collection Editor. The properties should appear as shown below now.
5) Now change the BorderStyle property as Solid and enter the field or the number at which you want the target line to come in the IntervalOffset property as shown below
6) Select OK and click on the preview button. You should be able to see the target line in the bar chart as shown below.
7) You can increase the width of the target line by changing the BackgroundColor property to the BorderColor (in this case Black) and by increasing the StripWidth property in the ChartStripLine Collection Editor shown in step 5.
A disadvantage of this method is that the target line appears behind the bars. Apart from this, it works pretty well and you have a clever workaround the next time someone insists on having a target line on their bar charts.