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.