OBIEE – Report Selection Prompt IGATE Interview - OBIEE GURU

govtjobonline-Latest Government Jobs 2023 Recruitment Notifications

Latest Govt Jobs Whatsapp Group Links-Telegram

For Latest Govt Jobs 2023 or for Freejobalerts or Join

Latest Government Jobs 2023

Saturday 14 January 2012

OBIEE – Report Selection Prompt IGATE Interview


    I was asked yesterday how to create View Selector type functionality that will allow the user to select between reports, rather than just views of the same report. I have a feeling this information must already be available, but I said that I’d provide instructions on how to achieve this and may as well add it to my blog. 
    I will go through the steps with screenshots. We want to create 2 reports, Option 1 and Option 2; we need to create a third report that will return either true or false (results or no results). Both reports, Option 1 and Option 2 will be placed on the Dashboard, both in their own Sections; and both sections with Guided Navigation making use of the conditional request. We create a Dashboard prompt giving the options of Option 1 and Option 2; and add a filter to the conditional report so that it filters by the value selected in the prompt. Essentially if Option 1 is selected in the prompt then Report 1 will be displayed; and if Option 2 is selected then Report 2 will be displayed. Reading through this sounds very complex, but it isn’t really – if you haven’t understood then follow the steps below. 
    Create the Dashboard Prompt 
    The Dashboard Prompt will display a list of the Reports available; we can enter anything that we like for each option.  To achieve this we must use SQL to generate the Show Values.  OBI forces us to select an existing column to populate the prompt, but we get around this by using the expresssion CASE WHEN 1=2; the expression will never result to true and will always show the result of the else statement, our option.  We create a SQL statement for each option we would like in the Prompt and union the statements together (in the order that we would like them to appear). 
    Note: The Column used should match the values you would like to generate 
    Prompt Show SQL
    Prompt Show SQL
    For the SQL above, obviously change the references to “column”, “table” and “Business Model”.  Once happy with your SQL I would usually select the Preview Button to check my code. 
    Test Prompt Show SQL
    Test Prompt Show SQL
    For this functionality you would usually not want an ‘All Choices’ option; uncheck its inclusion.  We should also choose to default to a Specific Value to restrict the list to only valid options.  Click on the elipses button and type your preferred default value from the last - no need for quotes, just the text itself.  I would usually verify that the default value is working by viewing the preview again. 
    Updated Prompt
    Updated Prompt
    The only remaining task is to populate a Presentation Variable using the Set Presentation Variable Drop Down.  I have created a variable pVar_ViewOption.  You will also probably want to relabel the prompt to something more meaningful and then you can save it. 
    Set Presentation Variable
    Set Presentation Variable
    Create a Conditional Report 
    We need to create a conditional report; the report will filter by the presentation variable created.  We will design the report to return values when one Prompt option is selected and return no values otherwise.  Essentially the report will return true or false, based upon the option selected in the prompt. 
    We need only a single column in the report; which should be the column referred to by the prompt.  Similar to the expression used in the prompt, we use a CASE statement to always return the relevant option; in this case the preffered option.
    Conditional Column Expression
    Conditional Column Expression
    We now add the filter to the conditional report to filter this column by the Presentation Variable.   If the option displayed by the column is selected then the report returns results; otherwise it returns no results.
    Conditional Filter
    Conditional Filter
    The Conditional Report is complete.  We also need to create the reports to be displayed with each option.  For this example I’ve created one for each of the 2 options.  So thats 3 reports in total and a prompt.  The screenshot below shows these object in My Folder.
    My Folder
    My Folder
    Configure the condition on a Dashboard
    Create a section for the prompt and another for each report. 
    Dashboard Layout
    Dashboard Layout
    We need to use Guided Navigation on each of the Report Sections; we will only show each section based upon the results returned by the Conditional Request we have created.  The Guided Navigation setup for Report Option 1 is given below; notice that it returns a report on the condition the conditional report returns results (ie the prompt selection is our preferred option).
    Report Option 1 Guided Navigation
    Report Option 1 Guided Navigation
    Report 1 - Conditional Navigation
    Report 1 - Conditional Navigation
    We need to set Guided Navigation for the second report to show when our conditional report returns no results, as below.
    Report 2 - Conditional Navigation
    Report 2 - Conditional Navigation
    And thats the process complete.  When we save our Dashboard and view the page our prompt is shown and defaults to the preferred option; the guided navigation kicks in to display the first report and not the second.
    Test One
    Test One
    And then when we select the other option in our prompt the guided navigation kicks in to display only Report Option 2 section, and not our other Report Section.
    Report Two Test
    Report Two Test
    It is worth noting that behind the scenes all three reports will be ran by the BI Server. I wouldn’t be concerned unless the approach causes too much load on the server.  However, this approach should not be used to improve performance; it doesn’t work like that unfortunately.

    Whatsapp group

    Govt Jobs Whatsapp Group Links 2023

    Telegram group

    Govt Jobs Telegram Group

    Facebook group

    Govt Job Online Facebook Group

    Follow on Facebook group

    Govt Jobs Online Facebook Page

    Subscribe to Youtube Channel

    Govtjobsonline Youtube

    Join Latest Govt Jobs Telegram Group here

    Join Government Jobs Whatsapp Group here (State Wise)

    Join State Wise Sarkari Results Telegram here

    1. ITI Jobs Notifications
    2. ITI Apprenticeship Program
    3. Diploma Jobs Notifications
    4. Engineering Jobs Notifications
    5. UPSC Jobs Notifications
    6. Court Jobs Notifications
    7. Staff Nurse Jobs Notifications
    8. NHM Jobs Notifications
    9. Exam Syllabus and Pattern
    10. SSC Recruitment

    No comments:

    Post a Comment

    Latest Govt Jobs Vacancy 2023