MDX Tip of the Month - 01 Jul 1999

Last month, we showed you one way to determine what percentage contribution individual pieces of a group have made. With the Sales cube in the FoodMart database sample, use the query in Listing A to analyze \[Store Sales\] as a percent of a group in the Product dimension, as Screen A shows.

Unfortunately, the member definition doesn't work very well if you navigate to the top of the product hierarchy such as \[Product\].\[All Products\]. Navigating to the top of the product hierarchy creates a division by zero error because the \[All Products\] level doesn't have a parent level.

To improve the percent of group measure defined in Listing A so that it returns 100 percent when the current member of the product dimension is the top member, try the query in Listing B. This query introduces a new MDX function, IIF, to our calculated member expression. The IIF function, an IF clause, will be familiar to many programmers because most languages support a version of it. In this case, you're checking whether the Product level is at the \[All Products\] level by checking the ordinal property. You're at the products root level, or the \[All Products\] level, if Product.CurrentMember.Level.Ordinal = 0. See Screen B.

(Remember, you can't run MDX expressions from an ISQL/w or Query Analyzer window. Many third-party front ends let you pass MDX to an OLAP Services box. If you don't have a third-party tool, use the MDX Sample Application installed by default in OLAP Services. We generated the screen shots we're using in this column with the MDX Sample Application.)