How to make prompts optional in Logical SQL based OTBI analysis

Question

Hi Experts,

We are trying to build OTBI analysis using logical SQL approach and there are few prompts required which are not a part of Select statement so we are trying to add the prompts in the SQL.

When trying to access the same, if we pass the prompt value then it is returning the output for the prompt selected, but this should be optional. Please let me know if anyone has any idea on this.

Syntax used:

"subject area dimension"."column name" in (@{p_param}{'All Column Values)'})

Thanks in advance.

Aaron Leggett · Answer

Change 'All Column Value' to just %.

E.g. "subject area dimension"."column name" in (@{p_param}{%})

You also don't have to add them into the SQL itself (unless you're filtering subqueries); you can just do this via the criteria tab where you'd usually add filters/presentation variables:

This will return all values by default if no default value is specified on your dashboard parameters.

Thanks

Aaron