MDX return attibute

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

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.

[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

[nhavis]

For what purpose, shockwave?

[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

[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