0

Having a heck of a time figuring out how to get my report to look like the following. Any advice would be very helpful regarding the Median function. In the totals the Median and Average is calculated in the underlying structure not from the calculated values from above. I should note I have no problem getting the Averages it's just the Median part.

I'm grouping by Store then by Item.

Store 1
Item          QTY          Median          Average        
Apples         17            31              33            
Oranges       182            17              21 
Totals        199            25              24


Store 2
Etc.

I found a really good article from this blog but I cannot get it to work with this grouping. Blog Link

The Code I'd like to use looks like this.

The question is now how do I use it properly?

I have tried adding a variable to the Item Group. =Code.AddValue(Fields!myValue.Value) Then in the field of the cell I added =Code.GetMedian()

It returns a garbage result thou. 

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
Flexpadawan
  • 77
  • 1
  • 8
  • What are your garbage results? You seem to have added a bunch to the code and using New **System.Collections.Generic.List** instead of **System.Collections.ArrayList**. I'm not sure what the point of the **valueCounts** is (Generic List doesn't have .Count?) but it still should be accurate. – Hannover Fist Apr 01 '15 at 21:03
  • I got that from this thread [link]http://stackoverflow.com/questions/1943437/mean-median-mode-in-sql-server-reporting-services – Flexpadawan Apr 01 '15 at 21:08
  • I get a median value something like 6.5 for every field. The value doesn't mean anything but something isn't right because it used the same value for every row. – Flexpadawan Apr 01 '15 at 21:09
  • Ah - the function is meant to be used for a group total. It's meant to use the =Code.AddValue(Fields!myValue.Value) in the detail rows and the total row =Code.GetMedian(). Do you have a (hidden) detail row? – Hannover Fist Apr 01 '15 at 22:22

0 Answers0