MDX StrToMember Error

Post Reply
lav4you
Posts: 30
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: 1944
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: 30
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 292 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 292 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 292 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: 2523
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: 30
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: 30
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: 289
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

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

Re: MDX StrToMember Error

Post by lav4you » Tue Feb 19, 2019 1:07 pm

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 )

User avatar
PavoGa
Community Contributor
Posts: 289
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 » Tue Feb 19, 2019 1:26 pm

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.
Ty
Cleveland, TN

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

Re: MDX StrToMember Error

Post by lav4you » Tue Feb 19, 2019 11:41 pm

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.

babytiger
Posts: 75
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: MDX StrToMember Error

Post by babytiger » Wed Feb 20, 2019 9:09 am

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 
)}
MK

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

Re: MDX StrToMember Error

Post by lav4you » Wed Feb 20, 2019 5:38 pm

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

User avatar
PavoGa
Community Contributor
Posts: 289
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 » Wed Feb 20, 2019 10:31 pm

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.
Ty
Cleveland, TN

Post Reply