OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ? - 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

Friday 27 January 2012

OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?


    In the repository

    The session variable

    In the administration tool :
    • go to Manage/Variable in the menu
    • the variable manager open
    • click on the node : Session/Variables/Non System
    • and create a session variable MyYear with for instance this default initialization string for the oracle database
    SELECT '1995' FROM dual

    Setting the session variable

    In the logical table source

    In the logical table source TIMES Fact from the logical fact table Times, let's fill in the where clause with the repository session variable.
    "orcl SH"."".SH."TIMES Fact".CALENDAR_YEAR =  VALUEOF(NQ_SESSION."MyYear")

    In a physical table of type select

    In the BI Presentation Service

    In BI Presentation service, create :
    • a dashboard prompt
    • and a report
    and add them into a dashboard.

    The dashboard prompt

    Create a dashboard prompt with the following characteristics :
    • Column : Calendar.”Calendar Year”
    • Control : Edit Box
    • Set Variable : Request Variable and the value MyYear

    The report

    Select only the column Calendar.”Calendar Year”.

    The dashboard

    To see the value of the server variable, I have had a text box dashboard below the dashboard prompt with this code :
    Session Variable My Year : @{biServer.VARIABLES['MyYear']} 

    The result

    Then when you set for instance the dashboard prompt to 1996 and hit the go button, you will retrieve this result :
    The value of the repository session variable is not changed. The new value is only valuable for the query. It's why you can't see in the session manager a new value for the session.
    The obiee logical sql request doesn't contain any filter :
    SET VARIABLE MyYear='1996';SELECT Calendar."Calendar Year" saw_0 FROM SH ORDER BY saw_0
    The logical request doesn't contain any filter :
    -------------------- Logical Request (before navigation):
    
    RqList  distinct 
        Times.Calendar Year as c1 GB
    OrderBy: c1 asc
    But the final query, send in the database, contains one.
    -------------------- Sending query to database named orcl SH (id: <<103865>>):
    
    select distinct T3883.CALENDAR_YEAR as c1
    from 
         SH.TIMES T3883 /* TIMES Fact */ 
    where  ( T3883.CALENDAR_YEAR = '1996' ) 
    order by c1

    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