Reporting is one of the standard requirements of businesses when they want to take the data you have collected in your database and share it in a meaningful way with users. SQL Server Reporting Services (SSRS), a standard feature in SQL Server that supports reporting, can be installed in one of two modes: SharePoint Integrated or Native (non-SharePoint) mode. This option will appear during installation and can be changed later, if desired. Once selected, the mode determines where users will view deployed reports.
There are two major sub-components within SSRS, Report Builder and Report Designer. Report Builder is the ad hoc reporting tool and is obtained in one of three ways:
- Through SharePoint, if SSRS is installed in SharePoint Integrated mode
- Through Report Manager if SSRS is installed in Native mode
Although Reporting Services integrates with other Microsoft technologies out-of-the-box, developers and third-party vendors can build components to support additional report output formats, delivery formats, authentication models, and data source types.
Report Builder is a free tool and meets the basic initiative of creating ways for data to get in hands of the users. Although there are three versions of Report Builder that are free and can be installed simultaneously, Report Builder looks more like Report Designer with every update.
Report Builder is intended for power users or even end users. It allows the creation of reports in a tool that looks very much like a standard Microsoft Office product. You can easily create reports that contain charts, graphs, tables, lists, and matrices. There is a section of Report Builder, called the Report Gallery, that includes reusable parts such as charts, tables, or other report regions that have been created and published. This allows end users to easily include these parts in their custom reports.
Report Designer, on the other hand, is a developer tool that is more sophisticated and includes more features than Report Builder. Report Designer is part of Business Intelligence Development Studio (BIDS) in SQL Server 2005, 2008, and 2008R2. It is part of SQL Server Data Tools (SSDT) in SQL Server 2012 and newer. Since both BIDS and SSDT are based on the Visual Studio environment, there are advanced debugging tools, programming tools, and customization features.
Report Designer allows easy creation of complex reports, including parameterized reports. Parameterized reports have a base report with flexible drop-down boxes or text boxes where users can enter parameters such as start and end dates for the report.
Report Manager is the web portal where users access deployed reports when SSRS is installed in Native mode, regardless of whether the reports were created with Report Builder or Report Designer. Alternately, when SSRS is installed in SharePoint Integrated mode, reports are deployed to SharePoint and displayed in the SharePoint site.
You can use Report Manager to perform the following tasks:
- View, search, print, and subscribe to reports.
- Create, secure, and maintain the folder hierarchy to organize items on the server.
- Configure role-based security that determines access to items and operations.
- Configure report execution properties, report history, and report parameters.
- Create report models that connect to and retrieve data from a Microsoft SQL Server Analysis Services data source or from a SQL Server relational data source.
- Set model item security to allow access to specific entities in the model, or map entities to predefined click through reports that you create in advance.
- Create shared schedules and shared data sources to make schedules and data source connections more manageable.
- Create data-driven subscriptions that roll out reports to a large recipient list.
- Create linked reports to reuse and repurpose an existing report in different ways.
- Launch Report Builder to create reports that you can save and run on the report server.
The Report Server represents the actual service and feature in SQL Server. This component is an option that can be selected during the initial installation of SQL Server or added later. The actual data for the reports does not have to be SQL Server data. Many data sources are supported, such as Oracle, MS Access, or SAP. It is possible to combine data from different sources into a single report.
When SSRS is installed, two databases, the ReportServer and ReportServerTempDB, are created. These contain the actual definition of the reports as well as metadata like cached reports or configuration information.
Microsoft has done an incredible job with SSRS in creating broad support for end users, power users, database administrators (DBAs) as well as standard Visual Studio developers. The reporting tools offer tremendous flexibility and features while being quite easy to use. Additionally, they support all types of business and create a simple and straightforward environment for business intelligence.