Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Repository variable not working in SQL results of dashboard prompt

Received Response
26
Views
3
Comments
Debraj Roy
Debraj Roy Rank 1 - Community Starter
edited Aug 13, 2024 6:03PM in Oracle Analytics Cloud and Server

Hi,

I am using the below SQL statement in one of my dashboard prompts. The condition being used is as below -

SELECT  

case 

when '@{PV_Time}'  in ('Yesterday','WTD','MTD','QTD','HTD','YTD') then VALUEOF("RMA_dv_r_Yesterday")

else 1

end

RMA_dv_r_Yesterday is the name of the repository variable being used - It works in Edit formula or any other narrative text. But this does not work in the SQL results of the prompt.

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Hi Debraj,

    Please check your variable syntax. Try @{biServer.variables.variablename} for referencing repository variables. 

    https://docs.oracle.com/middleware/1221/biee/BIEUG/analyses.htm#BIEUG2795

  • Debraj Roy
    Debraj Roy Rank 1 - Community Starter

    Hi Sherry,

    I tried with all the variable synatxes,but it is not working.

    below is the SQL which I am giving to populate the prompt - PV_Header

    SELECT   

    case  

    when '@{PV_Time}' in ('Yesterday','WTD','MTD','QTD','HTD','YTD') then VALUEOF("RMA_dv_r_Yesterday")

    when '@{PV_Time}' = 'Last Week' and "Daily Sales Markdown Highlights"."Fiscal Week ID"= valueof("RMA_dv_r_Elapsed_Wk") then "Daily Sales Markdown Highlights"."Fiscal Week"   

    when '@{PV_Time}' = 'Last Month' and "Daily Sales Markdown Highlights"."Fiscal Period ID" =   valueof("PriorMonth_wid") and ("Daily Sales Markdown Highlights"."Time Level Code")='MTH'  then "Daily Sales Markdown Highlights"."Fiscal Period"   

    when '@{PV_Time}' = 'Custom Future' and "Daily Sales Markdown Highlights"."Fiscal Period" =  '@{PV_FMth}{SEP-16}'  then "Daily Sales Markdown Highlights"."Fiscal Period"   

    when '@{PV_Time}' = 'Last Year' and "Daily Sales Markdown Highlights"."Fiscal Year" = valueof("RMA_dv_r_LastYear") then "Daily Sales Markdown Highlights"."Fiscal Year"   

    when '@{PV_Time}' = 'LLY' and "Daily Sales Markdown Highlights"."Fiscal Year" = valueof("RMA_dv_r_CurrentYear-2") then "Daily Sales Markdown Highlights"."Fiscal Year"   

    end  

    FROM "RMA – As Is – Time and Custom"

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    ok, if you create a report using simple logical sql below, are you getting the value for the repository variable ?

    SELECT CASE WHEN 'WTD' IN ('Yesterday','WTD','MTD','QTD','HTD','YTD') THEN VALUEOF("RMA_dv_r_Yesterday") END FROM "RMA – As Is – Time and Custom"