WEek1,week2,week3 data required in Report

[barrysaab]

I have to show in my cognos report 3 weeks ago revenue, 2 weeks ago revenue and 1 week ago revenue,

and the corresponding back end database (Oracle 11i)
Table Details:
-------------------
Campaign_ID RunDate weekno week_startdate week_enddate Week_Revenue
------------------ ------------- ----------- ---------------------- --------------------- ----------------------
919 23-JAN-13 -1 13-JAN-13 19-JAN-13 40
919 23-JAN-13 -2 6-JAN-13 12-JAN-13 35
919 23-JAN-13 1 20-JAN-13 26-JAN-13 50
919 23-JAN-13 2 27-JAN-13 02-FEB-13 45


the above table weekno -1 means previous week for current Run_Date

These are the info available in my table
coming to the report side
i have to show columns like that
3Weeks Ago Revenue 2 WeeksAgo Revenue LastWeek Revenue.Appreciate your help.

[RKMI]

Hi Barry,

I'm guessing this report is against a relational model, if so then create a new data item with a case statement where weekno = -1 then ('1 week ago') and so on. Then create a crosstab with rows with campaign and colms as the new dataitem with the case statement, then add the measure under the data item. it should give you the revenue for the weeks prior. If the data is not sorted then click the crosstab and under grouping and sorting by the weekno colm ascending.

P.s. If you want to have this function on selected Rundate, just add the Run date as filter in your query. ie. 23-jan-13 or 13-jan-13,...

Thanks,
RK