среднее, медиана, режим в службах SQL Server Reporting Services

Расширяя ответ @Homer, приведенный ниже код можно использовать для получения как медианы, так и режима. Мне нужны были целые числа, но было бы быстро принять Decimal или 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

Вот Median() из Советы и рекомендации по созданию отчетов...

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

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

=Code.GetMedian()

Вот как я получаю 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