October MDX Puzzle Solution Revealed - 01 Nov 1999

How can you write a Multidimensional Expression (MDX) query that returns the Unit Sales for the months of 1997 on columns and the top product brand names that account for 25 percent of Unit Sales for 1997? The most difficult part of this query is figuring out exactly what you're asking for. The first part of the query asks for Unit Sales for the months of 1997. You can use a straightforward query such as:

SELECT Descendants(\[1997\], \[Month\]) on Columns
FROM Sales
WHERE (\[Unit Sales\])

In this query, the Descendants function returns all the time dimension members descending from \[1997\] at the \[Month\] level. In other words, it gets the grandchildren of 1997 because the \[Month\] level is two levels below \[1997\].

Now you need to answer the second part of the question: "How do you get the product brand names that account for 25 percent of Unit Sales for 1997?" This is where the TopPercent MDX function comes in handy. The OLAP Services documentation says the TopPercent sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.

   TopPercent(   Set   ,   Percentage   ,   Numeric
   Expression   )

You pass in all the Brand Name dimension members and find the top 25 percent based on 1997 Unit Sales:

SELECT Descendants(\[1997\], \[Month\]) on Columns,
   TopPercent( \[Product\].\[Brand Name\].Members, 25,
   \[1997\]) on Rows
FROM Sales
WHERE (\[Unit Sales\])
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.