MDX problem: Source data in columns can contain square brackets ([ ]), how to stop these being interpreted as delimiters?

Question

I am working on a project that is using OLAP cubes, and we have a problem with an MDX statement.

The problem is that our source data has square brackets in. For example: Customer [customer code]

This is an example query on a specific customer, whose name is "CustomerA [123]". The measures list is insignificant and has been left out:

SELECT <measures> ON COLUMNS, 
[Customer_Dim].[All Customer_Dim].[CustomerA [123]] ON ROWS
FROM [cubename]

So when we submit this query for one specific customer, the MDX uses those square brackets as delimiters.

This causes an error, because it is reading the [123] and thinks there is a syntax error.

We want to protect the square brackets from the data from being read as delimiters. Is there a way to shield them, or are we forced to change the character to something else?

Thanks

Answer 1

You can have names with brackets in MDX, you'll have to use the escape character ] :

[Customer_Dim].[All Customer_Dim].[CustomerA [123]]] // note 3 ]]]

]] -> character ']', last for a 'classical' mdx bracket