MDX-How can I get attribute key with MDX
Q: How can I get attribute key with MDX
A:
To do so, use Member_Key function:
WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]
Added May 19, 2009 by Vidas Matelis.
You can also get the same results by using Properties("Key") function:
WITH
MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Properties("Key")
SELECT {Measures.ProductKey} ON axis(0),
[Product].[Product Categories].Members on axis(1)
FROM [Adventure Works]
For coposite keys Member_Key and Properties("Key") will return NULL values. You should use Properties("Key0"), Properties("Key1"), etc to get composit keys:
WITH
MEMBER Measures.StateProvinceKey0 AS [Customer].[State-Province].CurrentMember.Properties("KEY0")
MEMBER Measures.StateProvinceKey1 AS [Customer].[State-Province].CurrentMember.Properties("KEY1")SELECT {Measures.StateProvinceKey0, Measures.StateProvinceKey1} ON axis(0),
[Customer].[State-Province].Members on axis(1)
FROM [Adventure Works]
Here are results for the last query:
State-Province StateProvinceKey0 StateProvinceKey1 All Customers 0 #Error Alabama AL US Alberta AB CA Arizona AZ US Bayern BY DE Brandenburg BB DE British Columbia BC CA Brunswick NB CA California CA US Charente-Maritime 17 FR Colorado CO US
Note: There is performance penalty when you convert dimension key to a measure.