3 Replies Latest reply: Jan 13, 2013 3:06 AM by Jack Carver RSS

    DECODE in obiee

    451588
      Hi Guru's

      We got a requirement where by default we have to show calender year data, but when user selects fiscal year and he will be deselecting calender year we need to show fiscal year data.

      For above requirement we are using two prompts F_Year, C_Year and two presentation variables pv_fis_yr, pv_cal_yr to achieve above requirement we put the following in the Filters

      setting presentation variables as below

      F_Year --> pv_fis_yr
      C_Year --> pv_cal_yr

      (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR}, null,"Tab"."F_Year","Tab"."C_Year")
      IN (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR},null,@{PV_FIS_YR},@{PV_CAL_YR})))

      when we put above in the filters it is throwing syntax error, can you guys kindly help me in this.

      Thanks,
      Sumanth
        • 1. Re: DECODE in obiee
          Amith Y
          Sumanth wrote:
          Hi Guru's

          We got a requirement where by default we have to show calender year data, but when user selects fiscal year and he will be deselecting calender year we need to show fiscal year data.

          For above requirement we are using two prompts F_Year, C_Year and two presentation variables pv_fis_yr, pv_cal_yr to achieve above requirement we put the following in the Filters

          setting presentation variables as below

          F_Year --> pv_fis_yr
          C_Year --> pv_cal_yr

          (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR}, null,"Tab"."F_Year","Tab"."C_Year")
          IN (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR},null,@{PV_FIS_YR},@{PV_CAL_YR})))

          when we put above in the filters it is throwing syntax error, can you guys kindly help me in this.

          Thanks,
          Sumanth
          Instead of doing all this, why dont you set the Fiscal year and Calender year filters in the report equals to the value of presentation variable and default the calender year prompt to some value and leave the fiscal year prompt blank? That way, when the report is opened, it would get filtered on the calender year, and if user wishes to filter on fiscal year, he would then select fiscal year, and deselect calender year and run the report. I maybe wrong, but I think using Evaluate with decode is over complicating things a little bit?
          • 2. Re: DECODE in obiee
            451588
            Amith, that won't work out for the given requirement
            • 3. Re: DECODE in obiee
              Jack Carver
              Hi,
              instead of
              (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR}, null,"Tab"."F_Year","Tab"."C_Year")
              IN (EVALUATE('DECODE(%1,%2,%3,%4)' as char(4), @{PV_CAL_YR},null,@{PV_FIS_YR},@{PV_CAL_YR})))

              use:


              ( ( '@{PV_CAL_YR}{null}' = 'null' AND "Tab"."F_Year" IN '@{PV_FIS_YR}' )
              OR
              ( '@{PV_CAL_YR}{null}' != 'null' AND "Tab"."C_Year" IN '@{PV_CAL_YR}' ) )