A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Friday, May 21, 2010

Checking for NULL in SQL Server Reporting Services

Today I was tasked with replacing blank values on a report output with a double-dash. This was on a SQL Server Reporting Services (SSRS) report design I had made.

I knew this would involve an IIF() conditional. But I was not sure how to phrase the condition to check for the NULL value. I tried two approaches off the top of my head.

First, I tried the SQL syntax of Fields!FieldName.Value IS NULL. It was a long shot, and it didn't work. No real surprise, there, so I moved on.

Second, I tried the VBScript approach of IsNull(Fields!FieldName.Value). I was pretty sure this would work, because I have used some VBScript commands successfully with conditional formatting in SSRS before. However, this didn't work either.

What became apparent was that I needed to understand how to do this kind of condition in .NET, which is the actual programming language supported inside of SSRS for formula expressions. I did some searches on the Internet and eventually found the right syntax. It turns out that IsNothing() is the .NET equivalent to the VBScript function IsNull(). The working syntax for my report requirement is:

= IIf(Not IsNothing(Fields!FieldName.Value), Fields!FieldName.Value, "--")

No comments:

Post a Comment