Handling Multi-Value Parameters with Optional Blanks in Microsoft Report Builder

[doodlez]

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 affect the results or cause issues with the other parameters.

Here are my specific requirements:

1. Users can leave a parameter blank without it influencing the query or results.
2. If a parameter is selected, it should filter the results accordingly.
3. I'm working with multi-value parameters, so I need to handle cases where users can choose multiple values or leave it blank.

Has anyone encountered a similar issue or can suggest a solution to handle multi-value parameters with optional blanks in Microsoft Report Builder?

Thanks in advance for your help!

I try:

AND object_key IN(:object_key) - It works but can't left blank object_key

AND object_key IN (:object_key) OR :object_key IS NULL OR :object_key = '' - it can left blank object_key but can't choose multi-values.

From Options: Data_type (TEXT), Allow blank value (""), Allow multiple values

[Anonymous]

Thanks for divyed's concern about this issue.

Hi, @doodlez 

A multi-value parameter cannot include null values:

Perhaps you could set a default value for the parameter - set the parameter to all values of the field by default, so that you don't need to set the parameter, but at the same time you can select the desired value for the data query as required:

Parameter_OrderName is the dataset used to get the parameter OrderName:

When you run the report, it will default to select Select All:

You can select the desired value for the data query as required:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[doodlez]

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.

[divyed]

Hello @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

[doodlez]

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.