October 10, 2017 at 9:23 am
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
AddToMedian = 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
October 12, 2017 at 11:16 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply