Two prompts on the same dashboard

3294318

Hello and good morning,

I have two prompts on the same dashboard from different data sources. However, the prompts are generating the same inputs which is Location. I want to be able to have the first prompt automatically change the second prompt to the same location, and vice versa. For example, if I change the location to be New York in the first prompt, I would want the second prompt to change to New York as well. Is this possible to do in OBIEE? What code or steps would I need to use to generate this?

Thank you for the help!

ArijitC

You can try this ...Have two presentation variable pv_1 and pv_2..then use SQL result as prompt value ....and try something like this ....

in 1st prompt value

SELECT    first_source  FROM 1st_table WHERE first_source in (select 2nd_Source from 2nd_table where @{pv_2}{dummy} or 'dummy'[email protected]{pv_2}{dummy})

2nd Prompt Value :

SELECT    2nd_Source  FROM 2nd_table WHERE 2nd_Source in (select first_source from 1st_table where @{pv_1}{dummy} or 'dummy'[email protected]{pv_1}{dummy})

ArijitC

Also you can check if this option works

3294318

Thanks for the quick response. I tried the examples from above and I am getting an error as seen below.

Here is what I put in for the values.

First prompt:

SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE 'dummy'[email protected]{pv_2}{dummy})

Second prompt:

SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE 'dummy'[email protected]{pv_1}{dummy})

I also tried it with the first option you gave as well (@{pv_2}{dummy}). With that option the code looked as followed:

First prompt:SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE @{pv_2}{dummy})

Second prompt: SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE @{pv_1}{dummy})

What does {dummy} refer to?

ArijitC

I just wanted to give you an idea and what I wrote was not syntactically correct. It should be

for Prompt 1.

SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code"

in (SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" = @'{pv_2}{dummy}' or 'dummy'[email protected]'{pv_2}{dummy}')

Here ‘dummy’ is a default value of pv_1 or pv_2. So, when nothing is selected from the selector, the value of presentation variable is ‘dummy’…you can call it anything else, if you like . The idea is that, "OR"  section of the query is always true if nothing is selected. When something is slected in prompt 2 then that gets applid to prompt 1. see if this works.

3294318

Here are the prompts I created. I am still getting that same error.

1st prompt:

SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" = @'{pv_2}{dummy}' or 'dummy'[email protected]'{pv_2}{dummy}')

2nd prompt:

SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" = @'{pv_1}{dummy}' or 'dummy'[email protected]'{pv_1}{dummy}')

Does Default selection need to be changed from None to another option? I also tried changing this to SQL Results and adding the code but still get the error.

ArijitC

Sorry,I have to step out now. I will try to replicate it in my environment tonight, and reply to you. 

Thanks

Arijit

ArijitC

Here is how I replicated it ….

I have 2 tables cost center and cost center Attribute. Both of them has column member_name which has very identical data.

I have dashboard prompt member_name1 and member_name2. When nothing is selected both prompt will show full value list.

If I select any one of the prompt then the other gets filtered based on the current prompt.

Now here is the process….

Query:

SELECT "Cost Center"."MEMBER_NAME" FROM "HYPEIS" where "Cost Center"."MEMBER_NAME" in ( SELECT "Cost Center Attribute"."MEMBER_NAME" FROM "HYPEIS" where "Cost Center Attribute"."MEMBER_NAME" ='@{pv_2}{dummy}' OR 'dummy' ='@{pv_2}{dummy}' )

Query:

SELECT "Cost Center Attribute"."MEMBER_NAME" FROM "HYPEIS" where "Cost Center Attribute"."MEMBER_NAME" in (SELECT "Cost Center"."MEMBER_NAME" FROM "HYPEIS" where "Cost Center"."MEMBER_NAME" ='@{pv_1}{dummy}' OR 'dummy' ='@{pv_1}{dummy}' )

One more set up to apply the prompt as soon as it is selected. Uncheck the apply and reset button.

Thanks

Arijit

3294318

That worked! However, I have one problem with it. It does not update the second graph, you still have to manually select the one location that is in the list. Is there a way to have that automatically selected?

First prompt: For example, I select BIG out of this list.

Second prompt: After first one is selected

Is it possible to have BIG selected automatically without having to select it?

Also, it's a bit quirky if I want to change locations as well. It's like I need to Reset both locations to get the full list back. Or is there an easier way around this for the users?

Thanks!

ArijitC

The 2nd graph you have problem with , should have pv_1 and pv_2 presentation variable filter in it. not a regular column filter. That should solve your graph problem. You can not do an automatic selection for 2 different table like that .

The other option you have is really a Graph prompt or Table prompt. In a dashboard prompt what you are looking for is not possible.

Thanks

Arijit

3294318

I was able to find a way to sync them up so when I change the first prompt, it changes the other one automatically. I went into the second analysis and I added a Filter which "is based on results of another analysis" and I pointed it to the first graph so when the first graph changes location, the second one changes automatically.

Thanks for all the help!