Friday, November 5, 2010

MDX Top N with Remainder every Year

This is some good stuff. I tried a bit on this but was lost in mid way. This was coolly solved by HrvojePiasevoli in the MSDN forums. Thought of sharing this and may be this will be useful for few more.

Learnt Extract, Union and many more.
The requirement here is to Display the Top N sales, remainder and total for each year


with
member [Product].[Product].[AllProducts] AS [Product].[Product].[All Products]
,BACK_COLOR ="&H0000D00D"

MEMBER [Product].[Product].Remainder
AS Aggregate(
        [Product].[Product].[Product].MEMBERS -
        EXTRACT(
                  TOPCOUNT(
                        [Date].[Fiscal Year].CURRENTMEMBER *
                        NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                        3,
                        [Measures].[Internet Sales Amount]
                        )
        ,[Product].[Product])
            ), BACK_COLOR ="&H0000FFFF"
           
set TopSetWithReminder as
Generate(
    [Date].[Fiscal Year].[Fiscal Year].MEMBERS,
    UNION(
            TOPCOUNT
            (
                  [Date].[Fiscal Year].CURRENTMEMBER *
                  NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                  3,
                  [Measures].[Internet Sales Amount]
                  ),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].Remainder),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].[AllProducts] )
            )
    )

SELECT
{
      [Measures].[Internet Sales Amount]
      ,[Measures].[Internet Gross Profit]
} ON 0,
TopSetWithReminder ON 1
FROM 
[Adventure Works]
CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

LinkWithin

Related Posts with Thumbnails