Optional prompts concept only available in Webi reports and we cannot define the Optional prompts through universe predefined conditions / prompt and it can be done only at report level and also it cannot be shared with other report. If you want to use the same optional prompt for multiple reports, then you must create the same optional prompt in each report manually.

Mandatory prompts are either a predefined universe conditions or you can define at report level. If it is universe level predefined conditions, then you can re-use those conditions / prompts for any number of reports.

Universe Level Predefined Mandatory Prompt:

Report Level Mandatory Prompt:

We have huge of numbers of reports using Mandatory prompts (all are predefined prompts at universe level) and user wanted optional prompt facilities for some of the reports. We have updated few of standard reports to optional prompts by removing the predefined universe level prompts.

After the change we have identified, those updated optional prompt reports are taking longer time to refresh compared to mandatory prompt reports. We have analyzed both the reports and found that, in the optional prompted report SQL generation function is executing twice and that is what making longer report refresh.

Optional prompts may or may not have values and so the SQL generation could change after the prompt dialog appears. For example, if an optional prompt value is not selected, then the Where clause will omit that object. With Mandatory prompts, the SQL structure will always be the same before and after prompts are selected so it does not need to regenerate the SQL after a value is selected. So, Optional prompt can give different performance results so it should be considered before choosing one or the other and as long as you know this, you can optimize your report design.

Design your report based on report requirement with or without optional prompt and improve the performance.

Thanks for reading this post and share your comments.