Prerequisite
We should have some basic knowledge in SQL Server query and Visual Studio tool to create SSRS reports, using Visual Studio 2015.
- SQL Server basic query
- Visual Studio 2015
How to setup the SSRS environment
If you want to know how to setup the SSRS environment procedure, prerequisite and SSDT and you can read out my previous article, as given below.
How to create a table in database
First, create an Employee table, using SQL Server Management Studio in SQL Server database. Now, you can add an employee record in the Student table.
- CREATE TABLE [dbo].[Employee](
- [EmpId] [int] IDENTITY(1,1) NOT NULL,
- [EmpName] [varchar](25) NULL,
- [EmpLocation] [varchar](50) NULL,
- [EmpDept] [varchar](25) NULL,
- [EmpSalary] [decimal](18, 0) NULL
- ) ON [PRIMARY]
How to create SSRS Project
Open Visual Studio 2015. Go to File menu, point to new and click new project. New Project Window will open, you can select an installed template like “Reporting Services” in Business Intelligence template, select the Report Server project and type Project Name EmployeeTableReport. Choose the project location path and click OK button.
![]()
Now, you can see EmployeeTableReport project structure, as shown in the screenshot given below.
![]()
How to create new data source
Go to Shared Data Source folder, right-click the Shared Data Source folder and point to Add -> New Item and click the New Item.
![]()
Add New Item Window will open and choose the Data Source template. Now, click the Add button.
![]()
Shared Data Source Window will open and click the Edit button.
![]()
The Connection Properties Window will open and type the Server Name, select the Authentication, User Name, Password, select or enter a database name and finally you can check the database connection succeed, followed by clicking the Test Connection button. Now, click OK button.
![]()
Shared Data Source Wndow will open. Select the new data source, connection string and click OK button.
![]()
After creating new data source file, you can see the shared data source folder structure, as shown in the screenshot given below.
![]()
How to create new Dataset
Go to the Shared Datasets folder, right-click the Shared Datasets folder and point to Add -> New Item, followed by clicking the New Item.
![]()
Now, add New Item Window will open and choose the Dataset template. Now, click the Add button.
![]()
The Shared Dataset Window will open. Type the Dataset name and select the query type option. Now, type the query and click OK button.
- Select EmpId as ID, EmpName as 'Employee Name', EmpLocation as 'Location', EmpDept as 'Department', EmpSalary as 'Salary' from Employee.
![]()
After creating new dataset file, you can see the shared datasets folder structure, as shown below.
![]()
How to create a new Report
You can see in the details on how to add a new report, using Visual Studio 2015 in Solution Explorer. You can see the step by step procedures, as given below.
Go to Reports folder, right-click on the Reports folder and point to Add -> New Item, then click the New Item.
![]()
The Add New Item Window will open and choose the Report template and type the Report Name. Now, click the Add button.
![]()
After creating report file, you can see the report design view, as shown below.
![]()
You can see the SSRS Toolbox, as shown below.
![]()
In SSRS Toolbox, drag and drop table into report design, as shown below.
![]()
Now, we can drag and drop the Report fields from Report Data (or) we can click in the particular column. The content menu will display and select the column in the dataset, as shown below.
![]()
![]()
Finally, click the preview button to see the report view.
![]()
How to deploy the Report
Go to the Solution Explorer, right-click the EmployeeTableReport and click the deploy context-menu item.
![]()
After successfully deploying the report to Report Server, you will see the information from the output Window, as shown below.
![]()
How to run the report
You can see in details on how to run the report, using Report Server in the Browser. You can see the step by step procedures, as shown below.
First, you have to copy the default Report Server URL and paste the same URL into your Browser for the run report Application.
http://localhost/reportserver
![]()
Now, you can run the report Application, followed by EmployeeTableReport project and clicking the EmployeeDetailReport.
![]()
Conclusion
I hope you understood how we can create a table report without using Report Wizard in SQL Server Reporting Services 2016, how to create a new report, how to create a new data source, how to create a new dataset and how to deploy/run the report Application in the Report Server. I have covered all the required things. If you find anything which I missed in this article, please let me know. Please share your valuable feedback or comments and suggestions to improve future articles.