Monday, September 10, 2012

MDX To Filter Out All System Unknowns

There may be multiple Unknown’s within a given hierarchy, but they are all descendants of the hierarchy’s top-level unknown member.  Thus we can use the Descendants function to get a set of all unknown members within the hierarchy, and then the Except function to filter them out:
SELECT … ON 0,
       Except([
dimension].[hierarchy].,
              Descendants([
dimension].[hierarchy].UnknownMember))
       ON 1
FROM [
cube_name]