Report Portal

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-ProvinceStateProvinceKey0StateProvinceKey1
All Customers0#Error
AlabamaALUS
AlbertaABCA
ArizonaAZUS
BayernBYDE
BrandenburgBBDE
British ColumbiaBCCA
BrunswickNBCA
CaliforniaCAUS
Charente-Maritime17FR
ColoradoCOUS

Note: There is performance penalty when you convert dimension key to a measure.

 

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.