I'm getting the error when using 2 different Time dimension in the IF statement.
Please help with the syntax how I can make the ID statement to work. Thank you
Selected Month Data Item:
IF ( ?pCalendarType? = 'FISCAL' )
THEN ([Sales Cube].[Fiscal Time].[Fiscal Time].[Month]->?pSelMonth?)
ELSE ( [Sales Cube].[Seasonal Time].[Seasonal Time].[Month]->?pSeasonalMonth?)
try this:
member(
IF ( ?pCalendarType? = 'FISCAL' )
THEN ([Sales Cube].[Fiscal Time].[Fiscal Time].[Month]->?pSelMonth?)
ELSE ( [Sales Cube].[Seasonal Time].[Seasonal Time].[Month]->?pSeasonalMonth?)
;'month';'month')
but just to give you a heads up, the output may not be what you are expecting
Idealy you should avoid if statements when using cubes.
Have you considered using a token prompt to do what you want?
I'm curious why you have different prompts for ?pSelMonth? and ?pSeasonalMonth?
I would think that they could just be a single prompt but the contents would vary depending on what calendar type was selected.
Thank you for replying. I am very new to COGNOS. So far I have created simple reports only in Report Studio.
I created 3 prompts
1. Calendar Type (values include FISCAL and SEASONAL)
2. Fiscal Month (contains months from Fiscal Dimension)
3. Seasonal Month (contains months from Seasonal Dimension)
I am using Render Variable to display the right month prompt based on Calendar Type. This works so far.
But If possible, I want to maintain 1 Cross tab report and not create 2 cross tabs and use Render variable.
That is why I want to be able to get the IF ELSE statement to work. I have data item using parallel period function so I need to use IF ELSE also to point to the right Time dimension.
Please suggest if there's a better way to make this work.
Thank you