PL-SQL
Oracle PL/SQL Records Type with Examples
What is Record Type? A Record type is a complex data type which allows the programmer to create a...
SQL Server Reporting Services (SSRS) Tutorial for Beginners
- Details
What is SSRS?
SQL Server Reporting Services (SSRS) is a reporting software that allows you to produce formatted reports with tables in the form of data, graph, images, and charts. These reports are hosted on a server that can be executed any time using parameters defined by the users. It is part of Microsoft SQL Server Services suite.
In this SSRS tutorial, you will learn
- Types of reporting services
- Why SSRS?
- Example of SSRS reporting
- Features of SSRS
- How SSRS Works?
- SSRS Architecture
- Reporting Life Cycle
- What is RDL?
- Type of SSRS reports
- Advantages of using SSRS
- Disadvantages of using SSRS
Types of reporting services
Three types of SSRS reporting services are:
- Microsoft SQL Server Integration services which integrate data from different sources.
- Microsoft SQL Server Analytical service which helps for the analysis of the data
- Microsoft SQL Server Reporting service allows for generating a visual report of the data.
Why SSRS?
Here, are prime reasons for using SSRS tool:
- SSRS is an enhanced tool compared to Crystal Reports
- Faster processing of reports on both relational and multidimensional data
- Allows better and more accurate Decision-making mechanism for the users
- Allows users to interact with information without involving IT professionals
- It provides a World Wide Web-based connection for deploying reports. Hence, reports can be accessed over the internet
- SSRS allows reports to be exported in different formats. You can deliver SSRS reports using emails
- SSRS provides a host of security features, which helps you to control, who can access which report
Example of SSRS reporting
Consider a SSRS report example of a medical research institute where patients are recruited for various clinical trials.
The staff in the institute creates a database record for each patient.
Once they agree to be part of the trial, and the hospital gets the payment form the drug company based on the price at which it is ready to participate.
Without SSRS, the medical institute would need to manually email a report to the pharma company with the total number of weekly participants. The institute must also add details of every patient included in the trial, the number of drugs used, and all the unwanted situations. As a result, the time taken to collect and send this data in the correct format may consume loads of valuable time in the clinic.
If the institute were recording data, with the help of SSRS tool, they would be able to produce on-demand reports in a pre-defined format.
With SSRS, the drug company can access the report on the cloud, run the report at any time to get the latest data from the clinic.
Features of SSRS
- Offers a Simple Object Access Protocol (SOAP) application and pluggable architecture
- Retrieve data from managed, OLE ODBC, and DB connections
- Allows you to create adhoc reports and save them to the server
- Display data in a variety of formats which includes tabular, free-form, and charts
- Create custom controls by using report-processing extension
- Embed graphics, images to the reports. You can also integrate with external content using SharePoint
- You can store and manage custom reports
- Chart and Gauge control feature allows you to display KPI data
How SSRS Works?
Now in this SSRS tutorial, we will learn how SSRS works: 
- The report users are the peoples who work with the data as well as want some insights from data. They send a request to the SSRS server
- SSRS server finds the metadata of the report and sends a request for data to the data sources.
- Data returned by the data source is merged with the report definition into a report.
- When the report is generated, it is returned to the client.
SSRS Architecture
SSRS has quite a complex architecture. The report services architecture includes development tools, administration tools, and report viewers.
Here, are important components of SSRS
Report Builder
It is an ad-hoc report publishing tool that is executed on a client's computer. It has a drag and drop interface that is easy to use.
Report Designer
The Report designer tool helps to develop all types of reports. It is a publishing tool, that is hosted in Visual Studio or Business Intelligence Development Studio (BIDS).
Report Manager
The report managers check the report, matching it with the given requirements. They make decisions based on those reports.
Report Server
It is a server which uses the SQL Server database engine to store metadata information
Report server database
It stores metadata, report definitions, resources, security settings, delivery data, etc.
Data sources
Reporting services retrieve data from data sources like relational and multidimensional data sources.
Reporting Life Cycle
Every organization follows a standard reporting lifecycle which can be classified as follows:
Authoring: In this phase, the report author defines the layout and syntax of the data. The tools used in this process are the SQL Server Development Studio and SSRS tool.
Management: This phase involves managing a published report which is mostly part of the websites. In this stage, you need to consider access control over report execution.
Delivery: In this phase, you need to understand when the reports need to be delivered to the customer base. Delivery can be on-demand or pre-defined schedule. You can also add an automation feature of subscription which creates reports and sends to the customer automatically.
What is RDL?
Report Definition Language is shortly known as RDL. It describes all possible elements of a report using an XML grammar which is validated by an XML schema.
The report definition of an individual report is based on RDL. It contains instructions for rendering the design of the report at the run time.
Type of SSRS reports
Here, are types of reports which you can develop using SSRS tool.
| Report Type Name | Detail |
| Parameterized reports | This type of report uses input values to complete report or data processing. |
| Linked Reports | A linked report offers a point to an existing report. This type of report is derived from an existing report and retains the original's report definition. |
| Snapshot reports | A snapshot report contains layout information and query results which can be retrieved at a specific point in time. |
| Cached Reports | The cashed report allows you to create a copy of the processed reports. They are used to enhance the performance by reducing the number of processing requests and time to retrieve large reports. |
| Drill down Reports | Drill down reports helps you to hide complexity. It allows users toggle between hidden reports items to control how much detail data you want to see. It must retrieve all possible information that can be shown in the report. |
| Drillthrough Reports | Drillthrough reports are standard reports which can be accessed through a hyperlink on a text box in the original report. It works with the main report and is the target of a drill through an action for a report item like a placeholder text or a chart. |
| Subreports | As the name suggests, sub-reports is a report which displays another report inside the body of the main report. |
Advantages of using SSRS
- It is faster and cheaper.
- Efficient reporting access to information that is residing in both MS SQL Server database or Oracle
- No need for expensive specialist skills
- In SSRS the default report designer is integrated with Visual Studio .NET. This allows us to create an application and reports in the same environment.
- The security is managed in a role-based method which can be applied to folders and reports.
- Subscription-based reports are automatically sent to the users.
- Faster production of reports on both relational and cube data
- Real time information to the business, providing better decision support
Disadvantages of using SSRS
Some limitations of SSRS are given below:
- There is no print button. So if you want to print something you need to export PDF, excel, word or other formats.
- All reports need parameters to be accepted by users.
- It is difficult to make changes in the custom code and debug expressions.
- Does not allow you to add page number or total pages in the report body
- Does not offer any method to pass values form sub-reports to the main report
- Page header always creates extra spaces on every new page.
Summary
- The SSRS full form is SQL Server Reporting Services which allows you to produce formatted reports with tables in the form of data, graph, images, and charts.
- Three types of analytical SSRS services are 1) Integration 2) Analytics 3) Reporting.
- SSRS offers faster processes of reports on both relational and multidimensional data.
- SSRSS helps you to retrieve data from managed, OLE, ODBC, and DB connections.
- Report Builder, Report Designer, Report Managee, Report Server, Data sources are important SSRS components.
- Reporting life-cycle main based on three aspects 1) Authoring 2) Management 3) Delivery.
- Report Definition Language (RDL) describes all possible elements of a report using an XML grammar which is validated by an XML schema.
- Important types of SSRS reports are: 1)Parameterized reports 2)Linked Reports 3) Snapshot reports 4) Cached 5) Drill through Reports, etc.
