CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
Will F
Will F

Reputation: 437

NOT IN affecting output

I have a fairly simple script, but one field (person_name) has some test users' names (e.g. 'John Smith') in that I want to exclude from a report I am creating. The field also has some null values, which I want to keep in the report.

I have put in:

AND person_name NOT IN ('john smith', 'sally brown') etc.

This removes the names from the report but, for some reason, it has also removed the NULL values from person_name. As mentioned, I want to keep the NULL values in.

My question is, is there a simple reason why NULL has vanished from the report as soon as I start blocking certain names?

I am rather puzzled by this and any pointers would be appreciated.

Thanks.

Upvotes: 0

Views: 26

Answers (2)

stubaker
stubaker

Reputation: 1958

To work with NULLs, SQL uses three-valued logic which means predicates can evaulate to True, False, or Unknown. Since a NULL mark can represent either a value that is not currently known or one that does not exist, comparing anything to NULL evaluates to Unknown.

Going back to your predicate above and assuming it's part of the WHERE clause, only rows that evaluate person_name NOT IN ('john smith', 'sally brown') to True will be returned. Since the NULL rows evaluate to Unknown, they are excluded from the result set.

To ensure NULL rows are included, you can add a specific check for them like:

WHERE (person_name NOT IN ('john smith', 'sally brown') OR person_name IS NULL)

See also: the NULLs and three-valued logic section of this SQL Server Pro article and Quassnoi's great post on his ExplainExtended site.

Upvotes: 1

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

Whenever I've tried to use NOT IN/ NOT LIKE type filters in SSRS I've always run across lots of oddball situations like this. Try the following in your expression:

=IIf(IsNothing(Fields!yourfield.Value)=True, "testguy", Fields!yourfield.Value)

instead of just

[yourfield]

Keep the operator and Value the same.

Upvotes: 0

Related Questions