SQL 2005 Reporting Services Part 1
January 5, 2006
Included with SQL Server 2005 is a group of interrelated applications, collectively known as SQL Server Reporting Service (SSRS). SSRS includes all the development and management pieces necessary to publish end user reports in HTML, PDF, Excel, and CSV formats. Originally released as a SQL 2000 separate add on that could be downloaded from the web, all the Reporting Server pieces are now bundled in SQL 2005. With Reporting Services built into SQL, expect to see product adoption rise quickly. Microsoft's accounting package, Solomon, will soon discontinue use of Crystal Reports in favor of SSRS.
If you have experience with a banded reporting tool, such as Crystal Reports, or Microsoft Access, then SQL Server Reporting Services will be familiar as it includes a drag and drop report builder called Report Designer. In the past, SQL's lack of a report builder was usually one of the first stumbling blocks run into by Access developers looking to upgrade their application to SQL.
In addition to the drag and drop report builder, SSRS also includes an application for letting power users generate their own ad hoc reports called Report Builder.
Another unique feature of SSRS is the ability to create report subscriptions, where users can request reports to be emailed to them automatically at various intervals.
One of the most compelling features of SQL Server Reporting Service is the cost; it is FREE, sort of. The entire SSRS package (report processor, tools, and designers) are included free with the cost of SQL 2005. In addition, each user viewing a report is required to have a valid SQL client access license (CAL). In the case where the SSRS reports will be displayed as HTML over the web, where tracking the number of users is not practical, a SQL CPU license is required. Licensing details can be found at http://www.microsoft.com/sql/technologies/reporting/howtolicensers.mspx . This article is valid for both SQL 2000 and 2005 Reporting Services.
SQL Server Reporting Service is installed from the SQL Server media. Have IIS (Internet Information Server) preinstalled and running prior to setting up Reporting Services. If IIS is not running, you may receive the error 1603 during the SSRS setup. In addition, have the service DTS (Distributed Transaction Service) running and configured to run at startup. This can be done from the Windows Services applet. Next, during SQL setup from the SQL 2005 CD, check the Reporting Services option during the Component Install section.
The examples in this series of articles will use data derived from the Adventure Works sample database. To have this database installed on your machine, click the "Advanced" button during setup, then select Documentation, Sample Databases, and select Adventure Works OLTP.
The SSRS installation will create two new IIS sites, Reports and Report Server. The Reports physical directory is located at C:\ Program Files \Microsoft SQL Server \MSSQL.3 \Reporting Services \ReportManager. The Report Servers directory can be found at C:\ Program Files \Microsoft SQL Server \MSSQL.3 \Reporting Services \ReportServer.
The Reports virtual directory contains the Report Manager Web application. After install, directing a web browser to //your server name/Reports (//localhost/reports in this example) should bring up the Report Manage home page shown below.
The Report Manager is a web-based application for the organizing, securing, and displaying of created reports. Report Manager hosts a myriad of features including:
The other virtual directory, Report Server, is a web service for requesting reports without going through the Report Manager GUI. By passing arguments in the http query sting, reports and options can be requested. Browsing without any arguments should generate the web page shown below.
Setup also creates two SQL databases, Report Server, where the created reports reside, and Report Server Temp DB, where cached copies of reports are stored.
To check on the success of the installation, or to configure options after install, Reporting Server included a tool called Configuration Manger. It can be found in the Windows program group "Microsoft SQL Server 2005," "Configuration Tools," "Reporting Services Configuration" and is displayed below.
When first logging into Configuration Manager, you will be prompted for the SSRS machine and instance name. After login, the initial screen, "Server Status," will be displayed. Initialized should equal "Yes" and the service status should be "Running." The next screen, "Report Server Virtual Directory," can be used to change the name of the IIS virtual directory and require SSL if wanted. The "Report Manager Virtual Directory" is similar to the previous screen. A screen not to be overlooked is the Encryption Keys.
Logins and passwords used by the Reporting Service are encrypted. These include the login information used in reports and data sources. If the key becomes corrupt, Reporting Services will stop working. As a precaution, the keys should be backed up by following these steps:
Take this file and secure it. In the future, if the SSRS key becomes corrupt, this same screen can be used to restore the file just made.
Actual report creation is done in a drag and drop application called Business Intelligence Development Studio (BIDS). BIDS is located in the Windows program group "Microsoft SQL Server 2005," "SQL Server Business Intelligence Development Studio." The Business Intelligence Development Studio allows for easy creation of data sources and report formatting. As an alternative, if you already use Visual Studio for other development projects, this tool can create Report Service projects as well.
SQL Server Reporting Services can be used to create end user reports in several different formats including HTML, PDF, and Excel. All the tools necessary for report creation and management are included with SQL Server. The price, free with SQL licensing, makes SSRS very attractive. In the next article, we will begin using BIDS to create reports with datasets coming out of SQL Server 2005.