SSRS Report For Parameterized Stored Procedure

Introduction

This article explains creating an SSRS report using a SQL parameterized Stored Procedure object. The main purpose of this article is 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 an SSRS report.
• How to create a dataset in an SSRS report.
• How to create a parameter in an SSRS report.
• How to use a Stored Procedure in an 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 here.

Note

1. Before starting the process, we need to run VS 2012 as an Administrator.
2. Go to File -> New -> Project.
3. Then right-click on the report folder in Solution Explorer and 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 the SSRS report?

Open the Report Data tab from the View option.

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.

Then right-click on Data Sources then go to Add Data Source.

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 data source, choose the data source type from the dropdown list, and write the connection string required for connecting to the database for data extraction purposes.

For the connection, string click on the Edit button, then configure the connection,n then from here, click OK.

Then in this window, we see the connection string in the TextBox, then click on the OK button.

Then repeat the preceding process for the creation of the DetailReportdata data source.

Here we see two data sources that were created by the preceding process.

How to create a Dataset?

Here we create the following two datasets.

• Categorylist
• ListofRecord

Go to the Report Data tab, right-click on the dataset folded,r then click Add Dataset.

 

Then we can set various dataset properties from this window.

Here we click on the Query tab at the left side of this window, then select the 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 created in the previous step. Then select the query type option from here. Here I select a text query type and then write the query in the TextBox. I will write a simple query for selecting two columns from the category table. Then click OK.

Then we will create a second dataset (ListofRecord), but initially, we need to create a Stored Procedure because here we use the query type Stored Procedure. Here we create a Stored Procedure that accepts two input parameters and joins the two table's data. You can create a query depending on requirements.

CREATE proc uspDetailreport  
   @Categoryid int=null,  
   @unitsinstock bigint =null  
as  
begin  
   select c.CategoryName,p.ProductName,p.QuantityPerUnit,p.UnitsInStock,p.UnitsOnOrder,p.Discontinued from Products p join Categories c  
   on p.CategoryID=c.CategoryID  
where p.CategoryID=@Categoryid or p.UnitsInStock=@unitsinstock  
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 created in SQL Server.

Then click OK.

How to create a parameter in the SSRS report?

Go to the Report Data Tab and right-click on Parameters -> Add Parameter.

Here I will create two input parameters as in the following.

Then set the following properties in this window.

Write the parameter's name and the data type in the general tab.

In the Available Values tab, chooses the available values for this parameter.

Here we select the Dataset, value field, and label field for this parameter.

Then go to the Default value tab of this window and choose one option from here.

Then go to the Advanced tab of this window and choose an appropriate option from here.

Then Click OK.

How to use Stored Procedure in SSRS report?

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

How can table control be used in SSRS design and tying the dataset to this table?

Go to the Toolbox and drag and drop a table control onto the design part of the RDL report.

Then select table properties and tie the dataset to the table.

Then bind every column of the table to the corresponding dataset column name.

Then we will see the preview of this report. Here we will see two input controls at 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.

Summary

Then deploy the report to a SQL Server Reporting Service. For deployment of the report,t see my article here.

Reference 

• Stored Procedure in SQL Server