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 other areas of SSDT-BI it might simply be a link titled "Expression...".
The Report Data
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:
This looks rather dull to see an empty cell in the table and requires that I still skim down the table to find those databases missing a backup. Adding a bit of color to those cells that are NULL can make this report much easier to read through.The cell for the value of "last_full_backup" can be modified to use an expression for the fill color to achieve this goal. You would access this via the text box properties, under the fill tab.
In this instance we basically want to know if a date is returned or a NULL value is returned. This is where the IsNothing is utilized within the expression. You can use an expression similar to the following:
=IIF(IsNothing(Fields!last_full_backup.Value),"IndianRed","Transparent")
Once I have done that and preview the report, it changes the report to something that is a bit more appealing:
Now alternative to simply just coloring in the cell would be to color the complete row, but I will leave that to you to try on your own.
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):
