none
Members, WHERE in Filter MDX Formula RRS feed

  • Question

  • I'm trying to create a filter that will return all past weeks with data contained in that week, and will display the current week as the default and order them in the list going back from there.  I cannot use Time Intelligence because I need to display each week in a list.  The current formula being used is:  NonEmpty(Order([Date].[Insphere].[Week].AllMembers,
        Rank([Date].[Insphere].CurrentMember,
            [Date].[Insphere].[Week].AllMembers),
            BDESC),
        [Scenario].[Scenario Name].[Actual])

     

     

    This query runs pretty slow due to the Rank() function.  I have written an MDX query that will return the intended results, but it includes a WITH MEMBER statement and a WHERE clause (see below).  Is there any way to manipulate this query to fit within the bounds of an PerformancePoint MDX formula?

     

    WITH Member [Measures].[OrderBy]

    AS

    SUM (([Date].[Insphere Week]. firstSibling :[Date].[Insphere Week]. CurrentMember ,[Annual Premium]))

    SELECT {

    NonEmpty (

    Order

            (

            ([Date].[Insphere].[Week].AllMembers ),

            [Measures].[OrderBy],

            BDESC

            )

    ,  

    [Scenario].[Scenario Name].[Actual]

    )

    }

    DIMENSION PROPERTIES MEMBER_TYPE

    on 0

    FROM [InsphereDW]

    WHERE [Scenario].[Scenario Name].[Actual]

     

    Monday, March 7, 2011 6:24 PM

Answers

  • You don't have to use the RANK function if you just want to sort the members in DESC order. Use the keys for the Week level members to sort.

    An example from AdventureWorks, where the Months are sorted in DESC order (here the key at Month level consists of year and month)

    Order([Date].[Calendar].[Month].Members,
    [Date].[Calendar].CurrentMember.Properties("Key0") + vba!right("0" + [Date].[Calendar].CurrentMember.Properties("Key1") ,2), BDESC)


    -Remember to mark as helpful/the answer if you agree with the post.
    Monday, March 7, 2011 10:10 PM

All replies

  • You don't have to use the RANK function if you just want to sort the members in DESC order. Use the keys for the Week level members to sort.

    An example from AdventureWorks, where the Months are sorted in DESC order (here the key at Month level consists of year and month)

    Order([Date].[Calendar].[Month].Members,
    [Date].[Calendar].CurrentMember.Properties("Key0") + vba!right("0" + [Date].[Calendar].CurrentMember.Properties("Key1") ,2), BDESC)


    -Remember to mark as helpful/the answer if you agree with the post.
    Monday, March 7, 2011 10:10 PM
  • Perhaps this would work:

    ORDER ( nonempty(
        [Date].[Insphere].[Week].members,  [Scenario].[Scenario Name].[Actual]
      )
    , [Date].[Insphere].[Week].CurrentMember.MemberValue , desc)

    Assuming you have SSAS time intelligence as time dimension.

    MDX Date dimension in Descending Order

    http://dailyitsolutions.blogspot.com/
    Wednesday, March 9, 2011 10:21 AM