Tools required for SSRS report
- Visual Studio with SQL Server Data Tool.
- SQL Server.
The steps given below are required to create SSRS report.
Open Visual Studio => File = > Project.
![SQL Server]()
Select Reporting Services = > Report Server Project = > Set Location & Project Name, followed by clicking OK button.
![SQL Server]()
Select Solution Explorer
![SQL Server]()
The folders will be available there.
- Shared Data Sources
The purpose of Shared Data Sources is that it allows us to use same data source for all the reports within the project, which includes configuration details (For e.g SQL connection string). The Data Source contains multiple Datasets.
- Shared Datasets
The purpose of Shared Datasets is that it allows us to use the same dataset for all reports within the project.
- Reports
Where we can 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 Data Source name (e.g SharedDSource).
![SQL Server]()
Click Edit button and the Window given below will open. Select Server name and the database name. Now, click Test connection button. The successful message popup given below will be displayed.
![SQL Server]()
![SQL Server]()
Now, click OK button.
![SQL Server]()
If you will check the solution DSources.rds, the file will be added in Shared Data Source folder.
![SQL Server]()
Now, we are done with all configuration related part. Now we will move to 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 Next button. If you find the default SharedDSource, it needs to be selected. If you have not added Shared Data Source, then you need to add New Data Source for the specific report.
![SQL Server]()
Click Query Builder button
![SQL Server]()
After Click 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 second is Stored Procedure.
Text:- In Text, you need to add your select query.
Stored Procedure:- In Text, you need to add your procedure.
![SQL Server]()
Now, I am using stored procedure because all the stuff and complexity can be written in stored procedure.
![SQL Server]()
Now click “!” symbol. The Window given below will open. Currently, I am using procedure, so in my procedure; there is one parameter @EmpID, which is available. Now, I am passing null value for the time being. Just click Pass Null check box, select and then click OK button.
![SQL Server]()
The output given below will be displayed. Now, click OK button.
![SQL Server]()
Click Next button.
![SQL Server]()
Continue with Tabular and click Next button.
![SQL Server]()
In this Window, you need to select available fields and add to Display Fields content by clicking Details button.
![SQL Server]()
Now, click Next button.
![SQL Server]()
Click Finish button.
![SQL Server]()
The report given below will be displayed.
![SQL Server]()
There are options, which are available; which has 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.