Multi Valued Parameters in SSRS

Friends,

We discussed a lot about Singled Valued parameters here. Please fo through that post before jumping into this. If the user can select only one value from the parameter list then that is called Single Valued Parameters and if the user is able to select More than one then we call it as Multi Valued Parameters. First, Let us see how to add a Single Valued parameter to the report and then convert it to Multi Valued one. So, let’s jump into the topic –

First I created a report(Tabular) using the following query –

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName, FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey

At this point of time there are no Parameters defined and hence you will get all the data from the database and will be displayed in the report. PFB the steps to be followed to ADD PARAMETERS to your report –

• Right click on Datasource and select “Add Dataset” option.

• Select the data source and provide the query which pulls values for parameter and as I am adding “Products” as parameter I am using the following query.

select ProductKey,EnglishProductName as “Products” from dimproduct

• When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
• Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Chnage the default value given for NAME and PROMPT if required.

• Now select the “Available Values” tab which is on the Left hand side of the wizard and select the option “Get Values From a Query”. Provide the Dataset name,Value Field and Label Field as shown below –

• Click OK and then edit the MAIN DATASET to add this parameter to the query. For that double click on Main dataset “Dataset1” in this case and modify the query with the given one below.

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,
FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight
FROM DimProduct INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where DimProduct.ProductKey = @ReportParameter1

• Now Select Parameters tab and map the parameters as shown below.

• Click on OK and your report is ready. Go to preview tab and test you report. You can see Report data get filtered as shown below  but the problem is at a time ONLY ONE Value can be selected as parameter.

• To make is a Multi-Valued parameter double click on the parameter and check the option “Allow Multiple Values” as shown below.

• Now if you preview the report, it will let you select multiple values but on submit it will throw error and pfb screenshot of the same.

• This is because we are passing MULTI VALUES to the query but the query condition is “=” which expects a scalar value. To solve this issue go to the Main dataset query and change the query as given below.

SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,
FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight
FROM DimProduct INNER JOIN
DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
Where DimProduct.ProductKey in (@ReportParameter1)

• Now preview the report by selecting multiple values and pfb the screenshot of my sample report output.

There it you .. The required output. For multiple parameters see the blog post Cascading parameters here.

That’s it .. Hope you understood .. Happy Coding !!

Regards,

Roopesh Babu V