MDX in ActiveForm with "INSTR" does not work

[Mark2007]

Hi there,

I created a MDX-Statement, that works in the Subset editor and gives results, but does not work in an activeForm.
MDX-Statement: {Filter( { TM1SUBSETALL( [DimName])}, INSTR([DimName].[AliasName], "EKMS_33446") <> 0)}

Other MDX-Statements work in the ActiveForm, such as:
{TM1FilterByPattern({TM1SubsetAll([DimName])}, "EKMS_33446*")}

My aim is to create a MDX, that gives me a List of Elements where the Alias (!) are partly the same, e.g.
EKMS_33446_Name1
UK_12345_Name1
NE_234123_Name1
and I would like to get a MDX that looks for "Name1" in the Alias and lists all of the three.

I'm working with TM1 10.1.
Excel 2010

Any Ideas?

Regards

Mark

[rmackenzie]

I created a MDX-Statement, that works in the Subset editor and gives results, but does not work in an activeForm.

Does it only work in the subset editor when you have the alias actually turned on? If it doesn't work in the subset editor when aliases are off then at least you know it's consistent with the Active Form.

[tm1_bloke]

I have similar experiences that INSTR doesn't work in Excel Active Forms BUT when the same active form is uploaded in TM1 Web, it works there without any issues.

[Mark2007]

I created a MDX-Statement, that works in the Subset editor and gives results, but does not work in an activeForm.

Does it only work in the subset editor when you have the alias actually turned on? If it doesn't work in the subset editor when aliases are off then at least you know it's consistent with the Active Form.

In Subseteditor, the MDX works, even if the Alias is not activated.

[Mark2007]

I have similar experiences that INSTR doesn't work in Excel Active Forms BUT when the same active form is uploaded in TM1 Web, it works there without any issues.

Hi there, you're right, it works in web! Thanks!

Mark

[summerbrewgal]

In case anyone needs it to work in Perpsectives too, if you set the 4th optional parameter of InStr to 1 (text compare) that the function will work in both Perspectives and the web.
Change this:
MDX-Statement: {Filter( { TM1SUBSETALL( [DimName])}, INSTR([DimName].[AliasName], "EKMS_33446") <> 0)}
to this:
MDX-Statement: {Filter( { TM1SUBSETALL( [DimName])}, INSTR([DimName].[AliasName], "EKMS_33446",1) <> 0)}

[lotsaram]

In case anyone needs it to work in Perpsectives too, if you set the 4th optional parameter of InStr to 1 (text compare) that the function will work in both Perspectives and the web.
Change this:
MDX-Statement: {Filter( { TM1SUBSETALL( [DimName])}, INSTR([DimName].[AliasName], "EKMS_33446") <> 0)}
to this:
MDX-Statement: {Filter( { TM1SUBSETALL( [DimName])}, INSTR([DimName].[AliasName], "EKMS_33446",1) <> 0)}

Thanks for the pointer. Hopefully this will help others. I recently came across this very issue found the same workaround, albeit not before I had raised a PMR. I can't help but suspect that the timing of your post is more than coincidence.

[cdredmond]

Thank you all for this great discussion on this unlisted, but unofficially available INSTR MDX function in TM1!
A special thanks to SummerBrewGal for her post about using the optional fourth "language compatibility" parameter. Your post sent me down the path to explore this option which ultimately led to my discovery!

After some extensive testing, here is what I have found while working on TM1 v10.2.2 FP7 with IntegratedSecurityMode=5.

INSTR works pretty much as expected in the Perspectives Subset Editor. Beyond that, its a crap shoot with each tool.
Specifically, in TI the sub-string to match (third parameter) is limited to four characters. Beyond that, it returns an empty set.
To get around this problem, you must use the "optional" parameters. Both "optional" parameters MUST be used.
If you use the fourth parameter without the first parameter, an error is generated.

Here is example MDX code I used to get a pattern match out of the }TM1_DefaultDisplayValue alias in the }Groups dimension:

{ FILTER( { TM1SUBSETALL( [}Groups] ) }, INSTR( 1,[}ElementAttributes_}Groups].([}ElementAttributes_}Groups].[}TM1_DefaultDisplayValue]), ".NorthAmerica.",1 ) > 0 ) }

This code returns a set of elements with the sub-string ".NorthAmerica." in the Group name which came from MS AD similar to the list below.

ActiveDirectory\Application.CognosTM1.NorthAmerica.AdvUser.Contributor
ActiveDirectory\Application.CognosTM1.NorthAmerica.User.Contributor
ActiveDirectory\Application.CognosTM1.NorthAmerica.User.Explorer

[iansdigby]

...and you must include both the first and the fourth '1' parameters to get Instr to work in Perspectives too. e.g.

{FILTER([Employee].MEMBERS,Instr(1,[Employee].[EmployeeID_Name],"Jones",1)<>0)}

That is at least true of Plan An 2.0

[Wim Gielis]

In this topic, there is a continuation of the discussion:
https://community.ibm.com/community/use ... 4eda43146b

[Mark RMBC]

Hi Wim,

the more relevant topic is this one, as this is the topic that I think triggered George's post on the community:

https://www.tm1forum.com/viewtopic.php?t=16193

regards,

mark

[mnasra]

THANK YOU. THANK YOU. THANK YOU.

4 days I have been trying to find a solution until I wrote the right keyword into the forum.
THANK you all.
This Forum is SUPER.
PS: english is my third language.