SQL Server Reporting Services - Example for creating report

In this post, I am going to explain about :

1.)How to Open SQL Server Business Intelligence  Development Studio

2.)How to create new project for SSRS report

3.)How to add datasets in the form of query & stored procedure

4.)How to add parameter for report input & map it with parameter of dataset.

5.)How to design report & export it.

Step 1 : 

In windows, goto “All programs”,under Microsoft SQL Server 2008 R2, select  SQL Server Business Intelligence  Development Studio

Step 2: 

Create new project as shown in below image

Step 3:

Select Report server Project and mention [Name,Location..] for project as shown in below image

Step 4:

Create report under the project we have created as shown in below image.

We can have multiple reports under single project.

Step 5:

Create Shared Data Sources as shown in below image.

In this example, Shared Data Sources is nothing but SQL Server database.

Enter server name,database name & mode of logon by clicking edit under connection string.

If you create Shared Data Sources/Shared Datasets under solution explorer, it means you can use that datasource / resultset for all reports under that project.

Step 6:

Under parameters, by right-clicking parameters we can create parameters for report.

In this example, I have created parameters for getting input for report.

Step 7:

What is dataset?

Dataset is just a result set from database or other datasource

In this example, I have explained about query dataset & stored procedure dataset.

Example for Query dataset:

Create dataset using below query as shown in below image.

--query resultset
SELECT TOP 5 name,type,type_desc FROM sys.objects WHERE type = @report_input

 

 

Step 8 :

Map the parameters of report with dataset parameters.

So that when you enter value for report parameter, it will get the resultset based on parameter value from the dataset.

Step 9:

Example for Stored procedure dataset:

Create the Stored procedure in the database mentioned in Connection string.

--Stored procedure resultset

CREATE PROCEDURE Procedure_dataset
AS
SELECT TOP 5 name,type,type_desc FROM sys.objects

Step 10 : 

Create stored procedure dataset by calling stored procedure from where you have created as shown in below image.

Step 11:

In the below image you can see Design & Preview tab.

Design tab is for designing the report.

Preview tab is for viewing the report.

 

Step 12: 

Select Toolbox from View menu, drag textbox for title of report, drag  two Table from Toolbox (one for query dataset & one for procedure dataset) and put it in the body of report as shown in image:

Step 13: 

Now drag & drop, columns from Datasets into respective Tables in the body of report.

Step 14: 

Goto Preview tab and give input for report and then click view Report.

You can notice in above image, report is getting loaded.

Data from ReportServer Tempdb is rendered and displayed through report, this is called “Rendering in SSRS”.

Step 15:

You can export generated report to PDF,EXCEL … as shown in below image.

See Also:

• My posts on SSRS