This post is related SQL Server Reporting Services 2008 R2.
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
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
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!
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!