MDX Tip of the Month - 01 Aug 1999

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( 
   \[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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.