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 :(

@ArturChmielewski To format code blocks, do not include them in backticks, but indent them by at least four spaces. Including in backticks is meant for in-text code. — FrankPl

Frank Goortani Frank Goortani · Accepted Answer · 2013-09-10T15:56:52

If the result sets are not huge, the easy way is to do this is with a filter on the Dataset and NOT in MDX:

Add a Boolean filter in which the Expression is =True and the Value is something like this:

=iif(instr(join(Parameters!DataSet2Parameter.Value,","),
Fields!ToDimDateHierarchy.Value)=0,True, False)

SSAS (MDX) - Filtering Dataset based on another Dataset

2 Answers