Prologika Forums

I wish to create an MDX Query that will bring back all the members of a parent-child dimension irrespective of what level they may be in 1 column.

 If i run the below query:

select
{Descendants([SummaryGLAccount].[SKParentAccountID].[Level 02].ALLMEMBERS)} on 1,
{[Measures].[GLAcctName], [Measures].[ReportActual],[Measures].[ReportBudget],[Measures].[BudgetVariance] } on 0
from
[RepFinance]
where ([SummaryGLAccount].[SummaryReport].&[1] , [Date].[Financial].[Fin Period].&[200902])

it brings back a column for every level in the hierarchy, to combat this I could create an calculated member like this:

iif([SummaryGLAccount].[SKParentAccountID].Currentmember.Level.Name = "Level 05" ,
   iif([SummaryGLAccount].[SKParentAccountID].CurrentMember is null,
     null,
     [SummaryGLAccount].[SKParentAccountID].CurrentMember.Name),
 iif([SummaryGLAccount].[SKParentAccountID].Currentmember.Level.Name = "Level 04" ,
   iif([SummaryGLAccount].[SKParentAccountID].CurrentMember is null,
     null,
     [SummaryGLAccount].[SKParentAccountID].CurrentMember.Name),
null))

which will work for what I want to do, my question is... is there a better way?

Not sure why this query would bring back a column for each level. Can you repro it against the Adventure Works cube? The following query brings back all employees in a single column:

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Tax Amount]} on 0,

Non Empty {Descendants ([Employee].[Employees].[Employee Level 02].ALLMEMBERS)} on 1

from [adventure works]

where [Date].[Calendar].[Month].&[2003]&[3]