Select the product you need help with

INF: MDX: How to Query for the 10 Best Customers and the Top 5 Products for Each Customer

Article ID: 304386 - View products that this article applies to.

This article was previously published under Q304386

SUMMARY

A common question for any business is "Who are our 10 best customers and what are the 5 top products they purchase"? This article shows you how to use a Multidimensional expression (MDX) query, which answers the preceding question, by using a Named Set.

Back to the top | Give Feedback

MORE INFORMATION

The following is a sample query against the FoodMart 2000 database that is provided with Microsoft Analysis Services 2000. You can run the following query in the MDX sample application that is also provided with Analysis Services.

--The top 10 customers can be defined in a named set, using the TopCount
--function to limit the set and to return the names of the customers with
--the 10 highest Units Sales for the year 1997. 

WITH SET Top10Cust AS 
'TopCount( { [Customers].[Name].Members }, 10, ([Time].[1997], [Unit Sales]) )'

--Then use the Generate and CrossJoin functions in the body of the query
--to combine the Top10Cust Named Set with the top 5 products for those
--customers. 

SELECT { [Time].[1997] } ON COLUMNS , 
Generate( {Top10Cust}, CrossJoin( {Customers.CurrentMember}, 
TopCount([Product].[Product Name].Members, 5, ([Time].[1997], [Unit Sales])))) ON ROWS 
FROM [Sales]

REFERENCES

For more information about the Generate, CrossJoin, and TopCount MDX functions as well as Named Sets, refer to Microsoft SQL Server 2000 Books Online.

Back to the top | Give Feedback

Properties

Article ID: 304386 - Last Review: February 21, 2007 - Revision: 2.2

APPLIES TO

Microsoft SQL Server 2000 Analysis Services

kbinfo KB304386

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback