Repository variable not working in SQL results of dashboard prompt

Debraj Roy

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.

Sherry George

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

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

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"