Friday, July 17, 2020

SQL Server Reporting Services - Summing a Field Calculated in a Custom Code Assembly

I have a report that has a complex calculation being done in a custom code assembly that is attached to the report as a reference. The problem I ran into is trying to get a SUM on that calculation. I've written some custom code that you can input into the Code section on Report Properties.

Public Shared Value as Decimal=0.00
Public Shared ReturnValue as Decimal = 0.00
Public Shared Function GetValue(Item as Integer) as Integer
     value= value + Item
     return Item
End Function
Public Shared Function GetTotal()
     returnvalue = value
     value = 0
     return returnvalue
End Function


Then you just enclose the original expression inside:

=Code.GetValue(put expression here)

And then use this expression where you want the total:

=Code.GetTotal()

This code will also zero-out the 'Value' variable so that you can reuse it in a repeating group.