none
Ordering related non-leaf attributes - is this possible?

    Question

  • I wonder if anyone has run into this before.

    I'm trying to produce a report in which all rows are sorted by a specific
    column. For example the following works fine:

    WITH
    SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
    SET [ColumnSet] AS [Date].[Calendar].Levels(1).Members
    Select
    Order([RowSet], [ColumnSet].Item(0), BDESC) ON ROWS,
    [ColumnSet] ON COLUMNS
    FROM [Adventure Works]

    All product sub-categories are sorted by CY 2001.

    However when the rows and columns of the report contain related non-leaf
    attributes of the same hierarchy then the sort does nothing, as illustrated
    in this example:

    WITH
    SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
    SET [ColumnSet] AS [Product].[Category].Levels(1).Members
    Select
    Order([RowSet], [ColumnSet].Item(0), BDESC) ON ROWS,
    [ColumnSet] ON COLUMNS
    FROM [Adventure Works]

    Is such sort possible at all, may be using different MDX functions, or is
    this a known limitation for related attributes?

    Interestingly enough it works when the leaf-level attribute
    ([Product].[Product]) is mixed with a non-leaf level attribute (e.g.
    [Product].[Category]).

    Thanks,
    Stan
    Tuesday, May 04, 2010 3:59 PM

Answers

  • I think that the issue in this case is that the [Category] attribute on columns is directly related to [Subcategory] on rows. So the Order value expression: [ColumnSet].Item(0) sets the directly relating attribute: [Subcategory] to [All] - explicitly specifying the current member of the [Subcategory] attribute seems to work:

    WITH
    SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
    SET [ColumnSet] AS [Product].[Category].Levels(1).Members
    Select
    Order([RowSet],
    ([ColumnSet].Item(0), [RowSet].Item(0).Hierarchy.CurrentMember),
    BDESC) ON ROWS,
    [ColumnSet] ON COLUMNS
    FROM [Adventure Works]

     

     


    - Deepak
    • Marked as answer by Stan Kondrat Tuesday, May 04, 2010 6:56 PM
    Tuesday, May 04, 2010 6:27 PM
    Moderator

All replies

  • I think that the issue in this case is that the [Category] attribute on columns is directly related to [Subcategory] on rows. So the Order value expression: [ColumnSet].Item(0) sets the directly relating attribute: [Subcategory] to [All] - explicitly specifying the current member of the [Subcategory] attribute seems to work:

    WITH
    SET [RowSet] AS [Product].[Subcategory].Levels(1).Members
    SET [ColumnSet] AS [Product].[Category].Levels(1).Members
    Select
    Order([RowSet],
    ([ColumnSet].Item(0), [RowSet].Item(0).Hierarchy.CurrentMember),
    BDESC) ON ROWS,
    [ColumnSet] ON COLUMNS
    FROM [Adventure Works]

     

     


    - Deepak
    • Marked as answer by Stan Kondrat Tuesday, May 04, 2010 6:56 PM
    Tuesday, May 04, 2010 6:27 PM
    Moderator
  • Thanks Deepak.

    This is exactly what I needed to know.

    Stan

    Tuesday, May 04, 2010 6:58 PM