Forum Discussion
Handling Multi-Value Parameters with Optional Blanks in Microsoft Report Builder
Hi everyone, I'm working in Microsoft Report Builder and have a report with multiple parameters, including multi-value parameters. I want to ensure that when one parameter is left blank, it doesn't ...
divyed
Super User
Super UserHello doodlez,
I don't believe multivalues parameters can have blank. I mean if you select a parameter as multvalued you can opt for allow blank value.
Considering you have 2 parameters P1(single valued) and P2 (Multi valued), you can do :
1. Allow P1 to have null values and pass null as default
2. Modufy where condition to handle
where 1=1
and
(
(P1 is null or P1='') and P2 in (:p2) // this will work when P1 is blank but P2 has values
OR
( P1 on(:p1) and P2 in(:p2) ) // This will work when both P1 and P2 has values
)
I hope this will solve you issue. If yes, please mark this as solution
Cheers
- AND (a.terminal_key IN (:terminal_key) OR :terminal_key IS NULL OR :terminal_key = '') - If I write this, the report returns all records without filtering by terminal_key when none is selected. However, if I choose multiple values for terminal_key, the report fails to run. AND a.terminal_key IN (:terminal_key) If I write this, I can run the report and select multiple values for terminal_key. However, if the parameter is not selected or, the report does not return records. I can not mix this 2 together, I want both: choose multiple values and also avoid dependency.
