Some seemingly simple filtering logic can be more difficult to achieve when using
a dataset filter rather than the WHERE clause of a SQL query. The problem with
comparing Null values in an expression has little to do with the features of Reporting
Services but with the behavior of the Null value. Because dataset filters are
based on testing for equality, there isn’t an out-of-the-box method to check for Nulls.
The value Null is never equal to anything – even itself.
If you were testing a field value for Null in a SQL query rather than a dataset filter,
you would use the expression: WHERE StandardCost IS NULL or WHERE StandardCost IS
NOT NULL.
An expression such as WHERE StandardCost = NULL won't work because the value Null
doesn't play that way.
A dataset filter is evaluated for each row returned by the dataset. To test
for Null in a dataset filter, use one expression to test for whether the field contains
a Null value by using the IsNothing function. To include the row in the results,
return the field value from the expression. To exclude the row, return a value
that won't match when compared with the field in the same row. As an example,
I'll use the DimProducts table in the AdventureWorksDW sample database. If you
want to return all records where the StandardCost field does not contain Null, define
a dataset filter with an expressions to compare to the StandardCost field them using
the "=" operator:
Expression: =IIF(IsNothing(Fields!StandardCost.Value), "", Fields!StandardCost.Value)
You can actually use any value in the second argument in place of "" as long as it
doesn't match the field value.
Weblog by Paul Turley and SQL Server BI Blog.
