| Downloads |
|---|
| 6025.zip |
We think this MDX puzzle might be our best yet. The puzzle is a common problem with a simple solution. What makes it great is that you really need MDX to solve it. Very few, if any, front-end applications let you formulate this question in this solution's simple and elegant manner. The question is "What is the top drink product for each quarter in 1997?" Listing A contains the answer.
This query uses a couple of MDX tricks. In Mastering OLAP, "Member Properties" (August), we introduced the ability to create a calculated member that returns a string instead of a numeric value. Use this capability with the TopCount function to return the name of the leading drink product of the current period by Store Sales. TopCount is an MDX function that returns a number of the top members based on criteria you specify. Here, you ask for the top one member, measured by Store Sales of the current period.
TopCount( Set , Count \[, Numeric Expression \])
This statement sorts a set according to the value of Numeric Expression and returns the top Count members, where Count is a numeric expression.
Now, what is that Item(0).Item(0) business in Listing A after the TopCount? Here lies the second trick in the query. The TopCount function returns a set of tuples (a tuple is a set of members, each from a different dimension). By the way that you formulate the TopCount function, you know that it returns only one tuple with one member, so you can use the Item property to get direct access to that member. Select the first tuple in the set, then select the first member in the tuple. Then you can access the name of the member instead of its value by adding the Name property. Pretty cool, huh?
Now for the straightforward part: The columns in the table enumerate the quarters of 1997, and the rows list the new measure. Listing the quarters on the columns causes the new calculated measure to be evaluated for each quarter in 1997. The result is in Figure A.
