SSRS Report using Stored Procedure with Multi Value Parameter

This SSRS article shows how to create a report using a Multi Value stored Procedure that accepts multiple values as a parameter. For instance, stored procedures display sales by country, with the parameter that accepts multiple country names.

Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the report data set that we use for this SSRS Multi Value Stored Procedure with Parameter example.

• Create a Report using Stored Procedure
• Create a Report using Stored Procedure with a Parameter
• Table Report

The Sql stored Procedure that we used above SSRS example is shown below. If you observe the below Stored Procedure, it has a where condition with the @Country parameter.

USE [AdventureWorksDW2019]
GO
 
CREATE PROCEDURE [dbo].[SP_MultiValueCountrySales]
@Country VARCHAR(100)
AS
BEGIN
      SET NOCOUNT ON;
	  DECLARE @Str VARCHAR(MAX)
	  SET @Str = REPLACE(@Country, ',', ''',''')
	  DECLARE @Statement NVARCHAR(MAX)
	  SET @Statement = 
		'SELECT Geo.EnglishCountryRegionName AS Country, 
			Geo.StateProvinceName AS State,Geo.City,
			SUM(Fact.OrderQuantity) AS Orders, 
			SUM(Fact.TotalProductCost) AS ProductCost, 
			SUM(Fact.SalesAmount) AS Sales, 
			SUM(Fact.TaxAmt) AS Tax INTO ##CountryTemp
		FROM FactInternetSales AS Fact  INNER JOIN DimCustomer 
			ON DimCustomer.CustomerKey = Fact.CustomerKey
			INNER JOIN DimGeography AS Geo ON DimCustomer.GeographyKey  = Geo.GeographyKey
		WHERE Geo.EnglishCountryRegionName IN ('''  + @Str +  ''')
		GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City'
	EXECUTE( @Statement)
	SELECT * FROM ##CountryTemp
	DROP TABLE ##CountryTemp
END

Once you choose the stored procedure name, click the refresh fields to open the define query parameters window. Here, add or pass parameter value and click ok. It is very important; if you forget to click the refresh fields and give value, you won’t see any column in Report Data to design the report.

Create a SSRS Report using Stored Procedure with Multi Value Parameter

The below image shows the Stored Procedure’s available columns and the auto-created Country Parameter. But first, let me create another data set to bring the distinct country names to use them as the @Country parameter available fields.

SELECT DISTINCT EnglishCountryRegionName 
FROM DimGeography

Double-click @Country to open the Report Parameter Properties window. First, checkmark the Allow multiple values option.

Next, go to the Available Values tab, select Get values from a query option, and choose the CountriesList DataSet, Value, and label field.

We have designed a simple table report of sales by country and formatted the font and colors.

If you go to the report preview, it shows the dropdown list to choose the countries. For example, let me select Canada to display Canada’s Sales.

We can also choose multiple values as the parameter, and the report works.