Passing a Multi value TSQL Parameter to MDX Report

I am assuming that you are familiar with the basic report design, So in below post, I am going to give details regarding the use multi value parameter only.

I have shared .RDL with respect to this post Download

Steps to pass multivalue parameter to MDX SSRS report

1.    Create your basic report with MDX query.

2.    I am using Adventure works DW cube as datasource to describe the below steps.

3.    Go to the .rdl code and write your code between <CommandText> your MDX Query </CommandText> tag.

<CommandText>= "Select {"+

"[Measures].[Average Unit Price],[Measures].[Order Quantity],[Measures].[Total Product Cost]"+

"} on columns ,"+

"Nonempty ([Sales Territory].[Sales Territory Country].[Sales Territory Country],[Measures].[Internet Order Count])*"+

"Nonempty( [Sales Territory].[Sales Territory].[Region],[Measures].[Internet Order Count])*"+

"Nonempty( [Product].[Product].[Product],[Measures].[Internet Order Count])"+

"on rows from"+

"(SELECT({[Sales Territory].[Sales Territory].[Region].&amp;["+Replace(Join(Parameters!SalesRegion.Value,"],")+"]",",",",[Sales Territory].[Sales Territory].[Region].&amp;[")+"})on columns "+

" from [Adventure Works])"</CommandText>
Change the higlighed expression as per your requirement for parameter passing 

4.    If you are using expression based query, in that case report designer will not add required parameter list automatically. You need to add it manually. In our example, the parameter list will like below image.

Let’s describe above picture.

Parameter

1.    SaleRegion is parameter we have used in our MDX .(1)

2.    Product parameter which is taking TSQL query to pass the parameter value to our MDX.  (2)

3.    Go to the parameter properties and  Allow multiple value

Data sources

4.    MultiVlaue associated with Cube (3)

5.    SQL associated with TSQL. (4)

Data Set

6.    Multivalue to MDX (5)

7.    SQL Value is for  TSQL (6)

 SQL Value query I have used is

SELECT DISTINCT SalesTerritoryAlternateKey, SalesTerritoryRegion

FROM            DimSalesTerritory

you go to the product parameter (1) and map the query value to this parameter.

1. Choose to set the Available Values.

2. Specify Dataset from value is coming

3. Choose the value that we are going to pass our MDX

4. Specify the label name.

5. Now preview your report.