This discussion is archived
10 Replies Latest reply: May 13, 2013 10:08 AM by Yala RSS

Navigate from report to dashboard and  pass values between different column

Yala Newbie
Currently Being Moderated
Hello

I have a question about navigating from 1 report to a dashboard page while passing the value from column 1 to column 2 in the second report...

In OBIEE 11G, I create action link on report 1, column 1 and this action link is navigate to BI Content and the destination is a dashboard page, which has column 2 used as dashboard prompt and there are 3 reports all use column 2 to filter. Now the column 2 in the dashboard page is an alias of column 1 from report 1, from user's point of view they are the same, but from OBIEE point of view they are different.

My action link is able to navigate to dashboard, however, the value in column 1 which I clicked to execute the navigation, does not get passed to column 2 as the dashboard prompt doesn't get this value upon navigation..

Since I can't put column 1 in dashboard 2 nor can I put column 2 in report 1. Because our data model is done in the way that if I use column 1 or column 2 in both places, it will be circular joins...

Is there a way around this issue?

Let me know if I need to provide more clarification

Thanks
  • 1. Re: Navigate from report to dashboard and  pass values between different column
    Yala Newbie
    Currently Being Moderated
    Just a note:

    This issue may appear to be the same issue as below:

    Navigate between 2 reports and pass values between 2 different columns

    But ohh no, it is not.. I wish it were so that my life would've been a lot easier, but sadly it is not.. Entonces, I would like to have some help on this one..

    Thank you
  • 2. Re: Navigate from report to dashboard and  pass values between different column
    Sid_Middleware Explorer
    Currently Being Moderated
    Values will be passed if <presentation table>.<presentation column> is same between both reports.

    Your constraint did not make any sense to me. If they are same columns why can not you use one column in both the reports? Do they come from two different physical tables in two different physical databases? In that case, it is still good to have one logical and presentation column while merging both tables using fragmentation.

    You can create two subject areas. In one, bring in column1 and in another, bring in column 2. Make sure that presentation table/folder and column has same name in both subject areas. Now create two reports based on two subject areas. Drill down should work.
  • 3. Re: Navigate from report to dashboard and  pass values between different column
    Yala Newbie
    Currently Being Moderated
    Sid

    Here is the thing..

    Let's say we have Dimension 1, dimension 2 and fact 1 and fact 2

    In one report, you need to show Dimension 2 and fact 1 so Dimension 2 joins to fact 1.

    In some other cases, you need to shown dimension 1 and fact 2 stuffs, but dimension 1 can't get to fact 2 without going through dimension 2. So Dim 1 --> Dim 2 --> Fact 2

    Then in some other cases, you need to show Dim 1 and Fact 1 reports..

    In means, your model will look at:

    Dim1 --> DIm2 and Dim 1 --> Fact 1 and Dim 2 --> Fact 1.. This is just a simpler version of a much more complex model that I am dealing with now..

    So in this case, you can't just use Dim 1 attributes across the board. For some dashboards, you will have to create alias of Dim 1, call it A1_Dim1, un-join Dim1 with Dim 2 or to fact 1 to avoid circular joins... Then in different reports, you either use columns from Dim 1 or A1_Dim1..

    Now Dim1 and A1_DIM1 are really the same thing, but they are not according to OBIEE...

    So my problem is, in report 1 where Dim1 is used, it can't past values to Dashboard 2 where A1_DIM1 is used as dashboard prompt..

    I want to know if there is a way around this issue

    Hope this makes more sense now

    Thanks
  • 4. Re: Navigate from report to dashboard and  pass values between different column
    Sid_Middleware Explorer
    Currently Being Moderated
    Ok, but having same table&column names in two different subject areas resolved the issue?
  • 5. Re: Navigate from report to dashboard and  pass values between different column
    Yala Newbie
    Currently Being Moderated
    I only have 1 subject area where I have numbers of fact and dimensions..

    I have in this case, 2 Alias of the same Dimension..

    So the presentation Layer, we have Column 1 in folder 1 and Column 2 in folder 2.. But physically, they are the same column in the DB..


    I'd like to navigate between these 2 columns from report to dashboard page
  • 6. Re: Navigate from report to dashboard and  pass values between different column
    gauravnankar Explorer
    Currently Being Moderated
    Hi Lao,

    I guess what Sid is trying to say is have your two columns with the same 'TableName'.'ColumnName' names. You can do this by splitting your current subject area into 2 and renaming the tables.

    I gave your requirement a thought and could not find any way to get the Dashboard prompt values prompted unless the two columns have the same 'TableName'.'ColumnName' values in their names. So, if it is unavoidably necessary for you to get this done, you can try splitting the subject areas. It may require a lot of work, but there is no other way :(

    Thanks,
    Gaurav
  • 7. Re: Navigate from report to dashboard and  pass values between different column
    Anirban1 Explorer
    Currently Being Moderated
    Hi,

    According to my understanding, you have column1 and column1_Alias. Now you have one report with column1 in page1. You want to click on column1 values and it will navigate to a dashboard page say page2. There you have another report with column1_Alias and one prompt with Column1_Alias.

    1> Create one prompt with column1, put one presentation variable say column1.
    2> Create one prompt with column1_Alias column. Put column formula as Column1_Alias, in choice list values -- select sql result and put select column1_alias from table where column1_alias=@{Column1}.[presentation variable].
    3> Create the report with column1_alias and put column1_alias is prompted.
    4> Now in which page you want to navigate put the report with column1_alias, Column1_Alias Prompt and in different section put Column1 Prompt and hide this section.

    Please let me know whether it resolves your issue.

    Thanks
    Anirban
  • 8. Re: Navigate from report to dashboard and  pass values between different column
    Yala Newbie
    Currently Being Moderated
    Hello Anirban

    Your suggestion is very good, I like it..

    My question here is, what if I have more than 1 Alias..

    In other words, I have Column1, Column2, Column3 all are alias of ColumnX.

    Now ColumnX is the dashboard prompt in dashboardX..

    Dashboard 1, Dashboard 2, Dashboard 3 uses reports of Column1, Column2, Column3 respectively, each need to be hyperlinked and navigate to Dashboard X..

    In this case, if I take your suggestion to the next level, should I create 3 more prompts (set 3 Presentation Variables, var1, var2, var3) in Dashboard X and hide them all? Then should i modify the column X prompt by editting the SQL default display with something like:

    Select from X where x = var1 or X = var2 or X = var3?
  • 9. Re: Navigate from report to dashboard and  pass values between different column
    Anirban1 Explorer
    Currently Being Moderated
    Hi,

    You will be having three main dashboard page (i.e. dashboad1,2,3) in those dashboard you will be having 3 different report with different column alias (i.e. columnx_alias1,columnx_alias2,columnx_alias3) and you will having one more dashboard page say dashboardx with a report with columnx. Now if you click on dashboard1 report it should go to dashboardx, click on dashboard2 report also should go to dashboardx, click dashboard3 report also should go to dashboardx.

    1> You can create 3 presentation variable for 3 alias column and hide them. You can use union query in sql result say select columnx from table where columnx='@{column1}' union select columnx from table where columnx='@{column2}' union select columnx from table where columnx='@{column3}'.
    2> Now you can hide those prompts with variables.


    Please let me know if you are facing any issue.

    Thanks
    Anirban
  • 10. Re: Navigate from report to dashboard and  pass values between different column
    Yala Newbie
    Currently Being Moderated
    Thanks Anirban

    I think your approach will work.

    However, I should note that this is under the condition that Column Alias 1 and Column Alias 2 are from different dashboards navigating to Alias 3 in Dashboard 3..

    When Alias 1 and Alias 2 are part of the same report in Dashboard 1, this approach will have some problems..

    Thanks for the inspiration!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points