A common tool used in building SQL Server reports is expressions, to either control content or appearance. Functions are almost always used at some point within an expression. A particular function we are going to touch on in this article is called IsNothing. This function will let you inspect the value of an object to find out if it is NULL or not. There are various scenarios where this function can be used, but I am just going to touch on two that I think you might find used the most.

I am going to be using SSDT-BI for Visual Studio 2012. To let you know, to access the Expression Editor on any property within a report you will generally see an icon similar to this:

In order to try and keep things as simple as possible on the setup, I am just going to use SQL Server backup history as the data for this example. I will assume you are already familiar with setting up your data source and dataset within SSDT-BI. The query below is what I will be working with:

SELECT d.name AS DatabaseName
,MAX(b.backup_finish_date) AS last_full_backup
FROM sys.databases AS d 
LEFT OUTER JOIN msdb.dbo.backupset AS b ON b.database_name = d.NAME
WHERE d.name <> 'tempdb' AND b.type = 'D'
GROUP BY d.name
UNION
SELECT d.name AS DatabaseName
,NULL AS last_full_backup
FROM master.dbo.sysdatabases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE b.database_name IS NULL AND d.name <> 'tempdb' 
ORDER BY d.name

Scenario 1: Color Formatting

If you were to just put this dataset in a table on the report it would look something similar to the below image:

=IIF(IsNothing(Fields!last_full_backup.Value),"IndianRed","Transparent")

Scenario 2: Value Manipulation

Now, let us say instead of formating the cell with a fill color I just want to change the NULL to be an actual value. So instead of a NULL value I am going to populate the cell with the text "***NONE***". To get this result you are going to use an expression for the value of text box itself.

In the same example above go back into the text box properties, and then within the general tab you will click on the expression button for the value "last_full_backup":

You will use an expression similar to the color expression to achieve the goal of populating a value instead of leaving it NULL:

=IIF(IsNothing(Fields!last_full_backup.Value),"**NONE**",Fields!last_full_backup.Value)

Apply this to the report and then the preview will return something similar to this (highlighted the cells with the value):

Happy designing.