# 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```