OBIEE 11g cachable prompt

3466124

Hello there, I have a unique problem. I'm using to dashboard prompts that are connected, but not in a traditional way (the limit values by another prompt box in prompt properties), The second prompt is generated as an SQL statement with a WHERE condition that collects the values from the first prompt via presentation variable. The problem is that when I select a  value for a second/third/fourth time in the first prompt, the second prompt still shows the value which can only be shown in a combination with the last selected values in the first prompt. It also shows the new values like it's suppossed to, but the default selection is the last values, and that is not correct. I hope I managed to explain it well.

Thank you in advance.

Regards,

S

3466124

my terrible childish rantings that I have edited

Christian Berg-0racle

a) We don't always see everything

b) Why haven't you bumped the thread earlier?

c) "I hope I managed to explain it well." No not really because what does that problem description have to do with the "cacheable" in the title?

d) If everything is so shitty - what are you still doing in this tech stack? Nobody is preventing you from changing

e) Nice to insult the forum participants. That will definitely get you answers and appropriate help. It's always a very intelligent strategy to insult your doctor as well when you're sick. Ace approach

Gianni Ceresa

Breath in, breath out ...

Feeling better?

3466124 wrote:thank you so much for being the worst support community in the world. it's enough that I have to deal with this shitty technology, but the official oracle help is worth NOTHING. I have been working with Oracle 5 years, and not once, NOT ONCE any of you so called "experts" ever helped.

Maybe worth to note that this isn't the "paid" support you get with your Oracle Support contract, it's a community where independent people having a job and a life as well as few Oracle employees try to help others.

If you want a formal answer from Oracle Support open a SR on support.oracle.com and then there you can complain about the quality of the tools or your experience with support.

Also good to note that you could at least say which OBIEE version you are using as there must be around 20+ versions (counting bundle patches) and they can all works in a different way or have different behaviours. ( )

Going back to your question, I have the feeling you are trying to abuse how things works.

Based on what you said the second prompt works just fine as values inside the prompt change dynamically and adapt to the newly selected value.

So where is the issue? Apparently you expect the selected value to change as well or at least to be removed if it isn't part of the new values anymore.

Well ... sorry to disappoint you but in many versions (I don't remember all of them) even with default prompts linked with "limit values by" it works like that.

Once a value is selected it is selected...

But yeah, what else could you expect from the worst community in the world about shitty technology where some of the so called "experts" just lost 5 minutes of theirs life to reply to your question...

PS: if it's such a shitty thing, why in 5 years you never managed to change job? Not saying the problem is maybe somewhere else ... but who knows ... Also Alt+F4 works great in case

BluShadow

*** Moderator Note:

As others have mentioned, this is not the "official" oracle help channel, so the problem appears to be that you don't know where to post for official help.

Official support can be done through support.oracle.com which is the My Oracle Support (MOS) platform rather than the OTN platform.  There you can raise a Service Request (SR) with Oracle directly, once you've logged in with your account that is associated with your Customer Support Identifier (CSI) - this is available to any paying customers.

Offending public members and use of unprofessional language is not acceptable on the community.  Your words have been noted, and any further offensive language will be notified to the community managers, who will take the action they deem appropriate (which could result in termination of your account; not good if you use it to access official support on MOS as you'll have to explain that to your manager)

*** End of Moderator Note

3466124

I thought, obviously wrong, the only way for someone replying was raising a couple of red flags. I stand by what I said but not by the way I said it. Oracle user support is disastrous.  Sorry if I have mistaken you for an official support forum, but come one, community.oracle.com kinda looks like it's official. I really don't have any  time to research which is official , and which is not, especially for the entire procedure for official help. It's really time consuming. Also, I have no time to explain why I'm working in this technology and why I didn't change jobs, that's really not the issue here. Currently I'm stuck using Oracle, we have users using this platform and we're tweaking OBIEE reports everyday, creating new reports for them etc. I do sicerely apologize for using offensive language, but I got frustrated cause I couldn't have figured out how to solve my problem, and I was on a deadline. Still am. Nevertheless, it was unprofessional to use that kind of inappropriate language and I really do apologize to each and one you.

Back to my problem. English is not my first language, so I will try to make this simple as possible. I have two dashboard prompts. They're separate entites, saved as let's say prompt 1 and prompt 2. Prompt 1 is a select list straight from a database table. I put the selected value in a presentation variable, let's call it p_var1. Now, prompt 2 is a bit more complicated. It's created via SQL in prompt creator (when you select new ->dashboard prompt, select the subject area etc.). Instead of using a database table as a direct source, I use SQL to fetch the values I need from a table with a WHERE condition where a column has to find a matching values from p_var1 (prompt1). The select for prompt 2 looks like this:

SELECT  CASE WHEN "OPUS"."TWR_PARM_NAP1"."SIM"='D' THEN '"Sim"'  else '"NonSim"' END from "OPUS" where "OPUS"."TWR_PARM_NAP1"."GP"='@{p_var1}{K01STV}'

So, when I select a value from prompt 1 and when it gets put into p_var1, prompt2 should show values  that correspond with my WHERE condition in prompt2. It works fine the first time, couple of more times. But after a while, the value from previous iteration gets stuck, and it won't change. That's why I added cachable prompt into my post title. I was wondering are prompts using some sort of cache memory which remembers previous states disregarding if I disabled cache by checking the Bypass Oracle BI Presentation Services Cache box in the Advanced settings on my reports (which I did) and added this code into the prefix box on the same page : SET VARIABLE DISABLE_CACHE_HIT=1; (which I did). So does this have any effect on prompts or?  The problem is , that this gets kinda fixed for a little while after I go to Administration -> Issue SQL and execute : {call SAPurgeAllCache ()} couple of time, kill all sessions etc. But this is not a perminent fix. Some days the prompt1 and prompt2 colaborate wonderfully, but sometimes the value in prompt2 just  gets stuck. Hope I was more clearer this time.

And again, I really really apologize for offending anyone, of course I don't want to generalize and insult all of you, I've had bad experiences with the oracle community in the past (official and other) but I should not judge all of you. It was poor judgement on my part and frustration talking out of me.  I really believe that someone can help me here.

Thank you very much,

Regards.

s

3466124

bump

Gianni Ceresa

Did you read what I wrote above?

Because I actually gave you inputs on your original post (in the middle of the reply to your second one...).

Aren't you in the described case?

All the cache things ("checkbox" , prefix box etc.) are in the analysis using the value of your 2 prompts. But the prompt itself doesn't have these things.

3466124

I did, but it doesn't help me at all. That is why I wanted to elaborate. So basically I can't do anything. Also, you can't put limit values by other prompts if a prompt is generated by SQL which it is in my case. So, no... that didn't help. And the prompts are divided, two different prompts, they were not created in the same prompt creator so you cannot connect them with limit values by even if they weren't SQL generated.

Manoj Dixit

I shall try to help.

Version : OBI EE 12.2.1.2.0.

On a page where dashboard prompts' scope is limited to Page, I have two dashboard prompts.

Dashboard prompt 1: 'Choose Day Range within a Month' which is defined as under.

Type = Presentation Variable.

User Input = Radio Buttons (as I want the user to select one value only).

Radio Button Values are 'First Ten', 'Second Ten' and 'Third Ten'.

Default Value = First Ten.

Prompt Buttons : Show Reset button (apply button is disabled as ideally there should be only one Apply button on a page).

Dashboard prompt 2: 'Day of Month'.

Purpose: Display day of the month values based on the selection of the first prompt. This prompt is defined as under.

Prompt For Column = "Day"."Day Of Month".

Operator = is equal to / is in.

User Input = Choice List.

Choice List Values = SQL Results.

SQL Statement =

SELECT "Day"."Day Of Month" FROM "Subject_Area_Name" where ("Day"."Day of Month" between 1 and 10 and '@{pv_days_of_month}{First Ten}' = 'First Ten') or ("Day"."Day of Month" between 11 and 20 and '@{pv_days_of_month}{Second Ten}' = 'Second Ten') or ("Day"."Day of Month" > 20 and '@{pv_days_of_month}{Third Ten}' = 'Third Ten') FETCH FIRST 10 ROWS ONLY

Enabled user to select multiple values.

Prompt Buttons Apply and Reset are checked for this prompt.

The Analysis has a filter defined as 'Day of Month is prompted'.

When the page is hit the Analysis fetches data for all the days as Day of the Month is not yet selected. Note that by default, 'First Ten' value is selected in the first prompt.

When a user selects 'Third Ten', automatically the second prompt displays days above 20.

And when the user selects 'Second Ten' in the first prompt, the second prompt responds and displays days between 11 and 20.

Day of Month selection:

Now I select 11, 14, and 19 day values of a month and click 'Apply'.

The Analysis displays data for the days of month values selected in the second prompt.

Now, if I select 'First Ten', the second prompt will display days of month between 1 and 10 along with 11, 14 and 19 which were already selected. This is a normal behaviour. If you do not wish to display those previously selected days, then you need to use 'Reset' option 'Reset to default values'.

So it's a combination of how you use Apply, Reset options. As, the first prompt does not really need 'Reset' button at all.

Regards,

EDIT: Sorry, I should have tried it in 11g.

EDIT (27/05/17): I shall try to explain with examples what @Gianni Ceresa mentioned.

Gianni Ceresa

Thanks for confirming what I was saying with visual examples

As said: once selected it is selected and it will not be unselected without manual action (unselect them or reset).

In 11g it's the same business, that's how OBIEE worked, works and will probably continue to work (and it makes sense ....).