Using SSRS 2016. Have a request to use 2 parameters for the report, but only one of them needs to be populated before you view report.
If report has ParmA and ParmB, only one needs to have data selected or entered. But at least one needs to have data.
Thanks
Yes - it is possible to have two parameters and only have one of them populated. Both parameters could be set to ALLOW NULL VALUES.
Unfortunately, there's no way to force the user to select a parameter in this case.
A workaround that might work would be to create THIRD parameter that checks for a value in either of the other two parameters with an available value of 1.
The default value would be based on a query that checks for a value in the two other parameters.
SELECT CASE WHEN @PARAMETER1 IS NULL AND @PARAMETER2 IS NULL THEN 0 ELSE 1 END AS PARAMETER_CHECK
Unfortunately, there's no way to give an error message for the first two parameters based on the third parameter.
If the parameter is text with no Available Values specified:
Set the option to allow blank values.
Update the prompt to say "(optional)" so the user knows it's not required.
Update the SQL to be something like this:
WHERE (@PARAMETER1 = '' or @PARAMETER1 = Column1)
If using a single-value dropdown:
Add an "All" option. This is usually selected by default.
Update the SQL to be something like this:
WHERE (@PARAMETER1 = 'All' or @PARAMETER1 = Column1)
If using a multi-value dropdown, they can already select all values so it's usually not necessary to do anything more.
For dates there isn't a good way to make them optional. Some common workarounds are:
If both parameters are left blank, you can handle this case in the SQL and return no rows. Then in a table in the report, set the NoRowsMessage property to explain that this is not allowed.
