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.
HI Paul
I support customers who can’t handle working with MDX in text mode. The issue I’ve never figured out (after a couple years now) is how you can make the query designer filter out rows based on the value of a measure.
The sad thing I often see is customers creating a query that says “show me sales by employee for a certain time period.” 10 of these employees will have an actual value, while the others don’t have any real values.
I keep thinking there must be a way to filter out (in the query, not the report), those rows where Measure.x is null, but I’ve never been able to do this with the query designer in visual mode.
is it possible?
I’m not a fan of using the old-style report models and you’ll find that most SSRS professionals don’t use them. The writing is on the wall for deprecation.
How does this work when we’re working with Report Builder Query Filters based on report models? i.e. if I use
NOT Is Blank or Empty it still returns values that are either empty or NULL.
In fact when I capture the SQL SSRS generates for NOT Is Blank or Empty I see a construct like this.
AND NOT ( ([tbl_pmr_PMRs].[BuildName] = ”
AND NOT ( [tbl_pmr_PMRs].[BuildName] IS NULL )
OR [tbl_pmr_PMRs].[BuildName2] IS NULL)
)
But this seems to still pick up those that are Blank or Empty