CODEWITHSUNDEEP
reporting-services
Kim Jones
Kim Jones

Reputation: 133

SSRS 2008 need to return records with actual NULL value in a field

I have a report that has a parameter where I need to be able to have the actual value of NULL as an option to be selected and to actually have results returned but can't figure it out.

Some background (I'm oversimplifying here to make it more clear):

Table Major_Grp has 5 values, C,P,R,S and W with corresponding descriptions of Construction, Plants, Retail, Seed and Wholesale. However, a record in Order_Line very well could have NULL as the value in Major_Grp. I need to be able to select JUST the records with NULL by themselves, and NOT have them included if I choose either C,P,S,R or W.

The parameter for Major_Grp is set to Allow Nulls (no blanks, and no multiples) and uses a query in the MajorGrp dataset to load the available and default values. In my MajorGrp dataset, I have the following: select distinct major_grp, major_grp_desc from major_grp UNION select NULL,'Prelim'

When I open the dropdown, I see "Construction","Plants","Retail","Seed", "Wholesale and "Prelim". The report works when I choose one of the real values (i.e. "Plants") but if I choose "Prelim" and want to get ONLY the records that are really NULL, nothing is returned. I've tried Select '(NULL)' and 'NULL' as well with no luck.

In my dataset to retrieve the Order_Line (ol) data, if I use (ol.major_grp IN (@MajorGrp) OR ol.major_grp IS NULL) and I get the NULL records every time along with the real group I chose; if I use just (ol.major_grp IN (@MajorGrp)) and I get the Order_Line records that have a real value when I choose a single real value, but if I try "Prelim" to get just the NULLS, no NULLS are returned.

This is NOT a case where I want NULL to get everything, I literally want to get either Order_Line records that have either a real MajorGrp in them (C,P,R,S or W) or is NULL.

Upvotes: 0

Views: 69

Answers (1)

Jamie F
Jamie F

Reputation: 23789

You're really close.

Instead of (ol.major_grp IN (@MajorGrp) OR ol.major_grp IS NULL) and ... try (ol.major_grp IN (@MajorGrp) OR (ol.major_grp IS NULL and @MajorGrp IS NULL)) and ...

This should work if you are really getting a null through with your parameter. Sometimes this can be hard to control though, so sometimes I cheat a little and use a string such as "<No Value>" instead of NULL. Then I could do something like this:

(ISNULL(ol.major_grp, '<No Value>') IN (@MajorGrp)) and ...

This, of course, will not work if "<No Value>" is a valid value for your @majorgrp.

Upvotes: 1

Related Questions