mdx escape character CDE Pentaho

Question

I have seen some questions about MDX escape character, but they focus on a query that has a value specified. I have a dynamic mdx, with values that will be specified by the user. This is the MDX:

    SELECT
    NON EMPTY {Hierarchize({[Measures].[INDICADOR]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[NOW].[NOW].Members})} ON ROWS
    FROM [UNION_NOW]
    WHERE CrossJoin({[PARENT].[${parentParameter}]}

the wild card is based on my selector:

    SELECT
    NON EMPTY {Hierarchize({[NOW].[NOW].Members})} ON COLUMNS,
    NON EMPTY {Hierarchize({[PARENT].[PARENT].Members})} ON ROWS
    FROM [UNION_NOW]

How do I apply the "escape value" in this case?

Answer 1

The best way to solve will be to use the PRE/POST execution events of selection component depending upon which CDE components are you using.

For similar situation

• I used a select component which was bringing data from a SQL query
• Based on user selection, I used the pre execution event of Query Component to generate a where clause and set that where clause in a "simple parameter" component
• and finally in the MDX Query used that simple parameter to join the dynamic where clause.

You can also use the post execution of select component to generate a dynamic where clause and set it in a parameter, and then use the parameter in your MDX/SQL query

Hope it helps.