November MDX Puzzle Solution Revealed - 29 Oct 1999

Imagine that you have a simple grid showing the FoodMart Sales cube. The grid displays the quarters in 1997 across the columns and CA, OR, and WA down the rows. The user clicks CA to drill down. What's the easiest way to formulate this query?

SELECT [1997].Children ON columns,DrillDownMember( {[CA], [OR], [WA] }, { [CA] } )ON rowsFROM Sales

Microsoft designed the MDX function DrillDownMember to meet this common need. The DrillDownMember, according to the OLAP Services product documentation, drills down the members in a specified set that are present in a second specified set. The DrillDownMember takes two sets and inserts the children of each member into Set2 after that member occurs in Set1, as Screen A shows. You can see how the code inserts all the [CA] children into Set1. The resulting set is then [CA], [CA] children, [OR], [WA]. The DrillDownMember and other similar functions (DrillUpMember, DrillUpLevel, DrillDownLevel, etc.) are useful if you need to build a simple OLAP browsing application.