• 1 reply
  • Latest Post - ‏2013-02-04T14:15:08Z by DanielWagemann
1959 Posts

Pinned topic WEek1,week2,week3 data required in Report

‏2013-02-02T08:08:34Z |
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
Updated on 2013-02-04T14:15:08Z at 2013-02-04T14:15:08Z by DanielWagemann
  • DanielWagemann
    850 Posts

    Re: WEek1,week2,week3 data required in Report

    Based on what you have posted, it sounds like you are trying to do DMR/OLAP style reporting against a relational package. The right answer is to switch to a DMR/OLAP style package.

    If you absoutely need to do this in Report Studio over a relational package and still have week over week comparisons...(aka weeks in their own column) you will need to do this via relationships.

    You would have 1 master query with all the records and 1 query for every week period you want to display. You would then create a relationship between these querries, being sure to adjust the relationship expression to offset the week period by 1. Something like:
    (WeekNo=WeekNo-1) for the previous week.

    I have done the above successfully however it is not easy and you may need to adjust your querries to account for any performance issues that arise.