0

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?

1

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])
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.