Search code examples
sqlreporting-servicesssasmdx

Adding a filter to a MDX query


I'm new to SSAS and need help with what would be equivalent to a where clause in SQL and I'm sure an easy answer for a MDX regular.

I have this pulling values for a multiple select parameter list in report builder but I need to filter out the contracts based on what project the user is currently viewing. Here is the query that is pulling all the values correctly.

WITH MEMBER [Measures].[ParameterCaption] AS [dimContracts].[ContractName].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [dimContracts].[Contract Name].CURRENTMEMBER.UNIQUENAME 
SELECT {[Measures].[ParameterCaption], 
    [Measures].[ParameterValue]} ON COLUMNS, 
    [dimContracts].[Contract Name].Children ON ROWS 
FROM [cubeProjectEntities]

I need to add what would be equivalant to:

WHERE dimContracts.[Project Id] = 1

I've added which produces the correct filtered set but from here I don't know how to use the report parameter to get it to work. Every time I test it just gives and empty dataset. I have the parameter just printing on the page so I know that it is set correctly.

WHERE [dimContracts].[Project Id].[1]

This does not work:

WHERE [dimContracts].[Project Id].[@ProjectId]

And then in Report builder I will pass a parameter to the query to replace the 1 for @projectId.

Thanks for any help!


Solution

  • WITH MEMBER [Measures].[ParameterCaption] AS [dimContracts].[ContractName].CURRENTMEMBER.MEMBER_CAPTION 
    MEMBER [Measures].[ParameterValue] AS [dimContracts].[Contract Name].CURRENTMEMBER.UNIQUENAME 
    SELECT {[Measures].[ParameterCaption], 
        [Measures].[ParameterValue]} ON COLUMNS, 
        [dimContracts].[Contract Name].Children ON ROWS 
    FROM [cubeProjectEntities]
    Where (STRTOMEMBER(@projectid))
    

    In MDX, the where clause is a slicer. Here's a good article about the differences between SQL and MDX. Here's a link about using SSRS parameters with MDX queries. You'll actually want to pass the entire member name to the query rather than just the value (ex: [DimContracts].[Project ID].[1])