Friday, July 1, 2011

Interesting MDX on MSDN Forum

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

No comments:

Post a Comment