April 2002 MDX Puzzle Solution Revealed

In the April puzzle, Paul Goldy and I asked you to try extending the example in "The Non-Visible Level," April 2002, InstantDoc ID 24020, to populate an ALL OTHER BRAND member that's a sibling to each group of brands in the Product dimension. April's column showed how to sum all the suppressed values into one ALL OTHER BRAND level by adding a single record in the Product dimension table and creating a calculated measure to determine the record's value. However, a more useful analysis would sum ALL OTHER BRAND at each brand grouping within the product hierarchy. In other words, instead of having just one ALL OTHER BRAND for the entire Product dimension, you'd have an ALL OTHER BRAND for each product subcategory.

Listing A shows what the MDX for the Suppressed Unit Sales calculated measure would look like if you added an ALL OTHER BRAND record to each product subcategory in the Product dimension table. Before you run the query that Listing A shows, you need to create a hidden dimension called Hidden Store that's a copy of the Store dimension in the Sales cube. For more details about configuring the Sales cube for use with this puzzle, see "The Non-Visible Level."

The initial IIF() function checks to see whether the currently selected product is at the Brand Name level and not at the ALL OTHER BRAND member level. Brand Name is the proper level at which to check for suppression and possibly to sum the suppressed values into ALL OTHER BRAND.

If the first IIF() function yields true, the function counts the number of stores with sales for this product to determine whether the query should suppress the Unit Sales value by returning NULL or display the value by returning Unit Sales. If the IIF() function yields false, the function uses another IIF() function to determine whether the current product is at the ALL OTHER BRAND member level. If this ALL OTHER BRAND check yields true, the check executes a SUM() function that sums all the suppressed values across descendants of the current member's parent at the Product Name level. The optional Numeric Expression of the SUM() function is complex:

IIF(IIF(COUNT(CROSSJOIN(\{\[Hidden Store\].\[Store Name\].Members\},
\{(Store.CurrentMember,Measures
.\[Unit Sales\])\}),
EXCLUDEEMPTY)<3, Measures.\[Unit Sales\], NULL),Measures.\[Unit Sales\],0)

This section of the code tests whether to include or exclude the value. The code performs this test by checking to see whether the number of stores reporting is less than three. If so, the code returns Unit Sales, which flags the value for inclusion; if not, the code returns NULL, which flags the value for exclusion. The inclusion or exclusion has the end result of returning either Unit Sales or a 0 to the SUM() function.

The final part of the MDX, which is used if the initial IIF() function is false, is executed when you're at a level of the Product dimension that isn't the Brand Name level:

IIF(Count(CROSSJOIN(\{\[Hidden Store\].\[Store Name\].Members\},
\{(Store.CurrentMember,Measures
.\[Unit Sales\])\}),
EXCLUDEEMPTY)>2, Measures.\[Unit Sales\], NULL))

This check duplicates the suppression rule that determines whether the code should show or not show the Unit Sales value regardless of where you are in the Product dimension. If you have a more elegant solution to this puzzle, please send it to me at [email protected].