Tuesday, January 17, 2012

OBIEE 11g - Using a Dashboard Prompt as a Column Selector

Yesterday, I was looking for a solution for creating a column selector that would work for an entire dashboard page, without the objects on the page being part of the same compound view.  I found this link on Business Intelligence Consulting Group site that looked promising ( BI Consulting Group ).  However, with some Prompt functionality in OBIEE, I was able to do it using a simpler method that does not require any updates to the RPD.  


Create Variable Prompt
1.  First, instead of using the method from the link above where they start by creating a member in the RPD, I decided to bypass this by using the standard functionality within a Variable Prompt.  I decided to populate the variable 'SELECT_COLUMNS' with a variable prompt, and added custom values that will correspond to Presentation Column names in the RPD.  
Add your Presentation Column names in the 'Choice List Values', just as they are in the RPD


Example of Presentation Columns in RPD that match the custom
values in the Variable Prompt



2.  Now, I update my OBIEE query to have the Presentation Variable as part of the column name.  Now, when I put these together in a dashboard, the Presentation Variable allows me to select the column that I want to show in the dashboard.  Like I said, a column selector will do the same thing if you only have one query/view.  However, if you want to update more than one compound view, a column selector isn't really going to allow you to update all views with the same column selector. This approach allows you to do this without any RPD development.


Now, I add my Presentation Variable 'SELECT_COLUMNS' in the column formula to allow for the Variable Prompt to drive the Presentation Column that will be displayed in my dashboard.






Posted by at

25 comments:

  1. AnonymousApril 16, 2012 at 2:43 PM

    Hi there,
    Does this help in selecting columns to view on the dashboard without having to click on edit or analyze and selecting or deleting columns from the answers section?

    ReplyDelete
  2. DMurphApril 17, 2012 at 9:37 AM

    Absolutely. Selecting multiple 'fact' columns in a query is one of the things I was trying to avoid when doing this. Allows you to select facts using a drop-down.

    ReplyDelete
  3. Hari PrasadMay 2, 2012 at 2:10 AM

    Hi,

    One question. I want 5 different image prompts. I want a dropdown box to select prompts. I am facing to have this headache because in 11.1.1.5 image prompt with page break, when we click will simply go to next page prompt rather applying prompt. It will only apply last page prompt. Any help? :-)

    ReplyDelete
    Replies
    1. DMurphMay 9, 2012 at 10:24 AM

      HP, I'm not sure I understand. Can you maybe clarify with examples?

      Delete
  4. AnonymousMay 15, 2012 at 12:31 PM

    Hi Murph,
    Great work. I am pretty sure this will solve my problem but I am not exactly sure how to implement this in my scenario. I have a Language prompt on my dashboard with English, Spanish and Mandarin as the options. When the report is run first 3 columns in the report display data in English (the language prompt is defaulted to English). Now, when the language is changed to Spanish the three columns need to be replace by 3 other columns that hold data in spanish. Similarly, when Mandarin is selected mandarin columns need to be displayed. Is there any way I can implement your method in my senario? Let me know if you need any more information.
    Thanks a lot.

    ReplyDelete
    Replies
    1. DMurphMay 21, 2012 at 9:20 AM

      Sorry for the slow response, but I was out of town at a conference all last week. So, if I understand this correctly, you want a variable for the table name, and not the column name. Are the column names the same, but under different table names? If so, you could create the variable to be the table name and reference the variable in place of the table name. If not, you could create three new tables in your RPD in the Presentation section, one named 'English', one named 'Spanish' and one named 'Mandarin'. Each of these Presentation tables would have the columns you need to display in it (with the same column names for the corresponding fields for each language), and reverse what I've done above, using the variable for the table name instead of the column name. Hope this helps.

      Delete
    2. AnonymousMay 30, 2012 at 7:14 PM

      Sorry for not being clear enough. To be more specific, can I use one Variable Prompt to replace two different columns on the same report? I have implemented your method successfully to replace one column X, between X,X1 and X2 (X, X1 and X2 belong to table A). But I want to be able to replace another column Y between Y, Y1 and Y2 (Y, Y1 and Y2 belong to table B) using the same variable prompt.

      Delete
    3. AnonymousMay 31, 2012 at 1:20 PM

      I figured it out. Thanks again Murph.You were of great help.

      Delete
    4. DMurphJune 1, 2012 at 12:17 PM

      Thanks. I'm glad you were able to get it to work.

      Delete
  5. MSK Wealth CreationJune 21, 2012 at 9:45 AM

    DMurph, this is Great work.
    Can we extend your solution to enable Multiple Values in the Prompt Selection, so that the Report should dynamically change the No.of Columns in the Table view as well.

    ReplyDelete
    Replies
    1. DMurphJune 28, 2012 at 4:01 PM

      Kaushik, I don't think that will work with the solution the way I presented it. It is an interesting idea. If I find a way to make this behavior happen, I will add it to my blog. Thanks.

      Delete
  6. AnonymousAugust 3, 2012 at 8:41 AM

    I have two talbes coming from two different DB identical tables. Both of them have a Status column on which I want to add a single Prompt. When this prompt gets selected, I want to filter both the table simultaneously to show whatever is selected..
    Is this possible? If yes, how can I achive it ?

    Rgrds,
    Giridhar

    ReplyDelete
    Replies
    1. DMurphAugust 9, 2012 at 9:35 AM

      Giridhar, are both tables in the same data model or separate data models? If separate data models, as long as they share the same table and field names in the Presentation model, a prompt will end up filtering both of them. I do this when I have multiple related data models (different level of detail), but I want it to function as if it is one data model, without having to combine them. If they are in the same data model, another option is to create a presentation variable. You can have the prompt generate the result for the presentation variable, and reference that presentation variable in the filter of both tables.

      Delete
  7. JinJin3388November 20, 2012 at 10:30 AM

    I'm following your instructions but still not working.

    ReplyDelete
  8. JinJin3388November 20, 2012 at 12:26 PM

    I got an error saying "Scenario: FCST_WIP is not a valid number", I have 2 columns FSCT_WIP and Q2 FCST under the Scenario.

    ReplyDelete
    Replies
    1. DMurphDecember 3, 2012 at 1:10 PM

      Jin, are both fields defined in the RPD as having an aggregation rule? Are they the same data type? In my Business Model, all of my fields I use for this example above, are all data type DOUBLE.

      Delete
  9. AnonymousFebruary 20, 2013 at 8:59 AM

    Hi,

    I am using Oracle obiee 11.1.1.5

    My requirement is to pass the Dashboard prompt value captured in presentation variable to KPI (Target value in Specific)
    I have tried to do so, but for some reason, the value from the dashboard prompt is not passed to KPI.

    1. Can I use the presentation variable in KPI? If Yes, can you please give an example?
    Thanks in advance
    NN

    ReplyDelete
    Replies
    1. DMurphFebruary 24, 2013 at 9:21 PM

      Hi NN,

      Sorry, but I have yet to delve into using KPIs in OBIEE. I'm probably not much help here.

      Delete
  10. AnonymousMarch 8, 2013 at 5:48 AM

    Hi!

    Great Solution!

    But is there any way to display colum labels in the choice the list?

    I want to switch between the columns "revenue" and "profit", but in RDP these are called "cdmrev_measure" and "cdmprof_measure". Needless to say that I can present a choice listy with these RDP column namnes to my users.

    Tom

    ReplyDelete
    Replies
    1. DMurphMarch 27, 2013 at 9:38 PM

      Tom, I typically rename the columns in the Presentation layer to be something more universal, like "Revenue" and "Profit". If you've already built around the "cdmrev_measure" and "cdmprof_measure", you can always create a duplicate column in the busines layer called 'Revenue' and 'Profit' respectively, move these to the Presentation layer, and then try it with those columns.

      Delete
  11. UnknownMay 29, 2014 at 7:53 AM

    Hi!

    I have a column in the report which contains description of a product(it's look like a paragraph), but i don't need to show whole description, instead of that i can show only starting 10 letters of that para only in the report.
    I tried using hower text but it won't work for me. but it shows few words (ex. 5 words) but not whole paragraph.

    could you please help on this(it's very urgent).
    (Env:windows 7,google chrome, bi11.7 )

    Thanks you,
    Balu

    ReplyDelete
    Replies
    1. DMurphJuly 1, 2014 at 2:58 PM

      Not sure if I understand the question. If you're looking to limit text and you're displaying a field as text, you could do this:

      SUBSTRING('@{SELECT_COLUMNS}{Total Charges}' FROM 1 FOR 10)

      It doesn't get you number of words, but does let you control the length of the field.

      If you're looking to display all of it, you could potentially size the header within 'column properties' under 'Additional Formatting Options' under the 'Style' tab.

      Delete
  12. UnknownMarch 17, 2015 at 7:19 AM

    we have included 3 different columns in variable prompt and created a variable prompt. I'm still confused when I select one column how the analysis will know that it need to fetch that column, please explain

    ReplyDelete
  13. AnonymousAugust 22, 2023 at 4:04 AM

    Gives you the best website address I know there alone you'll find how easy it is. Atlanta Pool Demolition

    ReplyDelete
  14. AnonymousOctober 4, 2024 at 3:48 PM

    Cool and that i have a neat proposal: Whole House Reno old home renovation

    ReplyDelete

Subscribe to: Post Comments (Atom)