Search code examples
sql-serversql-server-2008mdx

how do I get the parent name in MDX query

I am trying to get the parent name of my agent list in the same row as the agency code, but I can't seem to get the right coding in the with member section

current code:

with
member Measures.[Parent Name] as [Agent].[Agency_View].[Agency Agent].currentmember.parent
select
    {Measures.[Parent Name],
    [Measures].[Costs], 
    [Measures].[Revenue]} on columns,
    [Agent].[Agency_View].[Agency Agent] on rows
from 
    [Distribution]

but all I get in the Parent Name column is #Error

VALUE #Error Query (2, 34) The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.

I have also tried:

as [Agent].[Agency_View].[Agency Agent].currentmember.properties("Agency Market")
as [Agent].[Agency_View].[Agency Agent].currentmember.Parent.Member_Caption
as [Agent].[Agency_View].[Agency Agent].currentmember.hierarchy.name
as ancestor([Agent].[Agency_View].[Agency Agent].currentmember,1)
as [Agent].[Agency_View].[Agency Agent].currentmember.parent.name
as [Agent].[Agency_View].[Agency Agent].currentmember.parent.Member_Caption

Any other things for me to try?

Solution

  • If you ever get a #Error cell in SSMS then double click it to get a better error.

    In this case, I believe your Member_Caption attempt was close but you were trying to run the CurrentMember function on a level object not a hierarchy. Please try the following:

    member Measures.[Parent Name] as [Agent].[Agency_View].CurrentMember.Parent.Member_Caption