MDX Filtering dimension members with result of other dimension

Question

I would like to filter a dimension for cube security with some information that are in another dimension.

So - I have a dimension which holds some account Responsible (Account Number and the initials on the one responsible) and another Dimension with all accounts. I would like to make sure, that a person only can see movements on the accounts on which they are responsible.

I can make the filtering work like this:

SELECT 
  {} ON 0
 ,{
    Exists
    (
      Filter
      (
          [Accounts].[Accounts].[AccountNo]
        * 
          [AccountResponsible].[AccountResponsible].[AccountNo]
       ,
          [Accounts].[Accounts].Properties("key")
        = 
          [AccountResponsible].[AccountResponsible].Properties("key")
      )
     ,[AccountResponsible].[Responsible].&[MSA]
    )
  } ON 1
FROM mycube;

the problem is, that there are two columns, and I can't use that in cube security. Is there a way to rewrite this, so that I actually get only one column with the members that the user are allowed to see?

Answer 1

Try using the Extract function:

SELECT 
  {} ON 0
 ,

 EXTRACT(
 {
    Exists
    (
      Filter
      (
          [Accounts].[Accounts].[AccountNo]
        * 
          [AccountResponsible].[AccountResponsible].[AccountNo]
       ,
          [Accounts].[Accounts].Properties("key")
        = 
          [AccountResponsible].[AccountResponsible].Properties("key")
      )
     ,[AccountResponsible].[Responsible].&[MSA]
    )
 }
 ,[Accounts].[Accounts]  //<<HIERARCHY YOU WISH TO EXTRACT
) ON 1
FROM mycube;