OBIEE - Variable Prompt behavior

Cameron Loepker

OBIEE Version: 12.2.1.3.0

Hello Board,

I am need of some advice, hoping someone can tell me how easy this fix will be

I have a variable prompt which is listing out 1- 10 as a static prompt which is then tied to an 'RCOUNT' column in the report (tied via presentation variable.)

My question is: how do I get the "All Column Values" checkbox to reappear in this new variable prompt drop down? Currently I am select one of the numbers, the prompts to reflect said number to show only that row, but then when I hit the drop down again - it doesn't show the "All Column Values" box again at the top and instead has the '1' at the top. This might seem trivial but it would definitely cause more clicks aka more work for an exec and we all know that wont fly

is there some code i can put in the SQL results portion of the variable prompt edit section? or some other way to get that drop down to behave uniformly the same after it has been applied for a specific row?

please let me know if you need more detail or something was stated vaguely....

before after

the variable prompt edit screen

Jerry Casey

Rather than using the "%" wildcard, you could enter a custom value of "Rows 1-10" in your prompt, then in your query filter:

'Rows 1-10'='@{PV_RN}{xxx}' AND RCOUNT(column)<=10

OR

RCOUNT(column)=@{PV_RN}{xxx}

Gianni Ceresa

Your 2 prompts are the same exact one? They aren't ...

The "All Column Values" is an option existing only on column prompt, as soon as your prompt is of a different kind (variable prompt either with manually provided values or LSQL based values) it doesn't exist.

"All Column Values" isn't a list of all the possible values of the column, it's a special value which tell the filter (of type "is prompted") to just be ignored.

If you store that thing in a presentation variable you will see it has a weird value which needs to be checked as a OR: condition for when the variable has a value OR condition when the variable is the 'All column values'.

So what you try to do is not like the tool works: use a column prompt and you will have "All Column Values" in your prompt.

Cameron Loepker

Yes, those two prompts listed are the same prompt and are both variable prompts....

in the prompt edit screen when i showed above - have the checkbox that says "Enable User to Select Multiple Values".. i was thinking this was the mechanism which allowed the all column values choice to appear...

Gianni Ceresa

Well ... I would say that's a bug in 12.2.1.3 ...

This is the option to add the "All Column Values" on top of a prompt:

And it is supposed to exist only with column prompts because of the special behaviour of this being a keyword and not the actual full list of values.

I don't have 12.2.1.3 to test right now, I'm using a newer version which works as expected.

Because it's a variable prompt and you defined values by hand, just add as first value something called "All Column Values" and deal with it logically in your filters.

Christian Berg-0racle

+1 to Gianni. That option isn't there for any variable prompt in the new versions of OAC either.