Cascading parameters.

how to set a cascading parameter in the sql reporting(mdx)?

Use the StrToMember method. Using AdventureWorks, if you wanted to limit the list of Product Subcategories from the selected category, @.Category.

Code Snippet

WITH MEMBER [Measures].[ParameterLabel] AS

[Product].[Subcategory].CurrentMember.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS

[Product].[Subcategory].CurrentMember.UniqueName

SELECT {

[Measures].[ParameterLabel],

[Measures].[ParameterValue]

} ON COLUMNS,

NON EMPTY

{ [Product].[Subcategory].[Subcategory].Members }

ON ROWS

FROM [Adventure Works]

WHERE (

StrToMember(@.Category, CONSTRAINED)

)