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
UNION ALL
SELECT     '2011-02-01' AS ValDate, 2 AS Val
UNION ALL
SELECT     '2011-03-01' AS ValDate, 1.5 AS Val

 

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

image

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

image

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.

image

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

image

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.

image

Click on the property value and choose Expression.

image

Enter the following expression.

image

Click OK and then run the report.

image

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

Have fun.

Comments

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?
Thanks.

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.

Write a comment