April 2003 MDX Puzzle Solution

Find the top 10 drink products based on store sales. For the purposes of this puzzle, a product is a drink product if it's in a product category with the name Beverage or Drink in its title. Format the results so that the top 10 drink products are listed on the rows and the store sales are displayed in a single column.

This problem demonstrates the use of a string search in MDX. In the query that Listing A shows, I created a set called Beverage Categories that contains the product categories that have Beverage or Drink in the category name. Then, I created a second set called Beverage Products that finds all the products that descend from the categories in Beverage Categories. The rest of the query is simple: I just selected Store Sales on columns and the top 10 products from the Beverage Products set on rows.