February MDX Puzzle Solution Revealed - 04 Feb 2000

How do you write an MDX query so that the cells you can't access return empty (null) values instead of #N/A?

WITH MEMBER \[Measures\].\[Unit Sales1\] AS 'iif(\[Unit Sales\] < -222222222,
   Null, \[Unit Sales\])'
SELECT \[Time\].\[Year\].Members ON Columns, \{\[Unit Sales1\]\} ON Rows
FROM Sales 

The answer: Check for small negative values. This formula is handy when you want to use the NON EMPTY clause to remove rows or columns that contain values that you don't have permission to view.

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.