3 Replies Latest reply on Apr 18, 2017 2:26 AM by Sherry George

    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.

        • 1. Re: Repository variable not working in SQL results of dashboard 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

          • 2. Re: Repository variable not working in SQL results of dashboard prompt
            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"

            • 3. Re: Repository variable not working in SQL results of dashboard prompt
              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"