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.
Do you know someone with an IT background who could use a break, is passionate about technology and wants to make a difference in the community? This is an opportunity for 24 deserving individuals to build skills and career credentials while giving back. Maybe it’s you or maybe it’s someone you know. Applicants propose a software project for a non-profit organization and submit a proposal to the deciding committee. Awardees will receive a wealth of software, licensing, training, books and certification exam vouchers – upon which to help build a new career.
A group of Microsoft MVPs; led by my friend and founder of the Oregon SQL Developer’s Association, Arnie Rowland; have pooled our resources and negotiated with Microsoft and other vendors to offer materials and services, valued at several thousand dollars, to support the development of a community project and the a person’s career development. The awardee is free to keep and use the software and all the materials for their own use within the license terms.
Applicants must be unemployed or underemployed and have a background or aptitude in software development or information technology. The project recipient must be a qualifying non-profit or charity. Awardees will receive multiple licensed activations for literally hundreds of Microsoft & partner products (nearly all of the desktop & server operating systems, development products, tools and business applications) including the following and some additional materials:
Microsoft: Visual Studio Ultimate with MDSN subscription,
Pluralsight: three (3) month Standard subscription to On-Demand!™ .NET Training Library,
APress: three books(3) of the winner’s choice,
O’Reilly Press: three (3) eBooks of the winner’s choice,
ComponentOne: license for Studio Enterprise components and tools,
Microsoft Learning: Vouchers for two (2) Free Certification Exams
Quest Software: license for Toad® for SQL Server
The program details and application form are here on Arnie Rowland’s blog
Microsoft and other companies make these resources available to MVPs to share at our disgression. Through this program, we hope to make a difference in the lives of people in our communities; both the developers and benefactors of these projects.