MDX StrToMember Error

[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

[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.

[lav4you]

Hi Wim,

Many thanks for the reply.

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

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.jpg (18.66 KiB) Viewed 13774 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.jpg (21.05 KiB) Viewed 13774 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.png (3.51 KiB) Viewed 13774 times

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

[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.

[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
)

[gtonkin]

...
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]

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

[PavoGa]

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.

[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 )

[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.

[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.

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

[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

[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.