Forum Stats

  • 3,851,490 Users
  • 2,263,988 Discussions
  • 7,904,740 Comments

Discussions

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!

Tagged:

Best Answer

  • ArijitC
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 8:48PM Answer ✓

    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.

    pastedImage_0.pngpastedImage_1.png

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

    pastedImage_2.pngpastedImage_3.png

    Now here is the process….

    pastedImage_4.png

    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}' )

    pastedImage_5.png

    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.

    pastedImage_6.png

    Thanks

    Arijit

Answers

  • ArijitC
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 12:19PM

    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
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 12:23PM

    Also you can check if this option works

    pastedImage_0.png

  • 3294318
    3294318 Member Posts: 12
    edited Oct 23, 2017 1:11PM

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

    pastedImage_0.png

    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})

    pastedImage_1.png

    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})

    pastedImage_4.png

    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
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 1:35PM

    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
    3294318 Member Posts: 12
    edited Oct 23, 2017 2:28PM

    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.

    pastedImage_3.png

  • ArijitC
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 3:06PM

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

    Thanks

    Arijit

  • ArijitC
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 23, 2017 8:48PM Answer ✓

    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.

    pastedImage_0.pngpastedImage_1.png

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

    pastedImage_2.pngpastedImage_3.png

    Now here is the process….

    pastedImage_4.png

    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}' )

    pastedImage_5.png

    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.

    pastedImage_6.png

    Thanks

    Arijit

  • 3294318
    3294318 Member Posts: 12
    edited Oct 24, 2017 9:56AM

    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.

    pastedImage_2.png

    pastedImage_5.png

    Second prompt: After first one is selected

    pastedImage_6.png

    pastedImage_4.png

    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
    ArijitC Member Posts: 203 Blue Ribbon
    edited Oct 24, 2017 6:24PM

    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
    3294318 Member Posts: 12
    edited Oct 26, 2017 3:42PM

    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!

This discussion has been closed.