Dynamic SSRS Chart Data Label Visibility

9 May, 2012 (09:30) | SSRS | By: Mark V

The other day, there was a question posted on the #ssrshelp hash tag on Twitter. The question was whether SSRS would allow you to show data labels for a chart, but ONLY on the last item in a series. For example, if you have months ranging from January to December, can you show the data label only on December’s value?

You can indeed. Here is how you do it.

Let’s start with a basic data set query (the source does not matter since were are hardcoding everything here.

SELECT     '2011-01-01' AS ValDate, 1 AS Val
SELECT     '2011-02-01' AS ValDate, 2 AS Val
SELECT     '2011-03-01' AS ValDate, 1.5 AS Val


We’ll call it Dateset1. It returns the following:


Let’s see a chart based on this data with the data labels turned on.


This is basic stuff. Now, let’s add the necessary items to hide all of the data labels except the one for 2011-03-01.

First, we’ll add a new Report Variable. Click on Report and choose Report Properties.


We create a new variable called MaxDateVal. We then click on the Expression button, shown in the rectangle above.


Don’t worry about the red underline here. Click OK when you’re done.

Now we head over to the Visible property of the Chart Series data labels, shown below in the rectangle near the bottom.


Click on the property value and choose Expression.


Enter the following expression.


Click OK and then run the report.


Notice only the MAX date value, 2011-03-01, has a data label now.

Have fun.


Comment from Dan English
Time May 9, 2012 at 9:53 am

Nice and included a report variable:) The variable is actually not needed, you could simply place that logic in the expression directly. If you are going to reuse that logic thought it is nice to centralize it though.

Comment from Mark V
Time May 9, 2012 at 10:43 am

I agree that the Variable isn’t specifically required. I could have noted that, I suppose. I just like how clean it is and that it is re-usable easily. I am a big fan of Report Variables. :) Thanks muchly.

Comment from Hank Buonforte
Time August 13, 2012 at 2:08 pm

Any advice on how to have data labels that show totals for each bar of a stacked bar chart?

Comment from Mark V
Time August 14, 2012 at 12:08 pm

Hi Hank. I remember wrestling with that on a project a few years ago. The issue is the a data label will map to a data point in your data set. And, by having a data point in your data set that represents the Total, it will want to display along with the other data points and end up just being another stack in the one bar. If memory serves, I tried something like this: http://road-blogs.blogspot.com/2010/06/display-total-on-top-of-stacked-chart_8589.html

I hope that helps.

Comment from John H
Time February 21, 2013 at 10:21 pm

I have a customer who wants the y-axis limits fixed, and occasionally one of the columns is too big to fit on the chart. In this case he’d like the data label displayed (only in this case). but unfortunately it doesn’t appear as the top of the column is off the chart.
Any idea how to get a label on the column in this case?

Comment from Mark V
Time February 22, 2013 at 10:19 am

Hi, John. The Data Labels properties for a column chart have a Position property. Setting it to Top will place the label above the bar. Setting it to Bottom will place the label at the top of the bar still, but inside the bar itself. You can control this with en expression that will determine dynamically for each bar. the default for this property is Auto, which lets SSRS make the choice for you. But i have found occasions where it chooses poorly. I wrote a vb function for this that, while probably not the best vb ever, handled this the way I wanted it each time. I will dig that up and blog it and hopefully that will help, too. In the meantime, try messing with the Position property of the Data Labels. Hope this helps.

Comment from Mike B
Time August 13, 2013 at 2:20 pm

I have a stacked column report with data ranging from 0 to 12mil. When a value along the x-axis is low, say 350k, SSRS is placing the label where you can only see the top half of the value, the bottom half is being cut off by the x-axis line. In general, SSRS places stacked column labels INSIDE the column. So when you have a small amount there really is no where INSIDE the column to place the label. Any ideas on how to position a column label so that it appears completely above x-axis? Smartlabels dont seem to work.

Comment from Mark V
Time August 15, 2013 at 9:23 pm

Hi, Mike. The link here http://technet.microsoft.com/en-us/library/dd220469.aspx indicates that for Stacked Bars, the data label can only go inside the series. I have, however, dynamically hidden the label when the value is small enough that the label would not fit. It take a little trial and error, but you can do this pretty easily with the Visible property on the Chart Series Labels. I hope this helps.

Comment from Rob C
Time January 2, 2015 at 8:25 am

Hi Guys. This has been really useful. I managed to get it to show the maximum and minimum labels only (to show the range of data and the points on which the min and max fell) on a line chart. How could I do this for a multi-series line chart to show for each series…?

Comment from Mark V
Time January 2, 2015 at 3:25 pm

Hi, Rob. If the Series values are fixed, you could create a variable for each. Then, in the expression, reference the matching variable or each using a SWITCH(). That is just off the top of my head. It has been a while since I have played in SSRS land.

Comment from Rob C
Time January 5, 2015 at 4:06 am

Mark – Thanks for the response. I sorted the visibility out using this expression

=iif((fields!Expr1.Value = Min(fields!Expr1.Value,”Chart1_SeriesGroup”)) or (fields!Expr1.Value = Max(fields!Expr1.Value,”Chart1_SeriesGroup”)),True,False)

in the label/visible section of the chart series.

Comment from Scott
Time May 25, 2016 at 7:36 pm

Thanks for the info, sorted my problem out :) Cheers

Comment from Mark V
Time June 1, 2016 at 12:11 pm

Sweet! Thanks for sharing, Scott.

Comment from Harini
Time January 18, 2017 at 9:24 am

Thanks. It worked for me.

Write a comment