mean, median, mode in SQL Server Reporting Services

Question

Is it possible to calculate an mean, median, mode, standard deviation, etc. of a column of data?

In general, is it possible to do these sorts of math calculations in SQL Server Reporting Services?

If so, how can it be done?

Answer 1

Expanding on @Homer's answer, the code below can be used to get both the Median and the Mode. I needed Integers but it would be a quick change to accept Decimal or Double.

Dim values As New System.Collections.Generic.List(Of Integer)
Dim valueCounts As New System.Collections.Generic.Dictionary(Of Integer, Integer)

Function AddValue(newValue As Integer) As Integer
    values.Add(newValue)
    AddValue = newValue
    If Not valueCounts.ContainsKey(newValue) Then
        valueCounts.item(newValue) = 1
    Else
        valueCounts.item(newValue) += 1
    End If
End Function

Function GetMedian() As Double
    Dim count As Integer = values.Count
    If count = 0 Then
        Return 0
    Else
        values.Sort()
        If count Mod 2 = 1 Then
            Return values(CInt((count / 2) - 0.5))
        Else
            Dim index1 As Integer = count \ 2
            Dim index2 As Integer = index1 - 1

            Dim value1, value2 As Integer
            value1 = values(index1)
            value2 = values(index2)

            Return (value1 + value2) / 2
        End If
    End If
End Function

Function GetMode() As String
    Dim max As Integer = 0
    For Each v As Integer In valueCounts.Values
        If v > max Then
            max = v
        End If
    Next v

    Dim maxCount As Integer = 0
    Dim retValue As String = ""
    For Each vcKvp As System.Collections.Generic.KeyValuePair(Of Integer, Integer) In valueCounts
        If vcKvp.Value = max Then
            maxCount += 1
            If Not String.IsNullOrEmpty(retValue) Then
                retValue &= ", "
            End If
            retValue &= vcKvp.Key
        End If
    Next vcKvp

    If maxCount = valueCounts.Count Then
        Return "N/A"
    End If

    Return retValue
End Function

Answer 2

Here is Median() From Report Design Tips and Tricks...

Scenario 1

1: In Report Designer, open the Report Properties dialog box and click the Code tab. Define an array, a function that takes a value and adds it to the array, and a function that calculates the median value from the array;

Dim values As New SystemCollections.ArrayList

Function AddValue(newValue As Decimal) As Decimal
   values.Add(newValue)
   AddValue = newValue
End Function

Function GetMedian() As Decimal
   Dim count As Integer = values.Count
   If (count > 0)
      values.Sort()
      GetMedian = values(count\2)
   End If
End Function

2: Wrap the call to the function in an aggregate and add it to an expression in the detail rows.

=Max(Code.AddValue(Fields!field.Name))

3: From a text box in the table footer, call into GetMedian() to retrieve the value

=Code.GetMedian()

Answer 3

Here is how I'm getting Mode for Ages:

Declare @Temp Table(Id Int Identity(1,1), Data Decimal(10,5))

Insert into @Temp Select DATEDIFF (YY, EmployeeCustomTabFields.CustDOB, GETDATE()) -
Case When (MONTH(EmployeeCustomTabFields.CustDOB)=MONTH(GETDATE()) AND DAY(EmployeeCustomTabFields.CustDOB) > DAY(GETDATE()) OR MONTH (EmployeeCustomTabFields.CustDOB) > MONTH (GETDATE()))
Then 1 Else 0 End as Age
From EM
inner join EmployeeCustomTabFields on EmployeeCustomTabFields.Employee = EM.Employee
Where EmployeeCustomTabFields.CustDepartment = '23 - Piping Design' and EM.Status = 'A' and EM.Type in ('A','B','C')

Select Top 1 with ties DATA
From   @Temp
Where  DATA IS Not NULL
Group By DATA
Order  By COUNT(*) DESC