# Controlling Chart axis in SSRS

Had a tricky one today where the business wanted more axes to show on an SSRS report. Seems fair enough, cause as this example shows, a single line is pretty bland.

After a bit of reading, I found a few clues over here.

The concept is to convert a number to scientific notation, then use the first digit as a multiplier against 10 to the power of the exponent. This generates a nice round number, which is also divisible by 5.

Here’s the code, I just pass the range of values on the chart (the calc is also used for the max of the chart), and the function returns an appropriate interval

Public Function AxisInterval (ByVal rangex As Double) Dim rangexString as String = Format(rangex, "#e+0") Dim manix as Double = CInt (rangexString.Substring(0, rangexString.Indexof("e"))) Dim pwr10 as Double = CInt(rangexString.Substring(rangexString.Indexof("e") + 2,rangexString.length - rangexString.Indexof("e") - 2)) Dim interval as Double If rangex < 1 and rangex > -1 interval = (manix / (10 ^ pwr10)) / 5 else interval = (manix * (10 ^ pwr10)) / 5 End If Return Math.abs(interval) End Function

Here’s how my chart looks wtih the interval calcs:

But… well that doesn’t look great either, because 600, 1200, 1800… and the maximum isn’t shown.

So then the code ‘evolved’ again…

Public Function InlineMax (ByVal first As Double, ByVal second As Double, ByVal third as Double) If first > second and first > third Return first Else If second > first and second > third return second Else return third End If End Function Public Function InlineMin (ByVal first As Double, ByVal second As Double, ByVal third as Double) If first < second and first < third Return first Else If second < first and second < third return second Else return third End If End Function Public Function Axis (Operation As String, ByVal min As Double, ByVal max As Double) ' check inputs, if they're invalid retrun Auto axis If IsNothing(min) OR IsNothing(max) OR IsNothing(Operation) OR min >= max Then Return "Auto" Dim range as Double = (max - min) Dim rangeString as String = Format(range, "#e+0") ' manix is the first digit once the number is rounded to scientific notation Dim manix as Double = CInt (rangeString.Substring(0, rangeString.Indexof("e"))) Dim pwr10 as Double = CInt(rangeString.Substring(rangeString.Indexof("e") + 2,rangeString.length - rangeString.Indexof("e") - 2)) Dim catnum as Double ' select the number of categories based on the manix; helps with rounding Select Case manix case 1, 5 catnum = 5 case 2, 4, 8 catnum = 4 case 3, 6, 9 catnum = 6 case 7 catnum = 7 End Select Dim interval as Double If range < 1 and range > -1 interval = (manix / (10 ^ pwr10)) / catnum else interval = (manix * (10 ^ pwr10)) / catnum End If If range <= 0 Return 0 Else if Operation = "Interval" Return Math.Abs(interval) Else If Operation = "Max" Return (Math.Abs(max) / max) * Math.Abs(interval) * (Math.Ceiling(Math.Abs(max) / Math.Abs(interval))) Else If Operation = "Min" If min = 0 Then Return 0 else Return (Math.Abs(min) / min) * Math.Abs(interval) * (Math.Floor(Math.Abs(min) / Math.Abs(interval))) End If End Function

## 0 Comments

You can be the first one to leave a comment.