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!
MDX filtering by attribute for current user
-
- MVP
- Posts: 3652
- 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 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.
-
- MVP
- Posts: 1815
- 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
You got pretty close and had all the constituent parts ready, the the below:
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 + "]" ).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')
)
}
Declan Rodger
-
- Posts: 27
- 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
Thanks declanr that worked very well!
Appreciate the response.
Cheers!
Appreciate the response.
Cheers!