SSRS: Complete Guide Tutorial For Free | CHECK-OUT

Last updated on 12th Jun 2020, Blog, Tutorials

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, graphs, 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.

It is a server-based report generating software system from Microsoft. It is part of a suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

Administered via a Web interface, it can be used to prepare and deliver a variety of interactive and printed reports. The SSRS service provides an interface into Microsoft Visual Studio so that developers as well as SQL administrators can connect to SQL databases and use SSRS tools to format SQL reports in many complex ways. It also provides a ‘Report Builder’ tool for less technical users to format SQL reports of lesser complexity. SSRS competes with Crystal Reports and other business intelligence tools.

History:

Reporting Services was first released in 2004 as an add-on to SQL Server 2000. Subsequent versions have been:

•  Second version with SQL Server 2005 in November 2005
• Third as part of SQL Server 2008 R2 in April 2010
• Fourth version as part of SQL Server 2012 in March 2012
• Fifth version as part of SQL Server 2014 in March 2014
• Sixth version as part of SQL Server 2016 in March 2016
• Seventh version as part of SQL Server 2017 in October 2017

In this basic 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

Why do we choose the SSRS tool?

The main reasons for choosing the SSRS tool are listed below:

• Compared to crystal reports, SSRS is an advanced report generating tool.
• Reports can be generated quickly for both multidimensional and relational data.
• SSRS tool provides accurate decision-making results to the users
• It allows you to export the reports in various formats and be delivered in the form of emails.
• SSRS is host secured and helps in controlling the access to reports.
• SSRS offers WWW connections to deploy reports so that they can be accessed over the internet.

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 an 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?

• 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. 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.

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 from subreports to the main report
• Page header always creates extra spaces on every new page.

Packaging:

• Microsoft SQL Server Developer, Standard, and Enterprise editions all include SSRS as an install option.
• The free SQL Server Express includes a limited version.

Use:

SQL Server Data Tools for Business Intelligence (SSDT BI) reduces the RDL (Report Definition Language) component to graphic icons in a GUI (Graphical User Interface). In this way, instead of writing code, the user can drag-and-drop graphic icons into an SSRS report format for most aspects of the SSRS report.

Reports defined by RDL can be downloaded to a variety of formats including Excel, PDF, CSV, XML, TIFF (and other image formats), and HTML Web Archive. SQL Server 2008 and 2012 SSRS can also prepare reports in Microsoft Word (DOC) format, while third-party report generators offer additional output formats.Users can interact with the Report Server web service directly, or instead use Report Manager, a Web-based application that interfaces with the Report Server web service. With Report Manager, users can view, subscribe to, and manage reports as well as manage and maintain data sources and security settings. Report Manager can also deliver SQL reports by e-mail, or place them on a file share.Security is role-based and can be assigned on an individual item, such as a report or data source, a folder of items, or site wide. Security roles and rights are inherited and can be overloaded.

Typically the reports are only revealed to users able to run them, and SQL connections in the source allow anyone to run with sufficient privileges. This is because configuring Windows Authentication through the reports execution is laborious and time-consuming: a Server Principal Name record (requiring DOMAIN ADMINISTRATOR access) is created in Active Directory associating the Sql Server Reporting service to the user the service runs under on the server (a network user to facilitate querying the Active Directory) and the service account user has to have the delegation option enabled, the server must be trusted for delegation too; the windows users wishing to run reports must be set to allow delegation – so Kerberos authentication protocols will be used. The reporting service itself has to have the configuration edited to enable Kerberos protocols… but then the reports will be secure and only display data the individual users are permitted to (based on SQL security configuration).

RDL reports can be viewed by using the standalone Report Server that comes with Microsoft SQL Server, or by using the ASP.NET ReportViewer web control, or by using the ReportViewer Windows Forms control. The latter method allows reports to be embedded directly into web pages or .NET Windows applications. The ReportViewer control will process reports by: (a) server processing, where the report is rendered by the Report Server; or (b) local processing, where the control renders the RDL file itself.

SQL Server Reporting Services also support ad hoc reports: the designer develops a report schema and deploys it on the reporting server, where the user can choose relevant fields/data and generate reports. Users can then download the reports locally.

Conclusion:

SSRS for End Users: From the offerings that Microsoft Business Intelligence suite provides, SSRS is definitely a unique offering as it caters to a wide variety of users. On a broader sense, Microsoft has classified these diverse sets of users into: Information Consumers.Hope you have found all the details that you were looking for, in this article.