SQL Server Reporting Service (SSRS) Tutorial
The Microsoft SQL Server Reporting Service (SSRS) is a server-based reporting platform. It allows you to produce structured reports. The reports are represented in the form of data, graph, charts, and images.
There are the following three types of reporting services:
- Microsoft SQL Server Integration service: It is a platform which is used to build enterprise-level data integration and data transformation solutions.
- Microsoft SQL Server Analytical service: It is a platform used for analyzing the data.
- Microsoft SQL Server Reporting services: It is a platform used to generate a visual report for the data.
Why do we use the SSRS tool?
There are the following reasons for using the SSRS tool:
- It allows you to export the reports in various formats and to be delivered in the form of emails.
- SSRS helps in controlling the access of reports.
- SSRS provides accurate decision-making mechanism to the users.
- SSRS provides security features, which helps you to authenticate the access of report.
- It provides the fastest processing of reports on both relational and multidimensional data.
- It allows you to retrieve data from managed, ODBC, OLE, and DB connections.
What’s new in Reporting Services?
- Reporting services web portal
- PowerPoint Rendering and Export
- Key Performance Indicators (KPI) in the web portal
- Mobile Reports
- PDF Replaces ActiveX for Remote Printing
- HTML 5 Rendering Engine
The below diagram shows the architecture of SQL Server Reporting Services.
Report Builder is a light-weight report designer tool which helps us to design and deploy SQL server reporting services reports.
The report builder offers the following benefits:
- Easy installation
- Basic learning
- Advanced toolbox support (Map, Table, Matrix)
- Allow the implementation of a self-service BI approach.
The report designer allows you to create data-bound reports and provides a set of tools to construct the reports from existing data.
Report Manager is a Web-based report management tool that allows you to administer a single report server instance from a report location over an HTTP connection. You can also use Report Manager to browse the report server folders or search for specific reports.
A report server is the core engine of the SSRS which uses the SQL Server database engine to store metadata information.
Report Server Database
Report Server Database is a stateless server that stores all objects, properties, and metadata in a SQL Server database. The stored data includes compiled reports, report models, and the folders hierarchy that provides the addressing for all items managed by the report server.
Some Report Server Databases are:
- Users and Roles
- Report definitions and report metadata
- Data source information
- Execution Logs
- Report Comments
The data source is a set of data connection that can be referenced by multiple reports, models, and data-driven subscriptions. The data source is created at a time when you create your database connection.
SSRS uses the following different data sources:
- Microsoft SQL Server
- SQL Server Analysis Service
- Report Server Model
- SAP Net weaver BI
How SSRS Works
The Working of SSRS can be explained in the below steps:
1. End users who work with the data, first send the Report Request to the SSRS server.
2. The SSRS Server finds the request in the form of metadata and sends a request for data to the data source.
3. Now, after processing the data, the data source returns the data in the form of the merged report.
4. Once the report is created, it is returned to the end-users.
Types of SSRS Reports
There are the following types of SSRS Reports –
1. Parameterized reports – A parameterized report uses input values to complete the report. With the help of parameterized report, you can vary the output of a report based upon the values.
2. Linked Reports – A linked report is a server report that provides an access point for an existing report.
3. Snapshot Reports – A snapshot report is a report that contains layout information and query results that are retrieved at a specific point in time. These reports are not saved in a particular rendering format.
The snapshot report serves the following three purposes:
- Report history
4. Cached Reports – A cached report contains a copy of a processed report. These reports are used to improve the performance by decreasing the number of processing requests to the report processor.
5. Drilldown Reports – A drilldown reports initially hide the complexity.
6. Drillthrough reports – Adrillthroughreports are standard reports that are accessed through a hyperlink on a text item in the original report. It is mainly used to display the main report’s information, such as a matrix or chart.
7. Subreports – A snapshot report is a report that contains layout information and query results that we retrieve at a specific point of time. It is similar to a frame in a Web page.
SSRS reporting lifecycle can be classified into the following three phases:
1. Authoring: In this phase, the Report author defines the layout and source of the data. It uses two methods of report development:
- End user-developed reports
- Report analyst developed reports
Both report development methods are necessary to support an enterprise reporting solution.
2. Management: This phase involves managing the published reports, which are public and available on the websites.
Report Management includes:
- Organizing reports
- Scheduling report execution and delivery
- Tracking reporting history.
3. Delivery: This phase consists delivering the reports in a required format like pdf, gif, etc. and creating subscriptions to deliver the reports on a scheduled basis.
Advantages of SSRS
There are the following advantages of SSIS –
- SSRS develops the reports very fast and at low cost.
- SSRS offers the capability to interact with business data.
- In SSRS, all reports need parameters, and that parameter needs to be confirmed by the users. Once the confirmation acceptance is done, all reports are automatically generated.
- SSRS treats the whole function as a single web service. So you can generate, control and sight reports using various methods offered by the web service.
- Each user can handle their own reporting requests by accessing the reports.
- In SSRS, it is easy to expand a centralized reporting infrastructure based on Microsoft Reporting Services.
- SSRS does not require expensive experts to handle the server-based report.
Disadvantages of SSRS
There are the following disadvantages of SSRS –
- In SSRS, there is no print option. So if you want to print something, you first need to convert the file into PDF, word, excel, or in other formats.
- It does not allow to add page number in the report body.
- It is difficult to make changes in the code.
- It is difficult to upgrade.
Create a Tablix report
To create the report in SSRS, first, we need to install the SQL Server Engine and SQL Management tool. You can use the following link to learn how to download and install the SQL server engine and management tool.
Step1: To create a Tablix report, first we need to open the visual studio. Then click on the File -> New -> Project.
Step2: In the New Project window, click on the Reporting Services and then select the Report Server Project. You can also provide the project name and browse the location where you want to save the project. Then click on the OK.
Step3: When you click on the OK button, the following screen is opened.
Step 4: Now, you need to create the data source. To create a data source, Right-click on the Shared Data Sources, and then click on the Add New Data Source.
Step 5: After clicking on the Add New Data Source, the following screen appears.
Click on the Build button, to make the connection with the database.
When you click on the Build button, the following screen appears in which you need to enter the Data source, Server name, and Database name. After proving these information click on the Test Connection. If Test Connection Succeeded, then click on the Next. Otherwise, check the connection properly.
Step 6: Now, we will create the datasets because we do not want to work on the all tables of a database, so to create the dataset Right-click on the shared datasets, and then click on the Add New Dataset.
Step 7: After clicking on the Add New Dataset, the following screen appears.
In the above screenshot, write the query that you want to execute. Then click on the OK.
Step 8: Now to create the report, Right-click on the Reports, and then click on the Add New Report.
Step 9: When you click on the Add New Report, the following screen appears. Then click on the Next button.
Step 10: Click on the Next.
Step11: Now, click on the Query Builder. Write a Query, such as “select * from Employ” then click on the OK.
Step 12: After clicking Ok, the following screen appears. In this screen, click on the next.
Step 13: Now, it will ask for the report type, in our case, we will select tabular then click on the Next.
Step 14: After clicking on the Next, the following screen appears. Now in this screen, click on the Next.
Step 15: Choose the Table Style that you want to add, then click on the Next.
Step 16: Now click on the preview, then click on the finish.
Step 17: After clicking on the Finish, the following screen appears.
Step 18: Click on the Preview to see the whole report.by