Hello again Cognos wizards,
I am trying to put together a Year Over year Revenue report and have no idea the best way to do this.
I am hoping for a result like this:
JAN2011 JAN2012 JANYOY% FEB2011 FEB2012 FEBYOY% etc
Group name1
Group name 2
It would be nice if I did not have to change anything going forward - basically so that it always compares current year to previous year
Thank you again for your assistance
Bret
I simply way is to create data items for each month and year
for example for jan 2011
if date =201101 then Revenue
else 0
for jan2012
if date=2012 then Revenue
else 0
and so on
You would need extract(year,current_date) to make sure you dynamically establish the buckets and then use dynamic list header items (not labels) to make sure that you show the correct month/year combo (otherwise you need to change the list header values each year..)
that is good news blom0344. Is there a way to extract day and month so it would return Jan-Dec for 2011 and 2012?
It depends from your database
for exapmple in sql you can use MONTH ( date ) and YEAR ( date )
awesome. I now have it so it shows Jan 2011 Jan 2012 Feb 2011 Feb 2012 etc - which is what I want
Now I am wondering if I can hook a prompt to it so when the manager selects May for example - it only shows May 2011 May 2012 instead of the whole 2 years
Sure , simply define a prompt with 12 static values :
1 Jan
2 Feb
.. ....
5 May
.... ....
12 Dec
Then take the associated parameter of the prompt to filter the data query:
extract(month,[somedate]) = ?parameter? and extract(year ,[somedate]) between
extract(year ,[current_date]) - 1 and extract(year ,[current_date])
thank you so much