MDX - SQL Server Analysis Services 2012 Cube Development - page 225

Reseller Sales
Amount

Sum for Cat-
egory Products Category

Women's Mountain
Shorts, S

Clothing $3,262.23 $23,549.22

Classic Vest, S Clothing $3,703.37 $23,549.22

5. The next query finds the top-selling product for each category. To do so it
uses the DESCENDANTS function to generate a set of all the products within
the category first, then uses the TOPCOUNT function to identify the product
with the higher reseller sales amount. Recall that TOPCOUNT returns a set—in
this case a set of only one cell. You must use the ITEM function twice; first we
use ITEM(0) to extract the first tuple from the set and then we use ITEM(0)
to extract a single cell / dimension member. The last calculated member with-
in this query obtains the reseller sales amount for the top-selling product:

WITH MEMBER measures.[top selling product
for category] AS TOPCOUNT(
DESCENDANTS([Product].[Product
Categories].CurrentMember, 3),
1, measures.[reseller sales
amount]).item(0).item(0).name
MEMBER measures.[product's reseller sales
amount] AS
SUM(TOPCOUNT(
DESCENDANTS([Product].[Product
Categories].CurrentMember, 3),
1, measures.[reseller sales amount]),
measures.[reseller sales amount])
SELECT [Product].[Product
Categories].category.members ON 0,
{measures.[reseller sales amount],