MDX in ActiveForm with "INSTR" does not work

Post Reply
Mark2007
Posts: 16
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2010

MDX in ActiveForm with "INSTR" does not work

Post by Mark2007 » Mon Apr 27, 2015 7:12 am

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 » Mon Apr 27, 2015 8:04 am

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: 23
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 » Mon Apr 27, 2015 8:14 am

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: 16
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2010

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

Post by Mark2007 » Mon Apr 27, 2015 2:09 pm

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: 16
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2010

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

Post by Mark2007 » Mon Apr 27, 2015 2:10 pm

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 » Tue May 17, 2016 8:31 pm

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: 2974
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » Thu May 19, 2016 4:38 pm

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 » Tue Aug 08, 2017 7:05 pm

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

Post Reply