Calculate Median Values in SSRS

  • fliu2265

    SSC Eights!

    Points: 815

    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

  • Sue_H

    SSC Guru

    Points: 90287

    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)

You must be logged in to reply to this topic. Login to reply