lundi 29 juin 2015

How can I generate correctly the percentiles for drawing a boxplot on a SSRS report from a dynamic subgroup of my data

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