Different Measure calcs based on period

[mickyo73]

Hi All,

I'm new to the boards and cognos so go easy on me! :)

I have a requirement to create a report based off a cube that the user enters a particular period and the report will show x no of periods prior to that period and y no of periods after the period selected. I've been able to create this using a value prompt (?pFrom?) and using a combination of union and lastperiods functions.

So far so good. Now for the bit I can't seem to get my head around. The users need to show Actuals for past periods the (ie lastperiods(8,[Proj Cube].[Proj Period].[Fin Month].lag(1)) columns, actual plus forecast for the period selected in the value prompt (?pFrom?) parameter  and Forecasts for future periods (ie lastperiods(-12,[Proj Cube].[Proj Period].[Fin Month]).  I have 2 separate measures in the cube ie Actuals and Forecasts that I can use for the calculcation.

I've been attempting to write a calculated measure using a case statement but have hit a brick wall.  I can't work out how to write the case so the 3 calculations correct for each period (columns on the report).

is it something like

CASE ?pFrom?
  WHEN lastperiods(8,[Proj Cube].[Proj Period].[Fin Month].lag(1))  THEN [Actuals]
  WHEN lastperiods(-12,[Proj Cube].[Proj Period].[Fin Month].lag(-1)) THEN [Forecasts] ELSE [Actuals] + [Forecasts] END?? 

Nothing is being returned.

Any ideas would be greatly appreciated.

Thanks
Mick

[Lynn]

Sounds to me like you are off to a good start!

You can't use relational constructs against a cube, so the case statement is a no-no.

Instead of doing a union to put your prior, current and future periods together you can just stack them one next to the other. Then drag the actual measure beneath the prior set. Then create a calculation that is actual plus forecast and use this nested beneath the current and future period sets.

[CognosPaul]

There are many ways of accomplishing this. But first, I have to understand what exactly the pFrom is returning.

Do you have a value prompt in which the static values are lastperiods(8,[Proj Cube].[Proj Period].[Fin Month].lag(1)), lastperiods(-12,[Proj Cube].[Proj Period].[Fin Month].lag(-1)), and something else? If you do, then I imagine you have a data item in your crosstab that looks something like:

Code Select Expand

#prompt('pFrom','token')#

You can make a few changes.
First, put the two lastPeriod expressions into their own data items. Call it LastPeriods8 and LastPeriods-12.
Next, change the static values of the prompt to LastPeriods8 and LastPeriods-12.
In the data item that's being used on the crosstab, set that to #sb(prompt('pFrom','token'))#

That will give the same result as you have now.

Now for the measure, create two new data items, LastPeriods8Measure and LastPeriods-12Measure.
In the data item you're using on the page, set the expression to #sb(prompt('pFrom','token')+'Measure')#