Reporting Services, a feature in SQL Server that supports reporting, can be installed in one of two modes: SharePoint Integrated or Native (non-SharePoint) mode. This option appears at the time of installation and can be changed later. Once selected, the mode determines where users will view deployed reports.
There are two major sub-components within SQL Server Reporting Services (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
- Stand-Alone
Report Builder
Report Builder is a free tool and meets Microsoft’s initiative of putting data in the hands of the user. To date, there are three versions of Report Builder that are fee and can be installed simultaneously. With every revision Report Builder looks more like Report Designer.
Report Builder is intended for end users or power users. It allows the creation of reports in a tool that looks very much like a Microsoft Office product. Users can easily create reports that contain charts, graphs, tables, lists and matrices. There is a section of Report Builder, called the Report Gallery. The gallery can include 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 reports. Parts can be charts, tables, or other report regions that developers create.
Here is an example of a report created using Report Builder:
Source: https://msdn.microsoft.com/en-us/library/dd220460.aspx
Report Designer
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 2014. 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.
Below is an example of a report created using Report DesignerIn this example, Report Month, Employee and Report Year can all be chosen to customize the base report.
Source: Microsoft AdventureWorks Free Sample Reports
Report Manager
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. Here is an example of Report Manager.
Source: Microsoft Report Manager
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, and 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, data base administrators (DBAs) as well as 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.
Related Training
Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services
Implementing Data Models and Reports with Microsoft SQL Server
MCSE: Business Intelligence Boot Camp