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...