Hi All!
I need help. I have been at this report for days and I am coming down to the wire and decided to turn to you all for help!!
To illustrate my issue, I recreated the issue using the Go Data Warehouse Cognos sample package. I understand using an olap source would be preferable for this type of analysis but we just don't have that at this time.
Anyone have a solution on how I can dynamically calculate the change over the year chosen in the prompt vs. it's prior year?
Much appreciation to a solution!!! OR even a bit of guidance!!!
Try setting your filter to:
[Year] in (?Year?,?Year?-1)
And your measures to (for example):
Sales Current Year:
case when [Year] = ?Year? then [NS].[Measures].[Sales] else 0 end
Sales Previous Year:
case when [Year] = ?Year? - 1 then [NS].[Measures].[Sales] else 0 end
Variance (with solve order set to 2, and aggregation to calculated):
[Sales Current Year] / [Sales Previous Year] - 1
There are a number of other ways to do it, you could try setting up a DMR solution for example. This way is the easiest to understand though.
Thank you Paul!! I will try that and let you know how I made out.