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.
Hi Jason,
Iam using SSRS 2008 R2. I want to create a heat map similar to what we have in Dundas. I don't know how to install Dundas or how to start, can you please guide me. Can we create a treemap with SSRS 2008 R2
Check this link – http://road-blogs.blogspot.com/2012/03/heat-maps-for-ssrs-using-map-control.html
Hi, What I did was added a derived calculated value as a line and I finalled got my line on the column chart 🙂
Hi Nick,
It is possible to make a line chart in a column chart, but not in a bar chart. So you might still need to use the above technique in the case of a bar chart 🙂
Cheers,
Jason
How will this work when the chart is changing dynamically with parameters? the line will not be in the center anymore if that is what I want.
if anybody has figured out how to bring the strip lines to the front in a bar chart then please let me know.
A lot of your pictures are missing (Chrome 34 and IE 10) 🙁
Hmm, unfortunately, I was using imageshack to upload the pics, and looks like they delete the images after a couple of years. You can get most of the images by following this link – http://web.archive.org/web/20130102153307/http://www.sqljason.com/2011/11/adding-target-line-to-horizontal-bar.html
Just what I need thanks a lot!
Perfect. Thank you
Me salvaste la vida!!! thanks a lot!
I just had this very same problem, but my bar chart was individual bars in a table and the striplines didn’t work for me (I forget why).
My solution was to use stacked bars to implement the vertical line as one of the stacked series. I couldn’t get the line to join up across the vertical gaps between the bars, so it is a dashed line, but it still looks (dashingly) good.
You need 4 stacked series to pull this stunt off:
Series 1 is the data value clipped at the target value and the bar segments are the normal colour,
Series 2 is 0 if the data value is at least the target value, otherwise it is the positive difference between the two. The bar segments of this series are invisible,
Series 3 is set to a small proportion of the x axis limit (I divided the maximum value by 150) to make a line-width bar. The bar segment fill colour for this series is the desired colour of the target line,
Series 4 is either 0 if the data value is less than or equal to the target, otherwise it is the positive difference between the target and the data value. These bar segments are the normal colour again.
I didn’t bother to compensate for the width of the target line, but you can optionally subtract it from the Series 4 value (clipping at zero).
The Series 3 bars are configured to be taller than the others, to emphasise the dashed-line effect. In my case I set the CustomAttribute PointWidth to 1.7 for the line, and to 1.3 for the other bar series to achieve this.