views:

242

answers:

0

I am trying to compute Mode along with a bunch of other aggregates (SUm,Min,StdDev) on a report.

Currently there is a Row Group and a Column group. The row Group is by region and the Column Group is based on the results from a column.

Following an tutorial online, I got this custom code working for it.

Dim values As System.Collections.ArrayList

Function AddValue(ByVal newValue As Integer)
    If (values Is Nothing) Then
        values = New System.Collections.ArrayList()
    End If
    values.Add(NewValue)
End Function

Function Mode() As Integer
    Dim Value As Integer
    Dim Count As Integer = 0
    Dim CurrentCount As Integer = 0

    If (values Is Nothing) Then
        Value = 0
    Else
        values.Sort()
        For i As Integer = 0 To values.Count - 1
            If values(i) = values(values.Count - 1) Then
                CurrentCount = CurrentCount + 1
                If CurrentCount > Count Then
                    Value = values(i)
                    Count = CurrentCount
                End If
            ElseIf values(i) = values(i + 1) Then
                CurrentCount = CurrentCount + 1
                If CurrentCount > Count Then
                    Value = values(i)
                    Count = CurrentCount
                End If
            Else
                CurrentCount = CurrentCount + 1
            End If
        Next
    End If

    Return Value

End Function

So the problem seems to be that AddValue is never called. In the report I have it as: =Code.AddValue(Fields("NormPriceOverInvoice").Value) This is hidden so that =Code.Mode() gets what it needs.

The report is layed out as:

Region  |     | <Dynamic Column Grouping>
--------------------------------------------------------------------
        |Min  | Value
<Hidden>|Mode | =Code.AddValue(Fields("NormPriceOverInvoice").Value)
<show>  |Mode | =Code.Mode()

The report is set up that a person will change how many columns there are in a report based on the passed variables...

I guess the question boils down to, is this possible in Sql Server 2008 Reporting Services? Should I be focusing on fixing this/making this work, or creating a query to run on having a separate table which makes for some redundant data...