# Calculate Median Values in SSRS

• Hello,

I have the following codes to calculate the median value by groups in SSRS.
********************************************
Dim Public Shared MedianArray(0) As Integer

Public Function ResetMedian()
ReDim MedianArray(0)
End Function

Public Function AddToMedian(fieldValue As Decimal) As Decimal
Dim i As Integer
i = UBound(MedianArray) + 1
ReDim Preserve MedianArray(i)
MedianArray(i) = fieldValue
End Function

Public Function GetMedian() as Decimal
Dim arraySize as Integer
Dim ii as Integer
Dim jj As Integer
Dim itemMoved As Boolean
Dim temp As Integer

'sort it & calculate it
arraySize = UBound(MedianArray)
If arraySize = 1 Then
GetMedian = ( MedianArray(0) )
Exit Function
Else If arraySize > 1 Then
For ii = 0 To arraySize - 1
itemMoved = false
For jj = LBound(MedianArray) To UBound(MedianArray) - 1
If MedianArray(jj) > MedianArray(jj + 1)
temp = MedianArray(jj)
MedianArray(jj) = MedianArray(jj + 1)
MedianArray(jj + 1) = temp
itemMoved = True
End If
Next
If itemMoved = False Then Exit For
Next

'calculate it
If arraySize Mod 2 = 0 Then
'average the two middle values
GetMedian = ( (MedianArray(arraySize / 2) + MedianArray((arraySize / 2) + 1)) / 2)

Else
'get the middle value
GetMedian = MedianArray(Floor((arraySize / 2)) + 1)
End If
End If
End Function
*********************************************************
The code is working except a minor problem.
For example, I have following salary in the order.
\$3112.00
\$3142.50
\$3432.00
\$3475.00

So the median salary should be (3142.50+3432.00) / 2 = 3422.25. However, the result only shows \$3422.00 (no decimal parts), not \$3422.25.
But if I test using GetMedian = (100.50+ 100.70)/2 = 100.60.  The result shows correctly.

Any help is highly appreciated.

Thanks

Frank

• Try the format property of the cell or text box where you are displaying this value. Refer to this documentation for more information:
Formatting Numbers and Dates (Report Builder and SSRS)

Sue

Viewing 2 posts - 1 through 2 (of 2 total)