2 Replies Latest reply: Jan 14, 2013 8:10 AM by kart RSS

    from To date of current year to from to date year back

    user8680861
      Hi we have a report in OBI 10g with a data prompt to choose From & Todate,based on which it will show the customers sale between those dates.

      Now we would like to also incorporate customers last years sale in the report between the chosen dates,

      for example

      from the prompt we have chosen from date as '01-dec-2012' and to date as '31-12-2012' so the report will be something like this

      CUST_CD | NAME | SALE(chosen dates)
      -------------------------------------------------------------
      1-1LQ-7 | XYZ | 1000

      now we would also like to display the sale for from date as '01-dec-2011' and to date as '31-12-2011'


      CUST_CD | NAME | SALE(chosen dates) |SALE(LY from date as '01-dec-2011' and to date as '31-12-2011')
      ---------------------------------------------------------------------------------------------------------------------------------------------------
      1-1LQ-7 | XYZ | 1000 | 50


      how can we achive this??

      Thanks in advanced
        • 1. Re: from To date of current year to from to date year back
          kart
          in your dashboard prompt have two presentation variables to store selected values. say from_date and to_date

          in your report do the following things
          1. apply a report filter like
          your_date_field between TIMESTAMPADD(SQL_TSI_MONTH, -12,cast('@{from_date}{01-dec-2012}' as date)) and cast('@{to_date}{31-dec-2012}' as date)
          2. In your SALE(chosen dates) field, apply the formula as given below
          filter(sales using your_date_field between cast('@{from_date}{01-dec-2012}' as date) and cast('@{to_date}{31-dec-2012}' as date))
          3. in your previous year field apply the formula as,
          filter(sales using your_date_field between TIMESTAMPADD(SQL_TSI_MONTH, -12,cast('@{from_date}{01-dec-2012}' as date)) and TIMESTAMPADD(SQL_TSI_MONTH, -12,cast('@{to_date}{31-dec-2012}' as date))

          let me know if the filter syntax is not working. i dont have obiee here.. so cant test it myself.