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?
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?
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
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()
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