How To Create Simple SSRS Report

Tools required for SSRS report

  1. Visual Studio with SQL Server Data Tool.
  2. SQL Server.

The steps given below are required to create an SSRS report.

Open Visual Studio => File = > Project.

SQL Server

Select Reporting Services = > Report Server Project = > Set Location & Project Name, then click the OK button. 

SQL Server

Select Solution Explorer

SQL Server

The folders will be available there.

  1. Shared Data Sources
    Shared Data Sources allow us to use the same data source for all the reports within the project, including configuration details (E.g., SQL connection string). The Data Source contains multiple Datasets.
    • Shared Datasets
      The purpose of Shared Datasets is that allows us to use the same dataset for all reports within the project.
    • Reports
      Where can we add our report file?

Hence, first, we will add one Shared Data Source.

Right-click on Share Data Source => Add New Data Source.

SQL Server

The Window given below will be opened, followed by renaming the Data Source name (e.g., SharedDSource).

SQL Server

Click the Edit button, and the Window given below will open. Select the Server name and the database name. Now, click the Test connection button. The successful message popup shown below will be displayed.

SQL Server

SQL Server

Now, click the OK button.

SQL Server

If you check the solution DSources.rds, the file will be added to the Shared Data Source folder.

SQL Server

Now, we are done with all configuration-related parts. Now we will move to the actual reporting part.

Right-click on Reports folder => Add New Report 

SQL Server

The Window given below will open. Now, don’t change anything and click the Next button. If you find the default SharedDSource, it needs to be selected. If you have not added Shared Data Source, you need to add New Data Source for the specific report.

SQL Server

Click the Query Builder button.

SQL Server

After Clicking the Query Builder button, the Window given below will open. Now, click Edit as Text option.

SQL Server

In the Window given below, you will get two options for the retrieval of your data, where one is Text and the second is Stored Procedure.

  • Text. In Text, you need to add your selected query.
  • Stored Procedure. In the Text, you need to add your procedure.

SQL Server

Now, I am using a stored procedure because all the stuff and complexity can be written in the stored procedure.

SQL Server

Now click the “!” symbol. The Window given below will open. Currently, I am using the procedure, so there is one parameter @EmpID, which is available in my procedure. Click the Pass Null check box, select it, and then click the OK button. Now, I am passing a null value for the time being.  

SQL Server

The output given below will be displayed. Now, click the OK button.

SQL Server

Click the Next button.

SQL Server

Continue with Tabular and click the Next button.

SQL Server

In this Window, select available fields and add to Display Fields content by clicking the Details button.

SQL Server

Now, click the Next button.

SQL Server

Click the Finish button.

SQL Server

The report given below will be displayed.

SQL Server

There are options available; one design, where you can modify your report as per your requirement, and another one is Preview, where you can see your report preview.

The final output is given below.

SQL Server

I hope you enjoyed this article. Please provide your valuable feedback & suggestions.


Similar Articles