Categories
Two prompts on the same dashboard
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!
Answers
-
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'=@{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'=@{pv_1}{dummy})
0 -
Also you can check if this option works
0 -
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'=@{pv_2}{dummy})
Second prompt:
SELECT "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE "Cognos_TurnoverByYearOrgLevel1"."OrgLevel1Code" in (SELECT "Cognos_TurnoverByMonthOrgLevel1"."OrgLevel1Code" FROM "Ultimate" WHERE 'dummy'=@{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?
0 -
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'=@'{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.
0 -
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'=@'{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'=@'{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.
0 -
Sorry,I have to step out now. I will try to replicate it in my environment tonight, and reply to you.
Thanks
Arijit
0 -
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
0 -
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!
0 -
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
0 -
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!
0