Intresting Question on MDX !

  • Article

There was an Interesting MDX Query Which i cam through in MSDN Forum Posted by Piasevoli thought its worth Sharing with All.
.

 
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