August 19, 2011 at 8:11 am
I have a user report request that required the date field paremeter to return records where the date is >= somedate or return records where the date is NULL.
I can't seem to figure out how to accomplish this with a single report parameter. If this is possible I would appreciate some suggestions. A solution using multiple parameters would be OK too.
Thanks in advance.
Tim
August 19, 2011 at 11:28 am
slimchance99 (8/19/2011)
I have a user report request that required the date field paremeter to return records where the date is >= somedate or return records where the date is NULL.I can't seem to figure out how to accomplish this with a single report parameter. If this is possible I would appreciate some suggestions. A solution using multiple parameters would be OK too.
Thanks in advance.
Your parameter should be just a date. Your dataset query should have a constraint something like
WHERE
(
TableDateColumn >= DateParameter
OR
TableDateColumn IS NULL
)
August 19, 2011 at 12:30 pm
Thanks for the reply.
It is not quite that simple. In your scenario, the user would enter a date as the parameter and would get back all records newer that that date AND all of the null records. They want the report to return all of the records after the selected date OR return the null records.
Tim
August 19, 2011 at 1:33 pm
slimchance99 (8/19/2011)
Thanks for the reply.It is not quite that simple. In your scenario, the user would enter a date as the parameter and would get back all records newer that that date AND all of the null records. They want the report to return all of the records after the selected date OR return the null records.
If that's the case, couldn't you make the Date parameter nullable and do something like this in your where clause?
WHERE
(TableDateColumn >= @Date)
OR
(@Date IS NULL and TableDateColumn IS NULL)
August 19, 2011 at 2:10 pm
If you have to use only 1 parameter, change your date parameter to a text parameter. Then they can freehand in their date, or the word null or empty or what ever convention you can get them to use. Then in your dataset query add some handling for a value of null. Something like
WHERE
(
(@InputParm = 'Null' AND TableDateColumn IS NULL)
OR
(@InputParm <> 'Null' AND TableDateColumn >= @InputParm)
)
I am not sure if this would evaluate and execute properly because of when @InputParm is 'Null' it won't know how to handle TableDateColumn >= @InputParm. If that is the case, then change your input from 'Null' to some dummy date that you know will never be used such as 1/1/1900 and then evaluate against that.
August 22, 2011 at 7:22 am
Perfect. Thanks.
Tim
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply