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?

why is there a crossjoin in the WHERE clause of the first script? WHERE CrossJoin({[PARENT].[${parentParameter}]} — whytheq
that MDX was created by the SAIKU Wizard, I'm not sure why there is a crossjoin, I'm still trying to understand the tool. — Bea.C

user18620 user18620 · Accepted Answer · 2016-04-06T16:56:04

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.

mdx escape character CDE Pentaho

1 Answers