SSRS 2008 parameter dates and nulls

Question

I have a report of customers that I wish to run in SSRS. The report I want to return is for a particular period (e.g. 01/01/2016 and 29/02/2016). The parameter is against a date field (End_Date).

What I would like to return is a list of customers WHERE End_Date is either BETWEEN the dates above (or any other period) and WHERE End_Date IS NULL too.

I am able to create a parameter that will list customers with an End_Date between the dates I want but how do I also get the parameter to also list the NULL values.

Hopefully that's clear but just in case - I need a list of customers where End_Date is between two dates or NULL.

Thank you

Answer 1

You need to account for the possibility of a null in your evaluation using an OR for the END_DATE.

Are you using the parameter in the query or on the dataset? The SQL is a bit different that the SSRS expression.

SQL

WHERE DATE_FIELD >= @START_DATE AND (DATE_FIELD <= @END_DATE OR @END_DATE IS NULL)

SSRS

=IIF(Fields!DATE_FIELD.Value >= Parameters!START_DATE.Value AND (Fields!DATE_FIELD.Value <= Parameters!END_DATE.Value OR ISNOTHING(Parameters!END_DATE.Value), 1, 0)

In the other filter properties, set the type to Integer, Operator to =, and Value to 1.

This will evaluate the expression and return 1 if it matches and 0 if not - then it filters for the 1.