Forum Stats

  • 3,851,556 Users
  • 2,263,999 Discussions
  • 7,904,778 Comments

Discussions

OBIEE - Variable Prompt behavior

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....

beforerow number drop down.PNG after row number drop down after selection is applied.PNG

the variable prompt edit screenvariable prompt .PNG

Tagged:

Answers

  • Jerry Casey
    Jerry Casey Member Posts: 286 Silver Badge
    edited Aug 6, 2019 12:07PM

    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)[email protected]{PV_RN}{xxx}

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,675 Blue Diamond
    edited Aug 6, 2019 12:35PM

    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
    Cameron Loepker Member Posts: 33
    edited Aug 6, 2019 12:55PM

    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
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,675 Blue Diamond
    edited Aug 6, 2019 1:06PM

    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:

    Capture.PNG

    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
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,671 Gold Crown
    edited Aug 6, 2019 1:10PM

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

    pastedImage_0.png