MDX Tip of the Month

Do you know how to write an MDX query that returns non-numeric results? Here is an example of how useful this capability can be. If you list your company's top 10 products based on unit sales, wouldn't you like to see a mark next to the products that make a low profit? The following MDX query demonstrates how you can create a calculated member that results in string values rather than numeric measure values.

WITH MEMBER [Measures].[Low Profit] as 'iif( 
   ([Product].CurrentMember, 
   [Profit]) < 300, "!!!", "")'
SELECT { [Unit Sales], [Low
   Profit] } ON COLUMNS,
 TOPCOUNT( [Product].[Product
   Name].Members, 10, [Unit
   Sales] ) ON ROWS
FROM [Sales] 

The query compares the current product's profit against 300. If profit for a product is less than 300, you see "!!!". Otherwise, you see an empty string. The result is in Screen A.