none
Analytics - dimension sort ordering RRS feed

  • Question

  • Hi all,

     

    I have a PPS analytic grid that looks like this:

     

    Year/Week Number Hierarchy                        Sales Units                  Sales Value

     

    2006                                                            100000                         10000000000000

    2007                                                             9999                            999999999999

     

     

    The year is a hierarchy dimensions and expands to show weeks 1-52.

     

    What we would like to do is to order the years in reverse order (i.e. 2007 comes first). I cannot see a way to do this in a grid as the sorting all seems geared to measures. Have I missed something, or would I need to use MDX for this and if so what MDX could I use (I cannot see to get order() to work).

     

    The MDX code is:

     

    WITH

    MEMBER [Date].[Week Number].[ Aggregation] AS 'AGGREGATE( EXISTING { [Calendar weeks to current week] } )', SOLVE_ORDER = 0

    SELECT

    NON EMPTY { [Reseller].[Reseller].[Reseller].ALLMEMBERS } * { [Measures].[Total Sales Units], [Measures].[Total Sales Value], [Measures].[YTD v L-YTD Units %], [Measures].[YTD v L-YTD Value %] }

    ON COLUMNS,

    NON EMPTY HIERARCHIZE( { [Date].[Week Of Year Hierarchy].[Calendar Year].&[2007], [Date].[Week Of Year Hierarchy].[Calendar Year].&[2006] } )

    ON ROWS

    FROM [Sales]

     

    Many thanks!!

     

    Matthew 

    Monday, December 3, 2007 11:08 AM

Answers

  • There may be a better way to do this, but for an easy fix, remove the "HIERARCHIZE" function that surrounds the year member selection, and this should give you the desired results:

     

     

    NON EMPTY  { [Date].[Week Of Year Hierarchy].[Calendar Year].&[2007], [Date].[Week Of Year Hierarchy].[Calendar Year].&[2006] }

    ON ROWS

    Friday, December 7, 2007 11:06 PM
  • Hi MAQ1

     

    I think this is what you're looking for:

     

    Code Block

    WITH

    MEMBER [Date].[Week Number].[ Aggregation] AS

    'AGGREGATE( EXISTING { [Calendar weeks to current week] } )', SOLVE_ORDER = 0

     

     

    SELECT

    NON EMPTY

    {[Reseller].[Reseller].[Reseller].ALLMEMBERS }

    *

    {

    [Measures].[Total Sales Units],

    [Measures].[Total Sales Value],

    [Measures].[YTD v L-YTD Units %],

    [Measures].[YTD v L-YTD Value %]

    }

    ON COLUMNS,

    NON EMPTY

    ORDER(

    [Date].[Week Of Year Hierarchy].[Calendar Year].members,

    [Date].[Week Of Year Hierarchy].MEMBER_NAME,

    DESC)

    ON ROWS

    FROM

    [Sales]

     

     

    HIERARCHIZE will attempt to order the members into their hierarchical order so isn't best used with the ORDER function

     

    HTH

     

    Tim

    Sunday, December 9, 2007 12:38 PM

All replies

  • There may be a better way to do this, but for an easy fix, remove the "HIERARCHIZE" function that surrounds the year member selection, and this should give you the desired results:

     

     

    NON EMPTY  { [Date].[Week Of Year Hierarchy].[Calendar Year].&[2007], [Date].[Week Of Year Hierarchy].[Calendar Year].&[2006] }

    ON ROWS

    Friday, December 7, 2007 11:06 PM
  • Hi MAQ1

     

    I think this is what you're looking for:

     

    Code Block

    WITH

    MEMBER [Date].[Week Number].[ Aggregation] AS

    'AGGREGATE( EXISTING { [Calendar weeks to current week] } )', SOLVE_ORDER = 0

     

     

    SELECT

    NON EMPTY

    {[Reseller].[Reseller].[Reseller].ALLMEMBERS }

    *

    {

    [Measures].[Total Sales Units],

    [Measures].[Total Sales Value],

    [Measures].[YTD v L-YTD Units %],

    [Measures].[YTD v L-YTD Value %]

    }

    ON COLUMNS,

    NON EMPTY

    ORDER(

    [Date].[Week Of Year Hierarchy].[Calendar Year].members,

    [Date].[Week Of Year Hierarchy].MEMBER_NAME,

    DESC)

    ON ROWS

    FROM

    [Sales]

     

     

    HIERARCHIZE will attempt to order the members into their hierarchical order so isn't best used with the ORDER function

     

    HTH

     

    Tim

    Sunday, December 9, 2007 12:38 PM