Esercitazione su SSRS: cos'è SQL Server Reporting Services?

Che cos'è SSRS?

SSRS stands for SQL Server Reporting Services. It is 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 at any time using parameters defined by the users. It is part of the Microsoft SQL Server Suite di servizi.

Types of Reporting Services

The three types of SQL Server reporting services are:

1. Microsoft SQL Server Servizi di integrazione, which integrate data from different sources.
2. Microsoft SQL Server Analysis Services, which help with the analysis of the data.
3. Microsoft SQL Server Reporting Services, which allow the generation of a visual report of the data.

Perchè SSRS?

Here are the prime reasons for using the SSRS tool:

• SSRS is an enhanced tool compared to Crystal Reports.
• Faster processing of reports on both relational and multidimensional data.
• Allows a better and more accurate decision-making mechanism for the users.
• Allows users to interact with information without involving IT professionals.
• It provides a web-based connection for deploying reports, so reports can be accessed over the internet.
• SSRS allows reports to be exported in different formats. You can deliver SSRS reports using email.
• SSRS provides a host of security features, which help you control who can access which report.

Esempio di segnalazione SSRS

Consider an SSRS report example of a medical research institute where patients are recruited for various clinical trials.

The staff in the institute create a database record for each patient once they agree to be part of the trial, and the hospital gets a payment from the drug company based on the agreed price for participation.

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 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 a lot of valuable clinic time.

If the institute records data with the help of the SSRS tool, they can produce on-demand reports in a pre-defined format. With SSRS, the drug company can access the report on the cloud and run it at any time to get the latest data from the clinic.

Caratteristiche dell'SSRS

• Offerte a Protocollo di accesso agli oggetti semplici (SOAP) application and pluggable architecture.
• Retrieves data from managed, OLE, ODBC, and DB connections.
• Allows you to create ad-hoc reports and save them to the server.
• Displays data in a variety of formats, including tabular, free-form, and charts.
• Creates custom controls by using a report-processing extension.
• Embeds graphics and images in reports. You can also integrate with external content using SharePoint.
• You can store and manage custom reports.
• The chart and gauge control feature allows you to display KPI data.

Come funziona l'SSRS

1. The report users are the people who work with the data and want some insights from it. They send a request to the SSRS server.
2. The SSRS server finds the metadata of the report and sends a request for data to the data sources.
3. I dati restituiti dall'origine dati vengono uniti con la definizione del report in un report.
4. Quando il report viene generato, viene restituito al client.

SSRS Architectura

SSRS has quite a complex architecture. The reporting services architecture includes development tools, administration tools, and report viewers. The important components of SSRS are below.

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 develop all types of reports. It is a publishing tool hosted in Di Visual Studio o Business Intelligence Development Studio (BIDS).

Responsabile dei rapporti

Report managers check the report, matching it with the given requirements, and make decisions based on those reports.

Server dei rapporti

It is a server that uses the SQL Server database engine to store metadata information.

Database del server di report

It stores metadata, report definitions, resources, security settings, delivery data, and so on.

Origine dei dati

Reporting services retrieve data from data sources such as relational and multidimensional data sources.

Reporting del ciclo di vita

Every organization follows a standard reporting lifecycle, which can be classified as follows:

Autore: In this phase, the report author defines the layout and syntax of the data. The tools used are the SQL Server Development Studio and the 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.

Consegna: In this phase, you need to understand when the reports need to be delivered to the customer base. Delivery can be on demand or on a pre-defined schedule. You can also add an automation feature of subscription, which creates reports and sends them to the customer automatically.

Cos'è l'RDL?

Report Definition Language is known in short 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 and contains instructions for rendering the design of the report at run time.

Types of SSRS Reports

Here are the types of reports you can develop using the SSRS tool.

Nome del tipo di rapporto	Eccezionale
Rapporti parametrizzati	Questo tipo di report utilizza valori di input per completare il report o l'elaborazione dei dati.
Rapporti collegati	A linked report offers a pointer to an existing report. It is derived from an existing report and retains the original report definition.
Rapporti istantanei	A snapshot report contains layout information and query results that can be retrieved at a specific point in time.
Rapporti memorizzati nella cache	A cached report allows you to create a copy of the processed report. It is used to enhance performance by reducing the number of processing requests and the time to retrieve large reports.
Drill-down reports	Drill-down reports help you hide complexity. They allow users to toggle between hidden report items to control how much detail they see, and they retrieve all information that can be shown in the report.
Rapporti drill-through	Drillthrough reports are standard reports accessed through a hyperlink on a text box in the original report. They are the target of a drillthrough action for a report item such as a placeholder text or a chart.
Sottoreport	As the name suggests, a subreport is a report that displays another report inside the body of the main report.

Advantages of Using SSRS

• È più veloce ed economico.
• Efficient reporting access to information residing in either an Base di dati MS SQL Server or Oracle.
• No need for expensive specialist skills.
• The default report designer is integrated with Visual Studio .NET, so you can create an application and reports in the same environment.
• Security is managed in a role-based method, which can be applied to folders and reports.
• Subscription-based reports are automatically sent to 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

Alcune limitazioni di SSRS sono riportate di seguito:

• There is no print button, so to print you need to export to PDF, Excel, Word, or another format.
• Tutti i report necessitano di parametri per essere accettati dagli utenti.
• It is difficult to make changes in custom code and debug expressions.
• It does not allow you to add a page number or total pages in the report body.
• It offers no method to pass values from subreports to the main report.
• A page header always creates extra space on every new page.

DOMANDE FREQUENTI