passing excel variable into MDX

[JamiseBondi]

Hi guys,

I've used the following format in the past to get a variable in excel passed into an MDX statement successfully.

Code: Select all

="{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dimension1] )}, 0)}, ASC)} ,
[CC_Config].([Version].[&CHAR(34)&$F$15&CHAR(34)&],[CC].[HQ],[}ElementAttributes_Dimension1].[Currency])='USD'
) }"

Code: Select all

The above allows me to choose a version within the workbook (in cell F15) and send it into the MDX to filter for any config in a cube against the relevant version, where the currency is 'USD'. What I'd like to do is also pass the currency from the workbook into the MDX statement to allow me to choose any currency in the workbook and have the MDX filter according to the chosen currency.

So in the above example lets say I'm not hardcoding 'USD' into the MDX statement, I'd like to lookup the currency from cell G15 in the workbook.

I need the single inverted commas on either side of the currency. The workbook reference has the currency without the single inverted comma - CHAR(39).

How would I substitute the 'USD' in the MDX above with an excel cell reference and also have single inverted commas inserted before and after the cell reference - to achieve 'USD' (when the workbook cell reference is simply USD)?

I've tried:

Code: Select all

="{FILTER (
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Dimension1] )}, 0)}, ASC)} ,
[CC_Config].([Version].[&CHAR(34)&$F$15&CHAR(34)&],[CC].[HQ],[}ElementAttributes_Dimension1].[Currency])=&CHAR(39)&$G$15&CHAR(39)&
) }"

Code: Select all

Thanks guys.

[Wim Gielis]

I'm not sure it's inverted comma's surrounding USD, I thought it's double quotes on either side.

Try for a more clean suggestion:

="{FILTER( TM1SORT( TM1FILTERBYLEVEL( TM1SUBSETALL( [Dimension1] ), 0), ASC), [CC_Config].([Version].[" & $F$15 & "],[CC].[HQ],[}ElementAttributes_Dimension1].[Currency]) = """ & G15 & """) }"

[JamiseBondi]

Thank you Wim,

I was trying to insert a single inverted comma, but excel didn't like " ' "
I used your example of " " " and it worked.
Much appreciated!