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.

DataSet

The Sql stored Procedure that we used above SSRS example is:

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

If you observe the above Stored Procedure, it has a where condition with the @Country parameter.

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.

Refresh Fields

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
New DataSet

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

SSRS Report using Stored Procedure with Multi-Value Parameter 4

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

SSRS Report using Stored Procedure with Multi-Value Parameter Available Values

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

Table Report

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.

SSRS Report using Stored Procedure with Multi-Value Parameter Preview

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

SSRS Report using Stored Procedure with Multi-Value Parameter Preview 2