SSRS Chart Dynamic Axis

This post is related SQL Server Reporting Services 2008 R2.

Problem:

I recently faced a situation when I was creating a column chart, showing Monthly Visit Counts. As the VisitCount Values were quite small (maximum being 2) and since the axis has default setting for Max value and Increment as Auto, it started showing me decimal values or repeating the values (if you switch off the decimal) for the ranges on the Axis. Now, since I am displaying Counts that are supposed to be "integers", the decimals could be confusing to the end-user.




We have following dataset:
MonthName | VisitCount

We need to create a Column Chart with following:
MonthName will go on X-Axis
VisitCount will go on Y-Axis

Trick:

Compute the Max value property of the Y Axis dynamically depending on the Max value in the dataset.

Solution:

Step #1: Add Custom Code

Here we are assuming that we can have the Maximum value for the Axis to be the 10x factor of either 10, 25 or 50. That is 10, 25, 50, 100, 250, 500, 1000, 2500 and so on. If you want other ranges you can modify the varibale values and add more variables.

This code computes the Max value property to be set for the Axis Options, depending on the Maximum value of the Measure in the data set.


Public Function GetScaleMaxVal(ByVal DataMaxVal As Integer) As Integer

   Dim ScaleMaxVal As Integer ScaleMaxVal = 10
Dim ScaleMaxVal10 As Integer
Dim ScaleMaxVal25 As Integer
Dim ScaleMaxVal50 As Integer

　
ScaleMaxVal10 = 10
ScaleMaxVal25 = 25
ScaleMaxVal50 = 50
　

While DataMaxVal > ScaleMaxVal

   If DataMaxVal < ScaleMaxVal10 Then
      ScaleMaxVal = ScaleMaxVal10

   ElseIf DataMaxVal < ScaleMaxVal25 Then
      ScaleMaxVal = ScaleMaxVal25

   Else
      ScaleMaxVal = ScaleMaxVal50

   End If

   ScaleMaxVal10 = ScaleMaxVal10 * 10
   ScaleMaxVal25 = ScaleMaxVal25 * 10
   ScaleMaxVal50 = ScaleMaxVal50 * 10End While

Return ScaleMaxVal

End Function

Step #2: Call the Function

As we are done writing the function, time to give it a call.
Go to your Chart
-> Vertical Axis properties
-> Axis Options
-> Maximum
Click on Expression button (Fx ) and add following code:

=Code.GetScaleMaxVal( Max(Fields!VisitCount.Value))
Here we are using in built mathematical function Max along with our custom function.

Thats it and you are done!