MDX in ActiveForm with "INSTR" does not work

Post Reply
Mark2007
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

MDX in ActiveForm with "INSTR" does not work

Post by 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
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: MDX in ActiveForm with "INSTR" does not work

Post by rmackenzie »

Mark2007 wrote: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.
Robin Mackenzie
tm1_bloke
Posts: 25
Joined: Sun Oct 13, 2013 6:03 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: MDX in ActiveForm with "INSTR" does not work

Post by Mark2007 »

rmackenzie wrote:
Mark2007 wrote: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
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: MDX in ActiveForm with "INSTR" does not work

Post by Mark2007 »

tm1_bloke wrote: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
Posts: 4
Joined: Tue Mar 10, 2009 9:36 pm
Version: 9.4
Excel Version: 2003

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX in ActiveForm with "INSTR" does not work

Post by lotsaram »

summerbrewgal wrote: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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
cdredmond
Posts: 23
Joined: Tue Sep 08, 2009 2:46 pm
OLAP Product: TM1
Version: SpreadsheetConnector4.0-10.2.2
Excel Version: v3 - 2013
Location: Tigard, OR (Portland, Oregon Metro area)
Contact:

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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
Christopher Redmond
Senior TM1 Consultant
http://www.bpmnw.com
Office: (503) 747-2614
iansdigby
Community Contributor
Posts: 109
Joined: Thu Feb 26, 2009 8:44 am
OLAP Product: TM1
Version: 9 + 10 + Plan An
Excel Version: All
Location: Isle of Wight, UK

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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
"the earth is but one country, and mankind its citizens" - Baha'u'llah
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX in ActiveForm with "INSTR" does not work

Post by Wim Gielis »

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: MDX in ActiveForm with "INSTR" does not work

Post by 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.
Thanks
Micheline
Post Reply