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)
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
Pinned topic WEek1,week2,week3 data required in Report
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-04T14:15:08Z at 2013-02-04T14:15:08Z by DanielWagemann
DanielWagemann 2700058MAJ850 Posts
Re: WEek1,week2,week3 data required in Report2013-02-04T14:15:08ZThis is the accepted answer. This is the accepted answer.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.