Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 206

Here, we use the TOPCOUNT function to find the three employees who
have the highest value of the sales amount measure. We use the CHILDREN
function instead of MEMBERS since otherwise the All member will appear in
the first position, as it contains the total sales amount of all employees.

Sales Amount
Margaret Peacock $217,469.14
Janet Leverling $176,515.01

Nancy Davolio $175,837.26

Query 6.6. Best-selling employee per product and year.
WITH MEMBER Measures.[Top Sales] AS
MAX([Order Date].Calendar.CURRENTMEMBER *
Employee.[Full Name].CHILDREN, Measures.[Sales Amount])
MEMBER Measures.[Top Employee] AS
TOPCOUNT([Order Date].Calendar.CURRENTMEMBER *
Employee.[Full Name].CHILDREN, 1, Measures.[Sales Amount]).
ITEM(0).ITEM(1).NAME

SELECT { Measures.[Top Sales], Measures.[Top Employee] } ON COLUMNS,

ORDER(Product.Categories.Product.MEMBERS,
Product.Categories.CURRENTMEMBER.NAME,BASC) *
[Order Date].Calendar.Year.MEMBERS ON ROWS
FROM Sales
The calculated measure Top Sales computes the maximum value of sales
amount for the current year among all employees. The calculated measure
Top Employee uses the function TOPCOUNT to obtain the tuple composed
of the current year and the employee with highest sales amount. The ITEM
function retrieves the first member of the specified tuple. Since such member
is a combination of year and employee, ITEM is applied again to obtain the
employee. Finally, the NAME function retrieves the name of the employee.

Top Sales Top Employee
Alice Mutton 1996 $3,010.80 Andrew Fuller
Alice Mutton 1997 $4,689.75 Steven Buchanan
Alice Mutton 1998 $2,702.70 Nancy Davolio

Aniseed Syrup 1996 $240.00 Robert King

Aniseed Syrup 1997 $800.00 Janet Leverling

Aniseed Syrup 1998 $740.00 Anne Dodsworth

··· ··· ··· ···

Query 6.7. Countries that account for top 50% of the sales amount.
SELECT Measures.[Sales Amount] ON COLUMNS,
{ Customer.Geography.[All],
TOPPERCENT([Customer].Geography.Country.MEMBERS, 50,
Measures.[Sales Amount]) } ON ROWS
FROM Sales