promptmany macro for an optional prompt

[bwalikar]

Hi all,

I am trying to include an optional multi-select prompt in SQL component of report studio. Can you please suggest what should be the default value for the prompt to stay optional. I do not want to hard code any particular values in default value.

Currently I am using below expression :

STATUS_CODE in (#promptmany('Status')#)

Any help would be greatly appreciated !

[bus_pass_man]

Prompt and promptmany have 6 parameters, some of which are optional.   None of them allow for the specification of whether the prompt is optional. 

( prompt_name , datatype , defaultText , text , queryItem , trailing_text )

If a prompt filter is created as a filter in a query subject then it has a property which you can switch on to make the filter optional.   

If you create it as a stand alone filter then the fact that it exists as a stand alone filter means that it is optional and will only come into play if someone adds it to the report.

Also, by being in the model, it is available for every report or dashboard and for everyone using the model.   They are not enslaved to some hard-coded SQL report and you are not enslaved to maintaining them.

So wouldn't it be somewhat simpler if you create the prompt filter in your FM model? 

[bwalikar]

Thanks for the response. I completely get your point . But in this case we have a complex SQL embedded in the report and there is a need to filter the SQL data based on the optional prompt in the prompt page.

If I try to include the filter in report query subject then it is impacting performance big time and for obvious reasons.

[Andrei I]

HTH
https://www.cognoise.com/index.php/topic,35234.msg116444.html#msg116444

[bwalikar]

Thank so much for the referral link.  It worked !