You are here
Home > SQL Server

Using Custom Functions in SSRS

I never bothered to try adding custom functions in SSRS before last week… and I can’t believe how easy Microsoft made this.

As an example, one of the most common calculations I write in reports tends to be division and with division you always need to take into account the possibility of NULL and 0 values. If you come across this before you’ve dealt with NaN in being returned in the reports and the "interesting" scientific notation number that not a number represents… not sure how exporting allows not a number to be a number but I guess I’m not that smart.

Instead of dropping that calculation into all the textboxes that are doing division  I go under Report >> Report Properties >> Code and drop in the following code:

Public Shared Function Divide(Num1 as double, Num2 as double) AS object
    IF ISNOTHING(Num2) Or Num2 = 0 Then
        Divide = "n/a"
    ELSEIF Num1 = 0 THEN
        Divide = 0
        Divide = Num1  / Num2
    END IF
End Function

And then in each of the textboxes on the report where I need to do division, I call the function like this:

=Code.Divide(1, 0)

Now this is a simple use for .NET code a report, but I am sure in the following months I’ll come up with a great many other uses.  In the meantime I am going to enjoy the time and headaches I’ve saved by using this…

And complain because the code window that this gets dropped into is just a textbox without any development environment intellisense and what-not.  Maybe in SQL Server’s next release they’ll add a code behind to reports to ease development of report functions.