March MDX Puzzle Solution Revealed - 27 Mar 2002

Retail businesses sometimes evaluate their stores' performance by analyzing revenue per square foot or cost per square foot. Use the FoodMart 2000 Sales cube to determine what the total store square footage is for each Store State (Store State is a level in the Store dimension). Note that what makes this problem unique is that square footage is stored as a member property in the Store dimension.

Listing A shows a query that solves this problem. This query is interesting because it demonstrates how to aggregate (sum) a numeric member property. The query creates a new measure that returns the value of the Store Sqft member property. If the selected store member is above the Store Name level in the dimension, the query sums all store square footage values below the selected member in the hierarchy to determine the square footage value. Because the MDX treats member properties as strings, the query uses the VBScript function VAL() to convert the member property from a string to a number before summing all the store square footage values.