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, "--")

Sunday, May 2, 2010

Pivoting Rows to Columns - Part 3

In this last installment of this series on pivoting rows into columns, I will show you how to use GROUP BY and CASE together to achieve the desired result. The specific example will take Profile codes from the NAProfiles table of the Shelby v.5 database and pivot them into columns.

Here is the basic query without pivoting, showing each name in the database along with Profile codes:

select
Names.NameCounter,
Names.FirstMiddle,
Names.LastName,
Profiles.Profile
from
Shelby.NANames as Names left join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter

This generates a list of names and Profile codes, but a person with three codes will be listed three times. What many people want is to list the person once, with a column for each Profile code of interest. The first step in that direction for the approach today is to add a GROUP BY clause and an aggregate function for the Profile code:

select
Names.NameCounter,
Names.FirstMiddle,
Names.LastName,
Profile = max(Profiles.Profile)
from
Shelby.NANames as Names left join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter
group by
Names.NameCounter,
Names.FirstMiddle,
Names.LastName

Now we are back to one line per person, but with only one Profile in the results. Because of the MAX() function, we have the Profile that comes last alphabetically for each person. To gain control over which Profile shows up, add a CASE statement that returns only the desired value inside of the MAX() function:

select
Names.NameCounter,
Names.FirstMiddle,
Names.LastName,
Profile = max(case Profiles.Profile when 'LASTCN' then Profiles.Profile end)
from
Shelby.NANames as Names left join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter
group by
Names.NameCounter,
Names.FirstMiddle,
Names.LastName

This returns only the LASTCN Profile code in the Profile column of the results. Now that you have a method of getting the info from NAProfiles on just row(s) you want, you can use a variety of NAProfile columns and column aliases to get the desired results. You can even substitute your own marking as the output of the CASE statement, such as printing an "X" to indicate a match.

select
Names.NameCounter,
Names.FirstMiddle,
Names.LastName,
LastCNDate = max(case Profiles.Profile when 'LASTCN' then convert(varchar, Profiles.Start, 101) end),
AllergyInfo = max(case Profiles.Profile when 'ALLERG' then Profiles.Comment end),
OnReachingTeam = max(case Profiles.Profile when 'ZRREACH' then 'X' end)
from
Shelby.NANames as Names left join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter
group by
Names.NameCounter,
Names.FirstMiddle,
Names.LastName

This approach works best when you have only one join that creates a one-to-many relationship, such as NANames to NAProfiles. If you have several joins of that type, then one of the first two approaches (discussed in the prior two blog posts) would probably work better. But when you do have just one such join, this is perhaps the easiest way to move rows to columns.

Followers