This discussion is archived
2 Replies Latest reply: Jan 14, 2013 6:10 AM by kart RSS

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

user8680861 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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.
  • 2. Re: from To date of current year to from to date year back
    kart Pro
    Currently Being Moderated
    Any updates?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points