SSRS Report For Parameterized Stored Procedure

This article explains how to create a SSRS report using a SQL parameterized Stored Procedure object.

This article explains how to create a SSRS report using a SQL parameterized Stored Procedure object. The main purpose of this article to reduce the extra effort of the SQL developer during various types of report extraction.
 
We will learn the following concepts:
  • How to create a data source in a SSRS report.
  • How to create a dataset in a SSRS report.
  • How to create a parameter in a SSRS report.
  • How to use a Stored Procedure in a SSRS report.
  • How to use a table control in SSRS design and tie the dataset to this table.
How to create a simple SSRS report can be read from here.
 
Note:
  1. Before starting all the processes we need to run VS 2012 as an Administrator.
  2. Go to File -> New -> Project.
  3. Then right-click on the report folder that is in Solution Explorer then go to Add -> New Item.
  4. A new window appears for selecting a report template.
  5. Then we proceed with the following procedure.

How to create a data source in SSRS report

 
Open the Report Data tab from the View option.
 
create data source in SSRS report 
 
Then go to the Report Data tab where we can see various options listed but in this step we will discuss only the Data Source option.
 
create data source in SSRS report 
 
Then right-click on Data Sources then go to Add Data Source.
 
create data source in SSRS report 
 
Here we will create two data sources. Here my database is named Northwind.
  1. ListofCategory
  2. DetailReportdata
Then in this window write the name of the datasource then choose the data source type from the dropdown list then write the connection string that is required for connecting to the database for data extraction purposes.
 
create data source in SSRS report  
 
For the connection string click on the Edit button then configure the connection then from here click OK.
 
create data source in SSRS report  
 
Then in this window we see the connection string in the TextBox then click on the OK button.
 
create data source in SSRS report 
 
Then repeat the preceding process for creation of the DetailReportdata datasource.
 
Here we see two datasources that were created by the preceding process.
 
create data source in SSRS report 
 

How to create Dataset

 
Here we create the following two datasets:
  • Categorylist
  • ListofRecord
Go to the Report Data tab then right-click on the dataset folder then click Add Dataset.
 
create Dataset in SSRS report 
 
Then we can set various dataset properties from this window.
 
Here we click on the Query tab that is at the left side of this window then select radio button. I will use a dataset embedded in my report. Then choose the corresponding datasource from the dropdown list. Here we can see all the datasources that were created in the previous step. Then select the query type option from here. Here I am selecting a text query type then write the query in the TextBox. Here I will write a simple query for selecting two columns from the category table. Then click OK.
 
create Dataset in SSRS report 
 
Then we will create a second dataset (ListofRecord) but initially we need to create a Stored Procedure because here we use as the query type Stored Procedure. Here we create a Stored Procedure that accept two input parameters and simply join the two table's data. You can create a query depending on requirements.
  1. CREATE proc uspDetailreport  
  2.    @Categoryid int=null,  
  3.    @unitsinstock bigint =null  
  4. as  
  5. begin  
  6.    select c.CategoryName,p.ProductName,p.QuantityPerUnit,p.UnitsInStock,p.UnitsOnOrder,p.Discontinued from Products p join Categories c  
  7.    on p.CategoryID=c.CategoryID  
  8. where p.CategoryID=@Categoryid or p.UnitsInStock=@unitsinstock  
  9. end  
Then we repeat the preceding step for dataset creation.
 
In this window I selected the Stored Procedure query type. Then select the corresponding Stored Procedure from the dropdown list that was created in SQL Server.
 
create Dataset in SSRS report 
 
Then click OK.
 

How to create a parameter in SSRS report

 
Go to the Report Data Tab then right-click on Parameters -> Add Parameter.
 
Here I will create two input parameters as in the following:
 
create parameter in SSRS report 
 
Then set the following properties in this window.
 
In the general tab write the name of the parameter and the data type.
 
create parameter in SSRS report 
 
In the Available Values tab choose the available values for this parameter.
 
Here we select the Dataset and the value field and label field for this parameter.
 
create parameter in SSRS report 
 
Then go to the Default value tab of this window and choose one option from here.
 
create parameter in SSRS report 
 
Then go to the Advanced tab of this window and choose an appropriate option available from here.
 
create parameter in SSRS report 
 
Then Click OK.
 

How to use Stored Procedure in SSRS report

 
See in Step 2 here I have already explained how to use a Stored Procedure in a SSRS report.
 

How to use table control in SSRS design and tie the dataset to this table

 
Go to the Toolbox and drag and drop a table control onto the design part of the RDL report.
 
table control in SSRS 
 
Then select table properties and tie the dataset to the table.
 
table control in SSRS 
 
Then bind every column of the table to the corresponding dataset column name.
 
table control in SSRS 
 
Then we will see the preview of this report. Here we will see two input controls on the top of the report.
 
One input is TextBox and another one is a dropdown list. From the dropdown list we select the category type and see the report of the specific category only.
 
table control in SSRS 
 
Then deploy the report to a SQL Server Reporting Service. For deployment of the report see my article here.