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.