Урок за SSRS: Какво представляват SQL Server Reporting Services?

Какво е 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

• Предлага a Прост протокол за достъп до обекти (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 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.

Въпроси и Отговори