Custom Aggrigate Functions

I was trying to calculate the Meadian of the group of values (grouped based on some columns). I could not find any standard aggrigate funtion for doing so.

It would be great if I can be able to create my own custom aggrigate functions and use the same in the RunningValues funtion.

It would be even interesting if the user can share code written across reports and report projects, with out the need to copy and paste the same custom function in all the reports.

Rich and very interesting feature would be enabling the developer to use the traditional VS2005 UI to develop his code instead of the mundane text box.

Take a look at the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Another example (a moving average aggregate implementation for a chart - can be applied similarly for a table or matrix) is shown in one of the samples of the following whitepaper - search for the section about "Moving Average Calculations": http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

-- Robert

|||

Robert, Thanks for the reply. I have done some thing similar to the one in the first link.

I am facing a problem with this kind of logic. I get the #Error in the first group footer, in second group footer I get the value, but it is the median value of the first group. It continues like this and last footer has the median value of the previous group footer (missing its own value).

To confrim how the values string is built I did reset the visibility of the hidden column and saw the values building correctly. It accumalates each value row by row and last row in the group (before the footer) has all the values to calculate median. And this holds good for the First Group also.

Am I doing some thing wrong?

The code is below:

Public AucBaseAmtString As String

'Append all the amounts to a string seperated by comma. ex: ,12,322,23,232

'Call this funtion in the hidden column of the table for each row

Public Function AccumulateAucBase(Amt As String) As String
AucBaseAmtString = AucBaseAmtString & "," & Amt
Return AucBaseAmtString
End Function

'Call the Median function in the Group Footer.

Public Function Median() As String
Dim Count As Integer
Dim AucBaseAmts() As String

'Truncate the first comma
If AucBaseAmtString <> "" Then
AucBaseAmtString = Mid(AucBaseAmtString, 2)
End If

'Get the amounts to array
AucBaseAmts = AucBaseAmtString.Split(",")

'Reset the string for the next group
AucBaseAmtString = ""

'Calculate median and return
Count = AucBaseAmts.GetLength(0)
If Count = 0 Then Return "0"
Array.Sort(AucBaseAmts)
If Count Mod 2 = 0 Then
Return CStr((CInt(AucBaseAmts((Count / 2) - 1)) + CInt(AucBaseAmts(Count / 2))) / 2)
Else
Return CStr(AucBaseAmts(Count / 2))
End If
End Function