SSAS - MDX - Read multiple attributes from a dimension

Question

For example, taking into account the Adventure Works database, I would like to display on rows the hierarchy of accounts and on columns i would like to show "Account Type" property.

This MDX shows an "#Error" on the Account Type column:

WITH MEMBER [Measures].[Account Type] AS [Account].[Accounts].[Account Type].CurrentMember.Name 
SELECT
    {[Measures].[Account Type]} ON 0,   
    (
        [Account].[Accounts].Members
    ) ON 1
FROM [Adventure Works]

Any idea how this can be improved?

FrankPl FrankPl · Accepted Answer · 2014-07-30T07:35:35

If you execute your query in Management Studio and either hover the mouse over the "#ERROR" or double click a cell showing "#ERROR", the error message is shown, which states that you use an element expression for CurrentMember while it was expecting an hierarchy. You should use

[Account].[Accounts].CurrentMember.Properties("Account Type", TYPED)

instead of

[Account].[Accounts].[Account Type].CurrentMember.Name

to access properties. In this specific case, the second argument to Properties can be omitted, as it states that the result should be returned as the data type of the property (e. g. integer or date). But here, the original is a string anyway, hence you could simply use

[Account].[Accounts].CurrentMember.Properties("Account Type")

which always returns the property as string, independent of its type.

SSAS - MDX - Read multiple attributes from a dimension

1 Answers