For a SSRS report I need to create a boxplot from a subgroup of my data. In order to do this I'd like to calculate the percentile values from within the report. (1st quartile, median, 3th quartile).
The data has both a series group and category groups. The user shall be able to select a single category group to subdivide the data. For each category, the percentile values should be generated.d
To achieve this I've included custom code to calculate the percentile values. I've added as category group variables of my boxplot the following:
Values: Code.AddValue(IIF(Parameters!Output.Value=0,
CDbl(Fields!Value1.Value),
IIF(Parameters!Output.Value=1,
CDbl(Fields!Value2.Value),
IIF(Parameters!Output.Value=2,
CDbl(Fields!Value3.Value),
Double.NaN
)
)
)
)
Median: Code.Percentile(50)
Q1: Code.Percentile(25)
Q3: Code.Percentile(75)
The values variable is an arraylist which should be populated by all the variables in the subgroup.
My Custom code:
Public Dim values As System.Collections.ArrayList
Public Function AddValue(ByVal newValue As Double)
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
End If
If Not Double.IsNaN(newValue) AndAlso newValue > 0 Then
values.Add(newValue)
End If
End Function
Public Function Percentile(ByVal percentileVal As Double) As Double
Return PercentilePos(values, percentileVal)
End Function
Private Function PercentilePos(values As System.Collections.ArrayList, ByVal percentile As Double) As Double
If IsNothing(values) OrElse values.Count = 0 Then
Return Nothing
End If
If percentile > 1 AndAlso percentile <= 100 Then
percentile = percentile / 100
ElseIf percentile <= 1 AndAlso percentile > 0 Then
percentile = percentile
Else
Throw New ArgumentException("percentile value is invalid")
End If
Dim numberCount As Integer = values.Count
Dim index As Integer = CInt(values.Count * percentile)
Dim sortedNumbers As New System.Collections.Generic.List(Of Double)
For Each val As Double In values
sortedNumbers.Add(CDbl(val))
Next
sortedNumbers.Sort()
If (sortedNumbers.Count - 1) < index Then
index = sortedNumbers.Count - 1
End If
Try
If (numberCount Mod 2 = 0) Then
If index > 0 Then
PercentilePos = (sortedNumbers(index) + sortedNumbers(index - 1)) / 2
Else
PercentilePos = sortedNumbers(index)
End If
Else
PercentilePos = sortedNumbers(index)
End If
Catch err As System.Exception
Throw New ArgumentException(err)
Return Nothing
End Try
End Function
Now I'm having the following problem: The group variable Values only collects the value from the first row of the subgroup and therefore the calculations are off. How do I get the group variable to collect / enumerate over all the rows in the subgroup?
Thanks in advance for your time and effort.
Aucun commentaire:
Enregistrer un commentaire