MDX StrToMember Error

Post Reply
lav4you
Posts: 27
Joined: Fri Jan 02, 2009 1:20 pm

MDX StrToMember Error

Post by lav4you » Fri Feb 08, 2019 6:48 pm

Hi,

I am trying to create a dynamic subset for a 'Project' dimension which looks at data in another cube 'z_Security_Site' cube which stores a numeric flag(1) for each user who has access to a site. Z_Securtiy_Site cube has 3 diemnsions i.e. }client, Site and z_Security_Control_Measure diemsions

The site information is available in Project Dimension as an attribute.

My MDX query looks like the following -

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ('[Site].['+ [b][Project].[Primary Site][/b] +']')
,[z_Security_Control_Measure].[Write_Flag])>0 )

it errors at StrToMember Function

Error Message reads:
STRTOMEMBER: Error in value Expression:""

I have tried referencing attribute cube instead of calling attribute

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].([}Clients].[UserID], 
StrToMember("[Site].["+[}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site])+"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )
but still that same error.

May I doing something wrong here??
Your help will be much appreciated.
Thanks.
Lav

Wim Gielis
MVP
Posts: 1939
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: MDX StrToMember Error

Post by Wim Gielis » Sat Feb 09, 2019 1:16 am

In the first code, StrToMember requires double quotes, not single quotes.

Did you check out this very recent topic ?
https://www.tm1forum.com/viewtopic.php?f=3&t=14492

In the second code, what is UserID ? Is it the fixed string 'UserID' and not dependent on the logged in user ?

For reference, this works for me with attributes, although th period element M07 is hardcoded:

Code: Select all

{StrToMember("[Bp_Period].[" + [}ElementAttributes_Bp_Period].([Bp_Period].[M07],[}ElementAttributes_Bp_Period].[Next Period]) + "]") }
You will also want to experiment with Properties(“Primary Site”) to refer to the attribute.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

lav4you
Posts: 27
Joined: Fri Jan 02, 2009 1:20 pm

Re: MDX StrToMember Error

Post by lav4you » Sat Feb 09, 2019 11:17 am

Hi Wim,

Many thanks for the reply.

I have tried the following different variations earlier but it did not help :cry:

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ("[Site].["+[Project].[Primary Site] +"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ("[Site].["+[Project].CurrentMember.Properties("Primary Site") +"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ('[Site].['+[Project].CurrentMember.Properties('Primary Site') +']')
,[z_Security_Control_Measure].[Write_Flag])>0 )
Regarding UserID it iss a pseudo code representing static user name like e.g. Admin.

Thanks for your example with M07 static period but if this statement does not work with dynamic Project selection( or in your example Period) than it defeats its purpose isn't it?

One behavior I have noticed is StrToMember function expected all string values

So I also tried

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ("[Site].["+
MemberToStr([Project].[Primary Site] )
+"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )
error1.jpg
Error1
error1.jpg (18.66 KiB) Viewed 132 times

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].( [}Clients].[UserID], 
StrToMember ("[Site].["+
MemberToStr([Project].CurrentMember.Properties("Primary Site") )
+"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].([}Clients].[UserID], 
StrToMember("[Site].["+
MemberToStr( [}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site]) )
+"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )
Error3.jpg
Error3
Error3.jpg (21.05 KiB) Viewed 132 times

Code: Select all

Filter( {TM1SUBSETALL([Project])}, [z_Security_Site].([}Clients].[UserID], 
StrToMember("[Site].["+
MemberToStr( [}ElementAttributes_Project].([Project].CurrentMember, [}ElementAttributes_Project].[Primary Site]) )
+"]")
,[z_Security_Control_Measure].[Write_Flag])>0 )
error4.png
Error4
error4.png (3.51 KiB) Viewed 132 times


But still no luck.
MemberToStr does not like dynamic attribute values and results in an error
Last edited by lav4you on Sat Feb 09, 2019 12:30 pm, edited 1 time in total.

tomok
MVP
Posts: 2519
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX StrToMember Error

Post by tomok » Sat Feb 09, 2019 11:56 am

Is Write_Flag a text or numeric attribute? If it is numeric try changing it to a text attribute. STRTOMEMBER expects a string value.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lav4you
Posts: 27
Joined: Fri Jan 02, 2009 1:20 pm

Re: MDX StrToMember Error

Post by lav4you » Sat Feb 09, 2019 12:44 pm

Hi Tom,

Thank for the quick reply.

[Write_Flag] It's a Numeric filed.
I can try converting that to string but I doubt that would yield any result as [Write_Flag] field is outside of the scope of StrToMember function.

#Filter applied on z_Security_Site Cube
Filter(

Selecting all element of Project dimension
{TM1SUBSETALL([Project])},

#Start filter Argunment LHS. This is a 3 dimensional cube which is defined in the following three statements
[z_Security_Site].

#First dmension of z_Security_Site cube
([}Clients].[UserID],

#Second dimension of Z_Securtiy_Site cube. The dimension name is 'Site' I am trying to dynamically derive site elements using project attribute - 'Primary Site'. Primary Site is a Text attribute type.
StrToMember("[Site].["+[}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site])+"]")

#Third dimension of z_Security_Site cube this is a measure dimension of this cube - Type numberic
,[z_Security_Control_Measure].[Write_Flag])

#Filter artgument RHS
>0

#Filter Closed
)

User avatar
gtonkin
MVP
Posts: 647
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX StrToMember Error

Post by gtonkin » Sat Feb 09, 2019 5:02 pm

lav4you wrote:
Sat Feb 09, 2019 12:44 pm
...
StrToMember("[Site].["+[}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site])+"]")
StrToMember as you said wants a string - the reference to Primary Site is still an element/member.

Try adding a .name to the end i.e.

Code: Select all

StrToMember("[b][Site].[[/b]"+[}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site].name)+"]")

lav4you
Posts: 27
Joined: Fri Jan 02, 2009 1:20 pm

Re: MDX StrToMember Error

Post by lav4you » Mon Feb 11, 2019 12:43 pm

Hi gtonkin,

Thanks for your help.

It did not work, I think its not a correct application of '.Name' function.

For the interested readers, I have resolved this issue by creating a new cube with Project and Client Dimension which holds Primary Site access information in the form of flags(1/0) and now I am using these flags to filter the Project dimension.

Thanks everyone for your help and suggestions.

Regards,
Lav

User avatar
PavoGa
Community Contributor
Posts: 286
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX StrToMember Error

Post by PavoGa » Mon Feb 11, 2019 1:29 pm

lav4you wrote:
Sat Feb 09, 2019 12:44 pm

StrToMember("[Site].["+[}ElementAttributes_Project].([}ElementAttributes_Project].[Primary Site])+"]")
In your call to [}ElementAttributes_Project], the code is missing a reference to [Project] so it cannot find the desired intersection; it is just calling the measure [Primary Site]. I think you need this instead:

Code: Select all

[}ElementAttributes_Project].([Project].currentmember, [}ElementAttributes_Project].[Primary Site])
Of course, you can substitute whatever you need for currentmember.
Ty
Cleveland, TN

Post Reply