MDX filtering by attribute for current user

Post Reply
CRP0021
Posts: 8
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

MDX filtering by attribute for current user

Post by CRP0021 » Sun May 27, 2018 11:29 pm

Hello all,
I am trying to create an MDX statement which will allow me to filter by an attribute who's value is equal to the current user. Here are the details:

This MDX statement retrieves the current user in the }Clients dimension...
{StrToMember("[}Clients].[" + USERNAME + "]")}
So example this returns AD/Bob Smith

I have a dimension called versions and an attribute on this dimension called "Created by User" and I'd like to filter the versions only created by the current user.

When I hard-code the username the filter is a pretty simple filter by attribute statement...
{FILTER( {TM1SUBSETALL( [versions] )}, [versions].[Created by User] = "AD/Bob Smith")}
This is returning only the elements for user Bob Smith as expected.

When I try and combine the 2 MDX statements together I'm running into a bit of trouble and wondering if anyone had previously come across something like this and what a solution might be.

Thanks in advance!

lotsaram
MVP
Posts: 3138
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX filtering by attribute for current user

Post by lotsaram » Mon May 28, 2018 4:39 am

Post your code. The actual code that isn’t working.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

declanr
MVP
Posts: 1588
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX filtering by attribute for current user

Post by declanr » Mon May 28, 2018 6:51 am

You got pretty close and had all the constituent parts ready, the the below:

Code: Select all

{Filter(
	{[version].members},
	[version].[created by user] =  StrToMember ( "[}Clients].[" + UserName + "]" ).name 
	 )
}

Edit - a second thought on how you are actually populating the attribute; maybe the attribute held against the version isn't actually the default user name, but instead an alias - in which case you could use something like below:

Code: Select all

{Filter(
	{[version].members},
	[version].[created by user] =  StrToMember ( "[}Clients].[" + UserName + "]" ).properties('}TM1_DefaultDisplayValue')
	 )
}

CRP0021
Posts: 8
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: MDX filtering by attribute for current user

Post by CRP0021 » Mon May 28, 2018 1:40 pm

Thanks declanr that worked very well!
Appreciate the response.
Cheers!

Post Reply