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\])