Q: Median function in report builder 3.0 I need to perform median calculation in MS Report Builder 3.0, could anyone explain how I could achieve it considering my original value are
Region - Etab- Value
Abc - Def - 10
Abc - Def - 12
Ged - Tae - 1
I need to group by Region and Etab.
I've managed to get the proper values using hashtable the following way :
Dim theHashTable As New System.Collections.Hashtable

Function AddValue(theRapport As String, theRegion As String, theEtab As String, theRow As String, theValue As String) As Integer
    Dim num As Integer
    num = 0
    If (theHashTable Is Nothing) Then
        theHashTable = New System.Collections.Hashtable
    End If
    If Integer.TryParse(theValue, num) Then
        If (num >= 0) Then
            If (theHashTable.ContainsKey(theRapport)) Then
                Dim regionHT As New System.Collections.Hashtable
                regionHT = theHashTable(theRapport)
                If (regionHT.ContainsKey(theRegion)) Then
                    Dim etabHT As New System.Collections.Hashtable
                    etabHT = regionHT(theRegion)
                    If (etabHT.ContainsKey(theEtab)) Then
                        Dim valueHT As New System.Collections.Hashtable
                        valueHT = etabHT(theEtab)
                        If (Not valueHT.ContainsKey(theRow)) Then
                            valueHT.Add(theRow, theValue)
                        End If
                        etabHT(theEtab) = valueHT
                    Else
                        Dim valueHT As New System.Collections.Hashtable
                        valueHT.Add(theRow, theValue)
                        etabHT.Add(theEtab, valueHT)
                    End If
                    regionHT(theRegion) = etabHT
                Else
                    Dim etabHT As New System.Collections.Hashtable
                    Dim valueHT As New System.Collections.Hashtable
                    valueHT.Add(theRow, theValue)
                    etabHT.Add(theEtab, valueHT)
                    regionHT.Add(theRegion, etabHT)
                End If
                theHashTable(theRapport) = regionHT
            Else
                Dim regionHT As New System.Collections.Hashtable
                Dim etabHT As New System.Collections.Hashtable
                Dim valueHT As New System.Collections.Hashtable
                valueHT.Add(theRow, theValue)
                etabHT.Add(theEtab, valueHT)
                regionHT.Add(theRegion, etabHT)
                theHashTable.Add(theRapport, regionHT)
            End If
        End If
    End If

    Return num
End Function

Function GetMedian(theRapport As String, theRegion As String, theEtab As String) As String

    Dim arrayInt As New System.Collections.ArrayList
    arrayInt = GetArray(theRapport, theRegion, theEtab)
    arrayInt.Sort()
    Dim mid As Double = (arrayInt.Count - 1) / 2.0
    Dim midInt As Integer = mid
    Dim mid2Int As Integer = mid + 0.5
    If arrayInt.Count >= 2 Then
        Return ((arrayInt(midInt) + arrayInt(mid2Int)) / 2).ToString()
    ElseIf arrayInt.Count = 1 Then
        Return arrayInt(0)
    Else
        Return ""
    End If
End Function

Function GetQ1(theRapport As String, theRegion As String, theEtab As String) As String

    Dim arrayInt As New System.Collections.ArrayList
    arrayInt = GetArray(theRapport, theRegion, theEtab)
    arrayInt.Sort()
    Dim taille As Integer = arrayInt.Count
    If (taille = 1) Then
        Return arrayInt(0)
    ElseIf ((taille Mod 2) = 0 And taille > 0) Then
        Dim mid1 As Integer = taille / 2
        Dim midmid As Integer = mid1 / 2
        If (mid1 Mod 2 = 0) Then
            Return ((arrayInt(midmid - 1) + arrayInt(midmid)) / 2).ToString()
        Else
            Return (arrayInt(midmid)).ToString()
        End If
    ElseIf (taille = 1) Then
        Return arrayInt(1)
    ElseIf ((taille - 1) Mod 4 = 0) Then
        Dim n As Integer = (taille - 1) / 4
        Return ((arrayInt(n - 1) * 0.25 + arrayInt(n) * 0.75)).ToString()
    ElseIf ((taille - 3) Mod 4 = 0) Then
        Dim n As Integer = (taille - 3) / 4
        Return ((arrayInt(n) * 0.75 + arrayInt(n + 1) * 0.25)).ToString()
    Else
        Return ""
    End If

End Function

Function GetQ3(theRapport As String, theRegion As String, theEtab As String) As String

    Dim arrayInt As New System.Collections.ArrayList
    arrayInt = GetArray(theRapport, theRegion, theEtab)
    arrayInt.Sort()
    Dim taille As Integer = arrayInt.Count
    If (taille = 1) Then
        Return arrayInt(0)
    ElseIf ((taille Mod 2) = 0 And taille > 0) Then
        Dim mid1 As Integer = taille / 2
        Dim midmid As Integer = mid1 / 2
        If (mid1 Mod 2 = 0) Then
            Return ((arrayInt(mid1 + midmid - 1) + arrayInt(mid1 + midmid)) / 2).ToString()
        Else
            Return (arrayInt(mid1 + midmid)).ToString()
        End If
    ElseIf (taille = 1) Then
        Return arrayInt(1)
    ElseIf ((taille - 1) Mod 4 = 0) Then
        Dim n As Integer = (taille - 1) / 4
        Return ((arrayInt(3 * n) * 0.75 + arrayInt(3 * n + 1) * 0.25)).ToString()
    ElseIf ((taille - 3) Mod 4 = 0) Then
        Dim n As Integer = (taille - 3) / 4
        Return ((arrayInt(3 * n + 1) * 0.25 + arrayInt(3 * n + 2) * 0.75)).ToString()
    Else
        Return ""
    End If

End Function

Function GetArray(theRapport As String, theRegion As String, theEtab As String) As System.Collections.ArrayList
    Dim arrayInt As New System.Collections.ArrayList
    If (theHashTable Is Nothing Or theHashTable.Count = 0) Then
        Return arrayInt
    Else
        If (theHashTable.ContainsKey(theRapport)) Then
            Dim regionHT As System.Collections.Hashtable
            regionHT = theHashTable(theRapport)
            If (theRegion = "" And theEtab = "") Then
                For Each value As System.Collections.Hashtable In regionHT.Values
                    For Each value2 As System.Collections.Hashtable In value.Values
                        For Each valeur As Integer In value2.Values
                            arrayInt.Add(valeur)
                        Next
                    Next
                Next
            ElseIf (regionHT.ContainsKey(theRegion) And theEtab = "") Then
                Dim etabHT As System.Collections.Hashtable
                etabHT = regionHT(theRegion)
                For Each value As System.Collections.Hashtable In etabHT.Values
                    For Each valeur As Integer In value.Values
                        arrayInt.Add(valeur)
                    Next
                Next
            ElseIf (regionHT.ContainsKey(theRegion) And theEtab <> "") Then
                Dim etabHT As System.Collections.Hashtable
                etabHT = regionHT(theRegion)
                If Not (etabHT Is Nothing Or etabHT.Count = 0) Then
                    If (etabHT.ContainsKey(theEtab)) Then
                        Dim valuesHT As System.Collections.Hashtable
                        valuesHT = etabHT(theEtab)
                        For Each value As Integer In valuesHT.Values
                            arrayInt.Add(value)
                        Next
                    End If
                End If
            End If
        End If

        Return arrayInt
    End If
End Function

Function PrintArray(theRapport As String, theRegion As String, theEtab As String) As String
    Dim arrayInt As New System.Collections.ArrayList
    arrayInt = GetArray(theRapport, theRegion, theEtab)
    Dim str As String = ""
    If (arrayInt.Count > 0) Then
        str = String.Join(" | ", arrayInt.ToArray)
    Else
        str = " "
    End If
    Return str
End Function

The first hashtable is for different tables of the report needing the median.
I then use the following command to add value
Code.AddValue("3_2",Fields!Region.Value,Fields!Etablissement.Value,Fields!rowNumber.Value,Fields!Value.Value)
Then I get the median using the expressions
=Code.GetMedian("3_2", Fields!Region.Value,Fields!Etablissement.Value)
=Code.GetMedian("3_2", Fields!Region.Value,"")
=Code.GetMedian("3_2", "","")
I've tried placing the AddValue fonction on a hidden table and in the summary row of the tables.
I get the proper value but as soon as I expand or collapse a row everything is change to blank. How can I keep the value or where could I put the AddValue function to make sure it is called on every action, for every table in the report ?
Thanks
