title: SSAS (MDX) - Filtering Dataset based on another Dataset
body: <p>I have a mdx report on a cube. As available values for division number report parameter I have an autogenerated dataset (dataset1, with all divisions in company) which query looks like this:</p>
  
  <pre><code>WITH MEMBER [Measures].[ParameterCaption] AS
              [Dim Division].[Hierarchy].CURRENTMEMBER.MEMBER_CAPTION
       MEMBER [Measures].[ParameterValue] AS
              [Dim Division].[Hierarchy].CURRENTMEMBER.UNIQUENAME
       MEMBER [Measures].[ParameterLevel] AS
              [Dim Division].[Hierarchy].CURRENTMEMBER.LEVEL.ORDINAL
  SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]}
         ON COLUMNS ,
         [Dim Division].[Hierarchy].ALLMEMBERS
         ON ROWS
    FROM ( SELECT ( STRTOMEMBER(@FromDimDateHierarchy, CONSTRAINED) : STRTOMEMBER(@ToDimDateHierarchy, CONSTRAINED) )
                 ON COLUMNS
            FROM [ArveCubeBiceps]
         )
  </code></pre>
  
  <p>I have another dataset (dataset2), which consists of only those division numbers, which logged in user is supposed to be able to view (data supplied by a procedure). I need to filter dataset1 available values by the values of dataset2.</p>
  
  <p>-I can't just use the dataset2 as available values for division number, since report query is MDX, and expects tuples, which dataset2 doesn't provide. When I try to concatenate dataset2 values into tuples, I get an error that I can`t use "&amp;" for Object() type.</p>
  
  <p>-When I create a hidden parameter on report, which available and default values are those from dataset2, and then apply a filter expression to dataset1 query, which says "DivisionID in @hidden_parameter" - I get no available values for the Division parameter ( I checked the tables, all data is there )</p>
  
  <p>What do I have to do to get this filter expression working... please help anyone :)</p>
  
  <p>@mmarie
  second dataset is created by stored procedure:  </p>
  
  <pre><code>USE [Biceps]  
  GO  
  SET ANSI_NULLS ON  
  GO  
  SET QUOTED_IDENTIFIER ON  
  GO  
  ALTER PROCEDURE [dbo].[ArveDivisionsByUserID]   
      @ldapuid nvarchar(30)  
  AS  
  BEGIN  
  declare @hier as hierarchyid  
  declare @managerID as int  
  
  select @managerID = em.employeeID from Dim_Employee2 em where em.ldapuid = @ldapuid  
  
  select @hier = dv.hier from Dim_Division dv where dv.managerID = @managerID and hier is   not null order by hier desc  
  
  select divisionID from Dim_Division dv where hier.IsDescendantOf(@hier)=1 order by hier  
  
  END
  </code></pre>
  
  <p>@FrankPI - offtopic
  don't know why, but I used ctrl+k for code, but it`s still regular text :(</p>