I have created the following macro prompt in Cognos Report Studio 10.2 to grab a 'Officer Employee 2011 Jan' dimension that will vary in the date that is appended to the end. I have versioned dimensions in a 2008 SSAS cube of the same Officer Hierarchy where a new version of the hierarchy is created each month. I would like the user in a report, to be able to select a month, (from a date value prompt drop down, and when they select the month, this macro i am building will subsitute the '?DateSelection? part of the macro with the correct dated dimension. the dimensions are dynamicaly created with the name as above, but the date will vary with the month. If i were to manually in the macro, replace ?DateSelection? with say '2011 Jan', it will pull up the correct dimension. Can someone help me to alter this macro to do the same dynamicaly based on the ?DateSelection? prompt i included in this macro? Thank you!
#prompt ('Officer', 'token', '[CPDM].[Officer Employee ' +
'?DateSelection?' + '].[Officer Employee Hierarchy]')#
The following expression worked for me on SSAS 2008 GOSalesFact_EN_MSAS2005 package:
#prompt('Orgcode','token','[GO Sales Fact].[Organization].[Organization].[Organization
Code1]','[GO Sales Fact].[Organization].[Organization].[Organization C','',']')#
So, I guess equivalent expression for would be:
#prompt ('Officer', 'token', '[CPDM].[Officer Employee].[Officer Employee 2011 Jan]',
'[CPDM].[Officer Employee].[Officer Employee 2011 ','',']')#
The Jan hierarchy is the default hierarchy in case you did not choose anything.
Now all you have to do is create a value prompt refer to the Officer prompt and put in a list of months ('jan','feb',...).
The prompt macro refrence of Cognos 10.2 is located here (Framework manager):
Creating prompts with query macros
and here (Report Studio):
Creating Prompts Using Query Macros