SQL Server Reporting Services

Last updated

SQL Server Reporting Services (SSRS) 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).

Contents

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:

Packaging

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 [2] including Excel, PDF, CSV, XML, TIFF (and other image formats [3] ), 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.

Microsoft SQL Server 2012 SP1 expands Microsoft support for viewing reports to mobile platforms, including Microsoft Surface, Apple iOS 6 and Windows Phone 8. [4]

Power BI Report Builder

Power BI Report Builder is a variant of the SSRS application for building paginated reports, [5] with some features added and branded under the Power BI platform. The "Power BI Report Builder" application (not to be confused with "Report builder", the standalone SSRS tool) produces RDL reports (.rdl files, similar to SSRS), in contrast to the interactive .pbix files of regular Power BI reports (produced for example with the Power BI Desktop application). A possible reason for introducing SSRS for Power BI may be that it fills a void in the current Power BI software when it comes to printing pdf or paper with good control of pagination and formatting of printed content, or to consolidate Microsoft's reporting software' under the Power BI brand.[ citation needed ] Similar to regular Power BI reports, paginated reports can also be published to the Power BI Service, and can also be connected to shared Power BI datasets. Paginated reports with Power BI Report Builder can be developed for free locally, but a Premium Power BI workspace is needed for publishing a report to the Power BI Service. [5] SSRS reports can be migrated to Power BI using the "Power BI Report Builder". [6]

Related Research Articles

Active Directory (AD) is a directory service developed by Microsoft for Windows domain networks. Windows Server operating systems include it as a set of processes and services. Originally, only centralized domain management used Active Directory. However, it ultimately became an umbrella title for various directory-based identity-related services.

<span class="mw-page-title-main">Microsoft Access</span> Database manager part of the Microsoft 365 package

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Access Database Engine (ACE) with a graphical user interface and software-development tools. It is a member of the Microsoft 365 suite of applications, included in the Professional and higher editions or sold separately.

FileMaker is a cross-platform relational database application developed by Claris International, a subsidiary of Apple Inc. It integrates a database engine with a graphical user interface (GUI) and security features, allowing users to visually modify a database. Versions for desktops, servers, iOS, and web-delivery have been released.

<span class="mw-page-title-main">WebObjects</span> Java web application server and framework originally developed by NeXT Software

WebObjects is a discontinued Java web application server and a server-based web application framework originally developed by NeXT Software, Inc.

<span class="mw-page-title-main">Microsoft Project</span> Project management software

Microsoft Project is project management software product, developed and sold by Microsoft. It is designed to assist a project manager in developing a schedule, assigning resources to tasks, tracking progress, managing the budget, and analyzing workloads.

PowerBuilder is an integrated development environment owned by SAP since the acquisition of Sybase in 2010. On July 5, 2016, SAP and Appeon entered into an agreement whereby Appeon, an independent company, would be responsible for developing, selling, and supporting PowerBuilder.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

<span class="mw-page-title-main">Microsoft Office 2007</span> Version of Microsoft Office

Microsoft Office 2007 is an office suite for Windows, developed and published by Microsoft. It was officially revealed on March 9, 2006 and was the 12th version of Microsoft Office. It was released to manufacturing on November 3, 2006; it was subsequently made available to volume license customers on November 30, 2006, and later to retail on January 30, 2007. The Mac OS X equivalent, Microsoft Office 2008 for Mac, was released on January 15, 2008.

Report Definition Language (RDL) is a standard proposed by Microsoft for defining reports.

<span class="mw-page-title-main">QGIS</span> Open-source desktop GIS software

QGIS is a geographic information system (GIS) software that is free and open-source. QGIS supports Windows, macOS, and Linux. It supports viewing, editing, printing, and analysis of geospatial data in a range of data formats. QGIS was previously also known as Quantum GIS.

Oracle XML Publisher (XMLP) is an Oracle Corporation technology originally developed to solve the reporting problems faced by Oracle applications. Osama Elkady from the Applications Technology Group and Tim Dexter from the Financials Group were the main drivers for the product. It was first released with Oracle E-Business Suite 11.5.10 in 2003. Since then it has been integrated into most of Oracle Products including JD Edwards EnterpriseOne application 8.12 and PeopleSoft Enterprise 9, and as a standalone version, XML Publisher Enterprise with no dependency on Oracle Applications. When XML Publisher became part of the Oracle BI Enterprise Edition Suite it was re-branded as Oracle BI Publisher.

Azure DevOps Server, formerly known as Team Foundation Server (TFS) and Visual Studio Team System (VSTS), is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used as a back-end to numerous integrated development environments (IDEs) but is tailored for Microsoft Visual Studio and Eclipse on all platforms.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

The Microsoft Open Specification Promise is a promise by Microsoft, published in September 2006, to not assert its patents, in certain conditions, against implementations of a certain list of specifications.

<span class="mw-page-title-main">ActiveReports</span> Data Visualization tool for .NET application developers

ActiveReports is a .NET reporting tool used by developers of .NET, and ASP.NET/JavaScript applications. It was originally developed by Data Dynamics, which was then acquired by GrapeCity, now known as Mescius. It is written in managed C# code and allows Visual Studio programmers to leverage their knowledge of C# or Visual Basic.NET when programming with ActiveReports.

SoftArtisans is an American company that develops active server components and reporting solutions for Microsoft web development environments such as ASP.NET. The company has two products: OfficeWriter, a .NET-based Microsoft Office reporting solution and FileUp, a client-server-server HTTP file transfer provider.

List & Label is a professional reporting tool for software developers. It provides comprehensive design, print and export functions. The software component runs on Microsoft Windows and can be implemented in desktop, cloud and web applications. List & Label can be used to create user-defined dashboards, lists, invoices, forms and labels. It supports many development environments, frameworks and programming languages such as Microsoft Visual Studio, Embarcadero RAD Studio, .NET Framework, .NET Core, ASP.NET, C++, Delphi, Java, C Sharp and some more. List & Label either retrieves data from various sources via data binding, or works database independent. Reports are designed and created in the so-called List & Label Designer and then exported into a multitude of formats like PDF, Excel, XHTML and RTF. Since version 27 a web report designer for ASP.NET MVC is available.

<span class="mw-page-title-main">BIDS Helper</span>

BIDS Helper is a Visual Studio open source extension with multiple features that extend and enhance business intelligence development functionality in all editions of Microsoft's SQL Server 2005, 2008, 2008 R2 and 2012. BIDS Helper improves the development environment for integration, analysis and reporting services. BIDS Helper is hosted on GitHub.

Microsoft Power BI is an interactive data visualization software product developed by Microsoft with a primary focus on business intelligence. It is part of the Microsoft Power Platform. Power BI is a collection of software services, apps, and connectors that work together to turn various sources of data into static and interactive data visualizations. Data may be input by reading directly from a database, webpage, PDF, or structured files such as spreadsheets, CSV, XML, JSON, XLSX, and SharePoint.

WinRM (Windows Remote Management) is Microsoft's implementation of WS-Management in Windows which allows systems to access or exchange management information across a common network. Utilizing scripting objects or the built-in command-line tool, WinRM can be used with any remote computers that may have baseboard management controllers (BMCs) to acquire data. On Windows-based computers including WinRM, certain data supplied by Windows Management Instrumentation (WMI) can also be obtained.

References

  1. ↑ Archiveddocs. "Reporting Services in SQL Server Express with Advanced Services". docs.microsoft.com.
  2. ↑ Capital), Ryan Majidimehr (H10. "ReportExecutionService.Render Method (ReportExecution2005)". docs.microsoft.com.{{cite web}}: CS1 maint: numeric names: authors list (link)
  3. ↑ Image Device Information Settings - SSRS can render BMP, EMF, GIF, JPEG, PNG, and TIFF.
  4. ↑ maggiesMSFT. "View Reporting Services Reports on Microsoft Surface Devices and Apple iOS Devices - SQL Server 2014 Reporting Services". docs.microsoft.com.
  5. 1 2 Power BI Paginated Reports vs SSRS reports – It Ain’t Boring
  6. ↑ Migrate SQL Server Reporting Services reports to Power BI - Power BI | Microsoft Learn
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.