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.

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.

Data source option

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

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.

datasource

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

connection

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

connection string

Then repeat the preceding process for creation of the DetailReportdata datasource.

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

wo datasource

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.

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.

selecting two columns

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.

dropdown

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:

Add Parameter

Then set the following properties in this window.

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

parameter

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.

Data set and value field

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

Default value

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

advanced

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.

table control

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

table properties

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

column of table

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.

particular category

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

 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now