SSAS (MDX) - Filtering Dataset based on another Dataset

Question

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:

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]
       )

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.

-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 "&" for Object() type.

-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 )

What do I have to do to get this filter expression working... please help anyone :)

@mmarie second dataset is created by stored procedure:

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

@FrankPI - offtopic don't know why, but I used ctrl+k for code, but it`s still regular text :(

Answer 1

If you can change the second query, then this could return the list of allowed members as a set definition string instead of a list of members, using e. g. the SetToStr function. This could then be used in the first query as a parameter replacing the text [Dim Division].[Hierarchy].ALLMEMBERS for the rows by StrToSet(@AllowedDivisionSet, CONSTRAINED).