OBIEE 12c prompt default value issue

Chandra M. G.

I am using OBIEE 12C, facing below challange would be great if somebody able to help me.

Having 3 prompt on my dashboard having values depending on each other like P1, P2, P3. Choose value from P1 then using SQL query to fetch value under P2 then again using SQL query fetching value for P3 prompt which taking input from previous prompt.

Now issue which i am facing is, like i choose P2 then apply then choose value from P3 then apply it shows data but now i am changing value P2 again and apply then in this case P3 has default value which i choose earlier so shows wrong data. Then in this case again i need to choose P3 & then apply.

Is this possible when i am changing value for Prompt P2 & apply then P3 old value should be wipe out so that it does not show wrong picture of data.

Thanks

Chandra M.

Robert Angel

Only with javascript.

asim cholas

Cant you use constrain option? Isnt the columns are related?

Chandra M. G.

Guys, let me put my issue one by one which will help me to resolve issue.

First issue which i am facing, like Having Two different prompt BU & PRODUCT which are coming from same table.

In BU, i am fetching data under Choice List Values using SQL results like below & assigning to presentation variable pv_lob.

SELECT "Master Mappings"."Lob" FROM "delivery report" where  "Master Mappings"."Lob" is not null FETCH FIRST 65001 ROWS ONLY

Now another prompt is PRODUCT which is fetching data under Choice List Values using SQL results like below

SELECT "delivery report"."Master Mappings"."Product Name" s_1 FROM "delivery report" WHERE (("Master Mappings"."Reporting" ='Yes') AND ("Master Mappings"."Lob" IN (@{pv_lob}['@']{'na'}) or '@{pv_lob}' = '') AND (("Master Mappings"."Effective Date" IN (SELECT max("Master Mappings"."Effective Date") FROM "delivery report" FETCH FIRST 65001 ROWS ONLY)))) FETCH FIRST 65001 ROWS ONLY

Now issue is, when i choose first prompt BU value then first i need to click on APPLY button then only it shows filter data of second prompt PRODUCT which is unacceptable by user, it should refresh automatically as soon as choose value in first prompt.

Please help me to achieve the same.

asim cholas

Can you try creating an opaque view in RPD(which is not a great practice) with the two prompt columns and apply all the filters required there. Join it with the fact and use if for prompt. Then you can constrain the records at prompt level and no need to use SQL results.

Chandra M. G.

Hi Asim, Is there any helpful page or docs to create opaque view & use that as i am new to OBIEE so would be greate to hear back.

Robert Angel

This is not difficult, you just create a new table in the physical layer but change the source to SQL and write / copy your code in...

asim cholas

https://gerardnico.com/wiki/dat/obiee/opaque_view

obieenow.blogspot.com/2014/04/opaque-view-in-obiee.html

Chandra M. G.

Hi Guys, I achieved same by removing "Apply" button now as soon as i select any value from prompt it automatically refresh another prompt value by fetching as per SQL.

Now second issue which i found in so many communities but unanswered is,

Like i had applied filter & values are displayed but when i am changing filter value then dependent filter is having old choose value as default one. Dependent filter is populted with new value as well in drop down but default is selected which was earlier. So in this case before applying new filter i need to RESET every time, Is there a way as soon as i change filter my another dependent prompt filter old value is cleared.

Please help me to achieve the same.

Thanks

Chandra M.

asim cholas

Dear Chandra,

There is an option in OBIEE to achieve this without any SQL

But if you want it to work you cannot use SQL Results. For that you need to bring the data in columns(the same filters you are applying here should be at RPD level).

when i selected male in first prompt it is displaying only Male Actors.

When i selected a female actress, only female is displayed in the first prompt. No need of apply button.

Robert Angel

I think he knows this, but what he wants is that if you change either value then the other prompt goes back to its unselected state, as cf your male / female example you could; -

Select Gender Male

Select Actor Tom Cruise

Go back to Gender select and select Female

Leaving Tom Cruise Female.