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

In this query, we use the TOPPERCENT function for selecting the countries
whose cumulative total is equal to the specified percentage. We can see in the
answer below that the sum of the values for the three listed countries slightly
exceeds 50% of the sales amount.

Sales Amount
All Customers $1,145,155.86
United States

$238,490.40

Germany $219,356.08

Austria $115,328.31

Query 6.8. Total sales and average monthly sales by employee and year.
WITH MEMBER Measures.[Avg Monthly Sales] AS
AVG(DESCENDANTS([Order Date].Calendar.CURRENTMEMBER,
[Order Date].Calendar.Month),Measures.[Sales Amount]),
FORMAT STRING = ' $###,##0.00 '
SELECT { Measures.[Sales Amount], Measures.[Avg Monthly Sales] } ON COLUMNS,
Employee.[Full Name].CHILDREN *
[Order Date].Calendar.Year.MEMBERS ON ROWS
FROM Sales

In this query, we cross join the Employee and Order Date dimensions to
display the employee name and the year on the row axis. The calculated
measure Avg Monthly Sales computes the average of sales amount of the
current employee for all months of the current year.

Sales Amount Avg Monthly Sales

Andrew Fuller 1996 $20,773.06 $3,462.18

Andrew Fuller 1997 $62,848.74 $5,237.40

Andrew Fuller 1998 $60,591.94 $15,147.99

Anne Dodsworth 1996 $9,894.51 $3,298.17

Anne Dodsworth 1997 $18,099.29 $1,809.93

Anne Dodsworth 1998 $39,803.96 $9,950.99

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

Query 6.9. Total sales amount and total discount amount per product and
month.
WITH MEMBER Measures.[TotalDisc] AS
Measures.Discount * Measures.Quantity * Measures.[Unit Price],
FORMAT STRING = ' $###,##0.00 '

SELECT ON COLUMNS,

NON EMPTY ORDER(Product.Categories.Product.MEMBERS,
Product.Categories.CURRENTMEMBER.NAME, BASC) *
[Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales

{ Measures.[Sales Amount], [TotalDisc] }