Dynamic Label Positions With SSRS
On a recent tabular "dashboard" with several bar charts in a small area our team found vertical positioning of the labels to be troublesome. When the labels were set to "bottom" and the bar was too short the label would truncate the significant digits. On the other hand, when the labels were set to "top" the numbers would be truncated if the column was too tall.
First we thought about using Smart Labels but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical. As you can see in the examples above the size of the numbers would make horizontal labels messy.
So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough.
In Report Builder, go to your chart and select a specific column - not just the chart. If you do not already have the Properties pane open go to the ribbon select the View ribbon and check Properties.
If you have selected the column in the chart, the properties pane should look like this:
The human readable explanation is if the sum of the current value is less than half of the total past value put the label on top otherwise put the label on the bottom.
However, this expression produced the following error:
The Position expression for the chart ‘Chart4’ contains an error: [BC30451] 'Top' is not declared. It may be inaccessible due to its protection level.
I searched the internet endlessly to find the reason why this expression did not work. I was confident it should work. After much searching I returned to the error and actually tried to comprehend what it was telling me. Eventually, I realized that it was telling me Top was not a keyword and meant nothing in the context of the expression.
=IIf(Sum(Fields!Current.Value) <= (Sum(Fields!Total_Past.Value)*.5), "Top", "Bottom")
After putting Top and Bottom in quotes, the expression began working like we always knew it should. Another method would be to create report level variables for Top and Bottom and then reference those variables.
 |
| Example of bar chart with label set to bottom. |
 |
| Same chart with the middle column label set to top. |
First we thought about using Smart Labels but we quickly found that smart labels are not so smart when the labels are turned 90 degrees to make them vertical. As you can see in the examples above the size of the numbers would make horizontal labels messy.
So the obvious choice was to use a function to place the label on top when the bar was small and on the bottom once the bar was tall enough.
In Report Builder, go to your chart and select a specific column - not just the chart. If you do not already have the Properties pane open go to the ribbon select the View ribbon and check Properties.
If you have selected the column in the chart, the properties pane should look like this:
Next expand the Label properties - highlight in blue above. If you do not see the label properties make sure you have selected a column in the chart. The properties pane is context specific.
Notice in the image above the Label Position is set to Bottom. This is the property we want to change dynamically. You will need to select the drop down next to the Position property and select expression.
Our first attempt looks like this:
=IIf(Sum(Fields!Current.Value) <= (Sum(Fields!Total_Past.Value)*.5), Top, Bottom)
The human readable explanation is if the sum of the current value is less than half of the total past value put the label on top otherwise put the label on the bottom.
However, this expression produced the following error:
The Position expression for the chart ‘Chart4’ contains an error: [BC30451] 'Top' is not declared. It may be inaccessible due to its protection level.
I searched the internet endlessly to find the reason why this expression did not work. I was confident it should work. After much searching I returned to the error and actually tried to comprehend what it was telling me. Eventually, I realized that it was telling me Top was not a keyword and meant nothing in the context of the expression.
=IIf(Sum(Fields!Current.Value) <= (Sum(Fields!Total_Past.Value)*.5), "Top", "Bottom")
After putting Top and Bottom in quotes, the expression began working like we always knew it should. Another method would be to create report level variables for Top and Bottom and then reference those variables.
