How to retain expand/collapse with custom MDX in PerformancePoint

When creating a dashboard in PerformancePoint Services (PPS), I often resort to custom MDX in a number of areas:

• Custom Filters
• Custom Scorecard Axes

However, in both cases, PPS immediately drops the expand/collapse capability (tree view). I've explored the MDX issued by PPS using SQL Profiler and have attempted to mimic it to no avail.

For example, the following MDX in an MDX filter creates a Year/Month hierarchy without the intermediate semesters and quarters:

SELECT GENERATE(
  [Date].[Calendar].[M开发者_开发知识库onth].Members
  , {
      Ancestors([Date].[Calendar].CurrentMember, [Date].[Calendar].[Calendar Year])
    , Ancestors([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month])
    }
  )
DIMENSION PROPERTIES MEMBER_TYPE ON 0
FROM [Adventure Works]

This results in a list of years and month, sorted correctly. But no tree view capabilities remain, despite selecting "Tree View" as the filter view type.

So, how do you feed PPS an MDX result set and end up with a working tree view?

I think instead of using Ancestors just use Descendants.

I usually use this structure in my PPS filters and it gives you tree view:

NonEmpty(
    Order(
        Descendants(
            [Date].[Fiscal Calendar].[All].Children, 
            [Date].[Fiscal Calendar].[Month], 
            SELF_AND_BEFORE), 
        [Date].[Fiscal Calendar].CurrentMember.Member_Key, 
        DESC)
    )

The result is years \ months with data. Descendants is pretty useful. And in a PPS filter expression CurrentMember is out of context. You have no CurrentMember at that point.

Let me know if it's helped or not what you're after.