Sql-server – Create dynamic set conditionally excluding member if exists

mdxsql server

As part of my cube definition I am creating a dynamic set of all claim status' minus the PEND status. This has worked great for years, but now we have someone who does not have the PEND status which is resulting in an error when opening the cube. I would like to create a set that is all Claim status minus PEND if it exists.

The error I am getting is "The level '&['PEND]' object was not found in the cube when the string, '[Claim].[Claim Status].%[PEND], was parsed."

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS [Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].&[PEND];

I tried using except without luck.

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS Except ([Claim].[Claim Status].[All Claims].Children, [Claim].[Claim Status].&[PEND]);

Even tried

Except ([Claim].[Claim Status].[All Claims].Children, StrToSet("[Claim].[Claim Status].&[PEND]"))

Does anyone know how to safely tell if a member exists then exclude it?

Best Answer

IsError to the rescue! Combined with an iif was able to solve it. Thanks to this post for leading me right to it. 

iif (
        isError(
            ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
            )
      , ([Claim].[Claim Status].Children)
      , ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
)
Related Question