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

End result:

0 Comments

You can be the first one to leave a comment.

Leave a Comment