Calculate Median Values in SSRS

• fliu2265

  SSC Eights!

  Points: 818

  More actions

  October 10, 2017 at 9:23 am

  #340741

  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: 90860

  More actions

  October 12, 2017 at 11:16 am

  #1963444

  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