SSRS Report For Parameterized Stored Procedure

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.

1. How to create a data source in 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 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.



For the connection string click on the Edit button then configure the connection 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 creation of the DetailReportdata datasource.

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



2. 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.



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.



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.

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 that was created in SQL Server.



Then click OK.

3. 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:



Then set the following properties in this window.

In the general tab write the name of the parameter and the data type.



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.



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 available from here.



Then Click OK.

4. 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.

5. 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.



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 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.



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