How to calculate revenue in a dimensional model with relative time functions?

[Gene]

Hi folks,

I searched on the net, but couldn't figure out how to build a relatively simple dimensional report that I need.

Calendar hierarchy:
Calendar -> Year (2010, 2011, ...) -> Quarter (2010 Q1, 2010 Q2, ...)

I need the following columns in each row of my report:
- Year
- Revenue for 1Q
- Revenue for 2Q

... there are other metrics, but I think I can figure these out with ParallePeriods, PeriodsToDate, and other functions

However at the moment I am stuck at the very beginning -- generating a simple reports with just these 3 columns.
I tried various forms of
"aggregate ([REVENUE] within set [Products], lastPeriods (1, currentMember([SDFDM cube].[Calendar].[Calendar]
))  ) "  however that sums up only annual revenue.

I figure it's something like "aggregate ([REVENUE] within set ..." but what?

Thanks in advance,

Gene

[cognostechie]

What do you need in the rows?

If you have a dimensional package, just drag the year level on the columns and then drag the same year level in the column again but under the 1st year level.
Cognos will automatically put the next level of the year (presumably quarter) under the year in the columns. You can also filter to exclude certain years of you don't want them.

If you want to make it more sophisticated then you can use either children or descendants functions against the dimension containing the years .