Parameterized MDX

[Nance1234]

Hi experts,

I have simple MDX to be filtered by a wildcard which needs to be parameterized within a process.

The result should be as below where we are filtering by level zero for consolidation 'Total' and then doing a wildcard search for 'GL' in those elements.
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account ].[Total] ) } , 0)}, "*GL*" )}

The one modified to use parameter is giving errors , mabe putting "*GL*" in quotes is causing the syntax error
{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account].[Total] ) } , 0)}, ' "* '| pAccount |' *" ')}

Please guide how fix this MDX for a wildcard search 'GL' which will be a parameter 'pAccount' to be entered while running the TI process.
I have been playing around the quotes and concatenation for long.

Thanks in advance.

[lotsaram]

Here you can use Expand to generate a string which contains double quotes.
TM1FILTERBYPATTERN also has an argument for the attribute to search in. Otherwise you will be searching the principal name.
The Descendants function can already return only leaves so no need to wrap in FilterByLevel.
Even though your Account dimension might not have hierarchies it's better practice to future proof code by referring to the hierarchy not just the dimension.

e.g. assuming your Account dimension has an attribute called Description and that's where you want to search.

Code: Select all

Expand('{TM1FILTERBYPATTERN( {Descendants( [ Account ].[Account].[Total], [ Account ].[Account].Levels.Count, LEAVES )}, "*%pSearchString%*", "Description" )}')

[gtonkin]

You need to assign the whole MDX statement to a string variable e.g.

Code: Select all

sMDX='{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account].[Total] ) } , 0)},  "* ' | pAccount | ' *" )}';

You could also try reduce the complexity of the quotes by using the Expand() function:

Code: Select all

sMDX=Expand('{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account].[Total] ) } , 0)},  "*%pAccount%*" )}');

There are pros and cons - Depending on what I am doing, the first example may be better as the colouring in PAW shows variables distinct from text.
If you use Expand() and you spelled the variable incorrectly, you only pick that up at runtime.

Edit: only saw Lotsa’s reply after posting this. What he said…

[Nance1234]

Here you can use Expand to generate a string which contains double quotes.
TM1FILTERBYPATTERN also has an argument for the attribute to search in. Otherwise you will be searching the principal name.
The Descendants function can already return only leaves so no need to wrap in FilterByLevel.
Even though your Account dimension might not have hierarchies it's better practice to future proof code by referring to the hierarchy not just the dimension.

e.g. assuming your Account dimension has an attribute called Description and that's where you want to search.

Code: Select all

Expand('{TM1FILTERBYPATTERN( {Descendants( [ Account ].[Account].[Total], [ Account ].[Account].Levels.Count, LEAVES )}, "*%pSearchString%*", "Description" )}')

Thanks Lotsaram.

Understood that no need for FilterByLevel if Descendants is used.

We don't have any hierarchies here and we are not using PAW. Just filtering on the principle name and not on any attribute.
Still getting syntax error on this one. Please guide.

vMDX = ' Expand ( '{TM1FILTERBYPATTERN( {Descendants( [' | dimName | '].[Total], [' | dimName | '].[' | dimName | ']. Levels.Count, LEAVES) } , "*%pAccount%*")} ' ) ' ;

[Nance1234]

You need to assign the whole MDX statement to a string variable e.g.

Code: Select all

sMDX='{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account].[Total] ) } , 0)},  "* ' | pAccount | ' *" )}';

You could also try reduce the complexity of the quotes by using the Expand() function:

Code: Select all

sMDX=Expand('{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [ Account].[Total] ) } , 0)},  "*%pAccount%*" )}');

There are pros and cons - Depending on what I am doing, the first example may be better as the colouring in PAW shows variables distinct from text.
If you use Expand() and you spelled the variable incorrectly, you only pick that up at runtime.

Edit: only saw Lotsa’s reply after posting this. What he said…

Thank you gtonkin

This Worked .
vMDX= Expand('{TM1FILTERBYPATTERN( {Descendants( [' | dimName | '].[Total] ) } , "*%pAccount%*" )}');

[gtonkin]

Glad it worked but would encourage you to put some time into what Lotsa said in terms of fully qualifying members with dimension and hierarchy. This could also be done using [%Dim%].[%Hier%].[…] style syntax and leveraging Expand() too, just like with the parameter.

Additionally, the 3rd parameter is also something to read up on as many search on a wildcard which is actually part of the description and the results are not returned as the principal name is what is actually being searched, as Lotsa stated.

[Nance1234]

Thank you to both of you. - Lotsaram and gtonkin

I will try to put the Hierarchy kinda MDX also which Lotsaram mentioned .

This one Worked .
vMDX= Expand('{TM1FILTERBYPATTERN( {Descendants( [' | dimName | '].[Total] ) } , "*%pAccount%*" )}');

But,

These did not work (did not render any element in the dimension)so I used the one above.I guess then for wildcard filter Expand is the only option.
vMDX='{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [' | dimName | '].[Total] ) } , 0)}, "* ' | pAccount | ' *" )}';
vMDX='{TM1FILTERBYPATTERN( {Descendants( [' | dimName | '].[ Total] ) }, "* ' | pAccount | ' *" )}';

[gtonkin]

Are there spaces between the asterisks and the single quotes? Cannot see on my phone but if there are you are filtering based on something containing a space before and after…

[Wim Gielis]

These did not work (did not render any element in the dimension)so I used the one above.I guess then for wildcard filter Expand is the only option.
vMDX='{TM1FILTERBYPATTERN( {TM1FILTERBYLEVEL( {Descendants( [' | dimName | '].[Total] ) } , 0)}, "* ' | pAccount | ' *" )}';
vMDX='{TM1FILTERBYPATTERN( {Descendants( [' | dimName | '].[ Total] ) }, "* ' | pAccount | ' *" )}';

Both options should work, probably like George said it’s the extra spaces.

[lotsaram]

I don't think you really understood the concept of using Expand here.

You EITHER use expand to build the whole string converting variable names to their value by wrapping with %
OR you use the pipe symbol to do the concatenation.
You don't use pipes within the Expand, because what is there is just a literal string except for text between %%.

[Nance1234]

I don't think you really understood the concept of using Expand here.

You EITHER use expand to build the whole string converting variable names to their value by wrapping with %
OR you use the pipe symbol to do the concatenation.
You don't use pipes within the Expand, because what is there is just a literal string except for text between %%.

Got it Lotsaram. Thank you for pointing that out.

[Nance1234]

Thank you all. Sorry for the late reply.
Yes , both options work.
Thanks again George ,Wim and Lotsaram