Forum Discussion

mkavcic's avatar
mkavcic
Frequent Visitor
4 years ago
Solved

Paginated report - optional parameter

Hi,   In paginated report, I would like to check if nothing is selected in the parameter, then this parameter would not be used in the dax query.   How can I achieve this?   For example, I hav...
Need Help
Show and Tell
Tips and Tricks
  • mkavcic's avatar
    mkavcic
    4 years ago

    Hi,

    Thank you for your answer!

     

    I did solve it in this way:

    1. Add a 'dummy' item called 'All' to the list of items in parameter query.

    2. Make the new item 'All' defualt. You could also use 'Null' here.

    3. Modified the DAX query in VBA with query.commandtext where I entered the command IIF(Parameter.Value = "All", "", "__Usefilter"), meaning if there is 'All' selected then do not use the filter, but if one item is selected then use the filter.

     

    Hope it helps.

    Thank you!

     

Surendar88's avatar
Surendar88
Frequent Visitor
4 years ago

Hi mkavcic 

If there is no default value for the optional parameter, then how would we handle it. 

I need help with DAX query, how to ignore the optional parameters. If I use or condition to check if its blank or the value passed, it is very slow. 

 

It will be very helpful for me. 

 

 

  • mkavcic's avatar
    mkavcic
    Frequent Visitor
    4 years ago

    Hi Surendar88 

    You should add a union sql to the dataset for the parameter. In the union add 'All' to the dataset.

    Then in parameters, add 'All' option as the default option.

    Then in main report dataset query, you need to edit query in VBA code where you check for 'All' option in the parameter and if it's there, then you don't use this parameter in the query, and if it's selected, then you use it in the query. 
    So you created a dynamic query.

    Hope it helps.