TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

MDX StrToMember Error

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

Page 1 of 1

MDX StrToMember Error

Posted: Fri Feb 08, 2019 6:48 pm
by lav4you
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

Re: MDX StrToMember Error

Posted: Sat Feb 09, 2019 1:16 am
by Wim Gielis
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.

Re: MDX StrToMember Error

Posted: Sat Feb 09, 2019 11:17 am
by lav4you
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
Error1
error1.jpg (18.66 KiB) Viewed 10345 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
Error3
Error3.jpg (21.05 KiB) Viewed 10345 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
Error4
error4.png (3.51 KiB) Viewed 10345 times


But still no luck.
MemberToStr does not like dynamic attribute values and results in an error

Re: MDX StrToMember Error

Posted: Sat Feb 09, 2019 11:56 am
by tomok
Is Write_Flag a text or numeric attribute? If it is numeric try changing it to a text attribute. STRTOMEMBER expects a string value.

Re: MDX StrToMember Error

Posted: Sat Feb 09, 2019 12:44 pm
by lav4you
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
)

Re: MDX StrToMember Error

Posted: Sat Feb 09, 2019 5:02 pm
by gtonkin
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)+"]")

Re: MDX StrToMember Error

Posted: Mon Feb 11, 2019 12:43 pm
by lav4you
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

Re: MDX StrToMember Error

Posted: Mon Feb 11, 2019 1:29 pm
by PavoGa
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.

Re: MDX StrToMember Error

Posted: Tue Feb 19, 2019 1:07 pm
by lav4you
Hi,

Thanks for the following up on this thread.
I tried the following code but it did not work.

Code: Select all

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

Re: MDX StrToMember Error

Posted: Tue Feb 19, 2019 1:26 pm
by PavoGa
This is what you need to reference the user id:

Code: Select all

StrToMember("[}Clients].["+USERNAME+"]")
To figure out what part of the MDX is not working, take each section and parse it out to figure what is and what is not returning the expected result. For example, to determine if this is working as expected:

Code: Select all

StrToMember("[Site].["+ [}ElementAttributes_Project].([Project].CurrentMember, [}ElementAttributes_Project].[Primary Site] ) + "]")
Then open the subset editor for the Site dimension and use this to test (substituting a real value for CurrentMember):

Code: Select all

{StrToMember("[Site].["+ [}ElementAttributes_Project].([Project].CurrentMember, [}ElementAttributes_Project].[Primary Site] ) + "]"}
When correct, this will return a single member set. Test all the pieces of your query in this manner until the culprit is found.

Re: MDX StrToMember Error

Posted: Tue Feb 19, 2019 11:41 pm
by lav4you
UserID it is just a pseudo code representing static user name like e.g. Admin.

what I am trying to do here is generate 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 original MDX statement is :

Code: Select all

Filter( {TM1SUBSETALL([Project])}, 
	[z_Security_Site].( StrToMember( "[}Clients].["+USERNAME+ "]"), 
	StrToMember ("[Site].["+ [Project].[Primary Site] +"]")
	,[z_Security_Control_Measure].[Write_Flag])>0 
)
the problem area is dynamic reference to project attribute.

Re: MDX StrToMember Error

Posted: Wed Feb 20, 2019 9:09 am
by babytiger
The issue here is not with the MDX. Checking the MDX, it looks ok.

The issue is here, there may be cases where one or more site codes stored (in the Primary Site attribute) is invalid site. Hence the MDX errored out.

If you are confident that all the non-blanks are valid site codes, then there is a way around it. First, you need to filter out the blank, then it will work.

Code: Select all

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

Re: MDX StrToMember Error

Posted: Wed Feb 20, 2019 5:38 pm
by lav4you
Hi Babytiger,

You are a star.
You were right one of my project had blank for primary site and that was the issue.

I never encountered this issue before so I wasn't aware that it always requires an accurate value for such MDX statement.

I have already used an alternative cube to resolve this issue but this is a good lesson to be remembered for a lifetime.

Thanks again for your help and thanks to all other members who replied to this thread earlier.

Regards,

Lav

Re: MDX StrToMember Error

Posted: Wed Feb 20, 2019 10:31 pm
by PavoGa
One additional consideration. Just in case that primary site attribute were to contain a bad element, it will still cause a failure of StrToMember. One way to avoid that is to add another attribute, using DIMIX, that holds the index value of the primary site within the parent dimension. You would want to filter on that attribute to make it bulletproof.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited