How to swap dimensions using string manipulation?

[oscarca]

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

[CognosPaul]

It's the dot that's causing the bad search expression. substitute uses a weird regex implementation, so you need to escape special characters:

Code Select Expand

#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.

[oscarca]

Thanks Paul! Changing to macro prompt and handling the dot did the trick!