Εκμάθηση SSRS: Τι είναι οι Υπηρεσίες αναφοράς SQL Server;

Τι είναι το 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 Σουίτα υπηρεσιών.

Types of Reporting Services

The three types of SQL Server reporting services are:

1. Microsoft SQL Server Υπηρεσίες ενοποίησης, 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.

Γιατί 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.

Παράδειγμα αναφοράς 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.

Χαρακτηριστικά του SSRS

• Προσφέρει ένα Πρωτόκολλο απλής πρόσβασης αντικειμένου (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.

Πώς λειτουργεί το 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. Τα δεδομένα που επιστρέφονται από την πηγή δεδομένων συγχωνεύονται με τον ορισμό της αναφοράς σε μια αναφορά.
4. Όταν δημιουργηθεί η αναφορά, επιστρέφεται στον πελάτη.

SSRS Archiδομή

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.

Δημιουργός αναφοράς

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.

Σχεδιαστής Αναφορών

The Report Designer tool helps develop all types of reports. It is a publishing tool hosted in Visual Studio ή Business Intelligence Development Studio (BIDS).

Διαχειριστής αναφορών

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

Διακομιστής αναφορών

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

Βάση δεδομένων διακομιστή αναφορών

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

Πηγές Δεδομένων

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

Αναφορά κύκλου ζωής

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

Συγγραφικός: 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.

Διαχείριση: 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.

Διανομή: 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.

Τι είναι το 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.

Τύπος αναφοράς Όνομα	Λεπτομέρεια
Παραμετροποιημένες αναφορές	Αυτός ο τύπος αναφοράς χρησιμοποιεί τιμές εισόδου για την ολοκλήρωση της αναφοράς ή της επεξεργασίας δεδομένων.
Συνδεδεμένες αναφορές	A linked report offers a pointer to an existing report. It is derived from an existing report and retains the original report definition.
Αναφορές στιγμιότυπου	A snapshot report contains layout information and query results that can be retrieved at a specific point in time.
Αποθηκευμένες αναφορές στην κρυφή μνήμη	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.
Αναφορές Drillthrough	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.
Υποαναφορές	As the name suggests, a subreport is a report that displays another report inside the body of the main report.

Advantages of Using SSRS

• Είναι πιο γρήγορο και οικονομικό.
• Efficient reporting access to information residing in either an Βάση δεδομένων 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

Ορισμένοι περιορισμοί του SSRS δίνονται παρακάτω:

• There is no print button, so to print you need to export to PDF, Excel, Word, or another format.
• Όλες οι αναφορές χρειάζονται παραμέτρους για να γίνουν αποδεκτές από τους χρήστες.
• 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.

Συχνές Ερωτήσεις