Hello Cognoise community,
I have report in Cognos 11 with two crosstabs and one prompt that uses two different cubes. What I want to accomplish is to sync the slicing/filtering using one Date dimension for both of them. So when you choose a date in the value prompt (based on the Date dimension from the "Sales Orders" cube) it should be able to filter the second crosstab that is using a Date dimension from the "Sales"
The MUN from the "Sales Orders" cube looks like this:
[Sales Orders].[Calendar Order Date].[Calendar Order Date.Month].[Month]->:[M14].[[Calendar Order Date]].[Month]].&[201811]]]
The MUN for the "Sales" cube looks like this:
[Sales].[Calendar].[Month].[Month]->:[M14].[[Calendar]].[Month]].&[201811]]]
This is what I have tried using the substitute function:
#substitute('Sales Orders','Sales',substitute('Calendar Order Date','Calendar',substitute('Calendar Order Date.Month','Month','[Sales Orders].[Calendar Order Date].[Calendar Order Date.Month].[Month] -> ?pPeriod?')))#
But I end up getting the error message : The macro functions contains a bad search pattern
Does anyone know how to solve this? All help, tips and ideas are greatly appreciated
It's the dot that's causing the bad search expression. substitute uses a weird regex implementation, so you need to escape special characters:
#substitute(
'Sales Orders'
, 'Sales'
, substitute(
'Calendar Order Date'
, 'Calendar'
, substitute(
'Calendar Order Date\.Month'
, 'Month'
, prompt ( 'pPeriod','mun' )
)
)
)#
I also prefer to use a macro prompt instead of the ? alias. I'm not sure how well this will work using aliases, I know the alias will expand into the macro at process time, but I don't know how the substitute will work with it.
Thanks Paul! Changing to macro prompt and handling the dot did the trick!