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