Nulling other Date parameters in SSRS on selection of one

Question

I have set up multiple dates as input parameters and in SQL query, I've set up the WHERE clause like:

WHERE datecol1 >= @date1 OR (@date1 IS NULL)

So that handles dates as optional parameters. I have three date parameters. I want to make the report such that if the user selects one date, other two should be nullified, like the null should become checked.

Also, right now, I have this setting in default values part of the date parameter that i want to be dependent on the other date parameter.

What am I doing wrong here? Because this still does not work.

Answer 1

In the screenshots you're setting the expression for the Default values which will only change the values on the initial display of the report. This will not do anything to keep a user from changing those values, so you can easily get a render request with all parameters populated with something other than the default.

I'd suggest making it clear to your report users what is happening with these dates, don't do magic with their parameters behind the scenes. To do this, create an option in each parameter set (you can do something like UNION and hardcode the label and key) to ensure that report execution not only does what you expect, but makes this is clear to any other developer or user.

The user might select something like:

Param1 - "Not Used"
Param2 - 20151202
Param3 - "Not Used"

You can force this behavior with cascading parameters. You can run into issues with updates to cascading parameters though.

Setup like this:

@Param1

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES

@Param2

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1

@Param3

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1
AND -1 = @Param2

This ensures that you can only select the "Not Used" option for param2 if param1 selected anything except "Not Used". You can still set the default to be not used for all of these.