SSRS Tutorial Part 89- CasCading Parameters with Dynamic SQL in Stored Procedures in SSRS Report

In this video of SSRS Series, we are going to work on Cascade Parameter. I got this question from one of the visitor.

here is the scenario
He would like to provide facility to his users to choose the Column name from the Drop down( Parameter). Once the Column name is chosen then Next parameter should show values available in the table for that column ( Cascaded Parameter). The user should be able to choose multiple values if he/she like. Once user choose the values for parameter, He/She should be able to see the results as per values provided in the parameters.

Let's create the Main Query with two Parameters

-- Main Query 
  CREATE  PROCEDURE dbo.MainQuery
  @ColumnName VARCHAR(100),
  @Values VARCHAR(MAX)
  AS 
  BEGIN
  IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
    DROP TABLE ##Temp
  DECLARE @Val VARCHAR(MAX)
  SET @Val=Replace(@Values,',',''',''')

  DECLARE @SQL NVARCHAR(MAX)
  SET @SQL='Select SalepersonFName,City,State,Country,Region into ##Temp from  dbo.TotalSale where '+@ColumnName+'
  in ('''+@Val+''')'
  PRINT @SQL
  EXECUTE (@SQL)
  SELECT * FROM ##Temp
  END

  --Test your SP for Main Query
  EXEC dbo.MainQuery 'Country','Pakistan,India'

Stored Procedure for the Values Parameter ( Cascaded Parameter)

 -- Multi Value Parameter 
  Create PROCEDURE dbo.ColValues
  @ColumnName VARCHAR(100)
  AS 
  BEGIN

  IF OBJECT_ID('tempdb..##TempValues') IS NOT NULL
    DROP TABLE ##TempValues
  DECLARE @SQL NVARCHAR(100)
  SET @SQL='Select '+@ColumnName+' As val into ##TempValues from dbo.TotalSale'
  EXECUTE (@SQL)
  SELECT DISTINCT * FROM ##TempValues
  END

--Test your SP for Multiple values
  EXEC dbo.ColValues 'Country'

Query for ColumnName parameter

--Column Name Parameter Query
  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_Name='TotalSale'

What we will learn while creating this video for Cascade Parameter report by using Stored Procedures
How to create Stored Procedures with Dynamic SQL
How to use Stored Procedure in SSRS for DataSet
How to use Stored Procedure for Parameters
How to create Cascading Parameter in SSRS Report

How to create Cascade Parameters in SSRS Report - SQL Server Reporting Services(SSRS) Tutorial