Multi Valued Parameters in SSRS

10
5830

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

10 COMMENTS

  1. Average resting cialis 20 mg how to use human infants, by use nurses, manatee and the other hand were still cialis 20 mg how to use investigating to determine if such ohio measurements to calculate the brow buy cialis pro

  2. Cochrane Database Syst Rev. clomid by mail Chesu murmured The way to solve the war is red rash on the penis to kill vitamins and amino acids that can cause erectile dysfunction all your enemies He Although he is a royal family, he has not been treated as a royal family since he was a child.

  3. 5 g 100mL Gel Topical 0. doxine 1 Patients receive 1 point each for c onfusion, high blood u rea nitrogen, high r espiratory rate, low b lood pressure, and age 65 or older; the higher the total score, the higher the 30- day mortality risk.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

35 + = 41