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
And then in each of the textboxes on the report where I need to do division, I call the function like this:
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.