MDX return attibute

Post Reply
shockwave
Posts: 88
Joined: Mon Dec 15, 2008 10:45 am
OLAP Product: TM1
Version: 9.1.3
Excel Version: 2003 SP3

MDX return attibute

Post by shockwave »

Hi All,

I am trying to return the cost centre and its name from my subset. What code would I use to return the attribute name of the cc that I am after:

The attibute that returns the name of the cost centre = "Description"

{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[costcentre].[business]}, ALL, RECURSIVE )}, 0)}

As an example my mdx statement returns say the following:

8256
8623
8456

I want to be able to return the Description attribute from the "costcentre" dimension so my result would be:

8256 - managment
8623 - procurement
8456 - something

Cant seem to find much about this. Anyone have any ideas?

Cheers

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

Re: MDX return attibute

Post by lotsaram »

If the description attribute is an alias then you can use SubsetAliasSet( DimName, SubName, AliasName ) in TI to set an alias to use in the subset definition.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: MDX return attibute

Post by Mike Cowie »

Hi Shock,

Just to add onto the first response, which will definitely help you switch on an alias if you're creating MDX subsets from TI...

Unfortunately, you don't have control over the alias that is used by the subset from within your MDX statement. The alias attribute used is sort of outside of/above the MDX expression - whatever elements are returned by the expression are either displayed as defined in the dimension or rendered based on a selected, active alias. In other words, all the MDX does in TM1 subset expressions is tell which elements should be returned and can't do anything about the displayed names.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
nhavis
Posts: 62
Joined: Mon Jan 05, 2009 12:47 am

Re: MDX return attibute

Post by nhavis »

For what purpose, shockwave?
shockwave
Posts: 88
Joined: Mon Dec 15, 2008 10:45 am
OLAP Product: TM1
Version: 9.1.3
Excel Version: 2003 SP3

Re: MDX return attibute

Post by shockwave »

Thanks Gents. The first idea should work well ( SubsetAliasSet( DimName, SubName, AliasName ) ) but was hoping that there was something that allowed you to pull a aliasname back, but it appears not. Thanks mike for the explanation kind of makes sense when mdx returns tuples which is an intersection of elements which attributes are not. I did find something here:

http://msdn.microsoft.com/en-us/library ... L.80).aspx

here is the snippet:

Properties

Returns a string containing a member property value.
Syntax

«Member».Properties(«String Expression»)
Remarks

The Properties function returns the value of the member property specified in «String Expression». The member property can be any of the standard member properties, such as NAME, ID, KEY, or CAPTION, or it can be a user-defined member property.
Example

In the Store dimension, if the Store Name level has an associated member property, Store Manager, the following example returns Smith:

[Store].[All Stores].[USA].[WA].[Bellingham].[Store 2].Properties("Store Manager")

It mentions that you can call a function called "properties" as per above When I was in the tm1 subset editor and typed in the following it didn't seem to like "[costcentre].[00000008324].Properties("Description"), thought it may work but nooooooo.

Why does this not work? Is this something that TM1 does not support. Using 9.1.3.

Cheers

Shock
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: MDX return attibute

Post by Mike Cowie »

Hi Shock,

Yes, the Properties syntax is nice when you're using something like the FILTER function and the [Dimension].[Attribute] syntax is such that Attribute conflicts with an Element name. But, I don't think TM1 will return something other than a member/element of the dimension using a subset expression. I agree that it would be nice to have it do this, but in the end the Selected Alias setting of the subset is what trumps all when it comes to the displayed names.

Going back many versions ago (probably early 8.0), you used to be able to (confusingly) return elements from other dimensions into a subset using an MDX expression (e.g., use "[Accounts].[Total Assets].Children" in the Entity dimension), so I think as time has passed there are now more checks in place to restrict one from doing anything out of the ordinary in a subset expression. I haven't noticed anything strikingly different in recent versions, but development has put some effort into the MDX support over the years - as a result you might get some benefit out of at least reporting what you're seeing as a bug/issue/enhancement to IBM Cognos in order to get a clearer answer from product development.

Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
Post Reply