Your SQL Server databases are probably chock-full of data, but how do you actually get results out of the data? Scott Lowe shows how to quickly build SQL Server reports using SQL Reporting Services.
With the release of SQL Reporting Services, Microsoft has given their customers a gift. While originally slated to work only with the Enterprise edition of SQL Server 2000, at the request of a number of customers, Microsoft relented and allowed this powerful tool to work with both the Standard and Enterprise editions of SQL Server 2000.
About the tool
What, exactly, can Reporting Services do for you? In short, it can provide reports for your users. However, Reporting Services performs this function with style and provides a number of features that make report generation very easy. For example, using Reporting Services, your users can schedule reports that are sent to them at specific intervals. Now, rather than someone manually running a sales report every morning, a completed report can simply be automatically delivered to that person's mailbox.
As I mentioned, you need either SQL Server 2000 Standard or Enterprise in order to use Reporting Services (MSDE, while supported as a source database, is not useable for the Reporting Services database itself). However, this requirement is for licensing and reporting database purposes only. With Reporting Services, you can pull data from any version of SQL Server or from any ODBC data source, making it an excellent addition to just about any organization that has at least one SQL 2000 server.
Reporting Services is made up of four components:
- Report Server: The Report Server is a web application that handles the processing and management of reports created using the tool.
- Report Database: The database is an instance of SQL Server 2000 in which the Reporting Services information will be stored.
- Report Manager: Another web tool, used to manage Reporting Server.
- Report Designer: The Report Designer is integrated into Visual Studio .NET 2003 and provides a GUI in which people can write reports for use with Reporting Services.
Reporting Services has modest hardware and software requirements:
- Must installed onto a SQL Server 2000 (Standard, Enterprise or Developer) system running SP3 or later. SP4 is recommended.
- 512MB of RAM is recommended.
- If you have a 1GB of disk space available, you will be able to install Reporting Services and all of its related software.
- .NET Framework 1.1. The Reporting Services installer handles this for you, if necessary.
- Visual Studio .NET 2003 (or any component). I've installed this on my Reporting Services server as well as onto the client used to write reports.
- IIS with ASP.NET enabled.
Obtaining Reporting Services
Reporting Services is now included with retail packages of SQL Server 2000. For Open license customers, your reseller can get media for your use, for a small charge. For Select customers, Reporting Services is shipped to you as a part of your CD updates.
If you don't have a Reporting Services CD and you want to try it out now, you can also download a 120-day evaluation version from Microsoft's web site.
Installing Reporting Services
As long as your system meets the prerequisites and you have Visual Studio .NET 2003 installed on the server and have IIS enabled, you're ready to go. For this article, I am using the 120-day evaluation version of Reporting Services, so I downloaded the file and extracted the contents to a folder on my SQL Server.
Upon executing the setup.exe file, you're greeted with the typical hundred page long end-user license agreement followed by the results of a prerequisites check.
|Reporting Services makes sure that everything it needs to run is installed and working|
I'm not going to show screen shots for the next couple of screens as they're the typical software installation stuff that you've seen a thousand times. The installer asks you for your name and company name and provides you with a splash screen that kicks off the process.
The first screen of substance is the Feature Selection screen, shown below in Figure B, provides you with the opportunity to select exactly what should and should not be installed on your report server.
|Decide which options you want to install|
In this example, I've installed most of the components, with the exception of the sample database. One thing I also like about this screen is the Disk Cost button. Click it to find out exactly how much space is required on each of your drives based on the selections you've made. My disk cost window is shown in Figure C. You can change the location to which files are installed by clicking the Browse button on the Feature Selection window.
|The Disk Cost screen shows you exactly how much space you need across your server's volumes|
Since Reporting Services runs as a Windows service, it needs credentials in order to be able to function. The Service Account screen of the installer provides a place for you to specify these credentials. You can run the service using one of the built-in accounts, or, you can use a domain user account. For this example, I've opted to use the Network Service account, as shown below in Figure D. Also make sure to auto start the service by verifying that the associated checkbox on this screen is marked.
|Decide under which context and user account the Reporting Services will run.|
Next comes the IIS configuration part of the installation. On this screen, specify both the report server virtual directory and the report manager virtual directory. By default, these are "ReportServer" and "Reports", respectively. One note here: by default, the SSL option box at the bottom of the window is enabled, but you need to have a certificate ready to go on the installation server. I don't have a certificate infrastructure configured in this environment, so I opted to not use SSL, as shown in Figure E.
|Provide IIS virtual directory information for the reporting services.|
Reporting Services itself needs a place to store its goods, that is, the information about your reports. As such, the installer asks you to provide the name of a SQL Server 2000 system on which the database can be created. Further, you need to tell Reporting Services how it should connect to the SQL Server instance. Should it use a service account, a domain user account, or a SQL user account? You cannot use a service account unless you are running SQL Server 2000 SP4, or have applied a specific hotfix to an SP3 installation. I opted to use a domain user account, as shown below in Figure F.
|Determine how Reporting Services should authenticate against SQL Server.|
It also makes sense that, on this screen, you need to provide the name of your SQL Server as well as the name of the database you'd like to create for Reporting Services. The default is ReportServer, which I have used in this example.
Remember earlier that I told you that Reporting Services has the capability to automatically email reports to users. In order for this to work, it needs a mechanism by which to deliver these reports. The next screen of the installer asks you to provide an SMTP server and "From address"—the address from which reports will be sent, as seen in Figure G.
|Provide the name of an SMTP server and a valid From address|
Finally, Reporting Services wants to know a little about your SQL Server licensing. This should match whatever you're purchased for SQL Server. In this case, Figure H, my SQL server is licensed for use on two processors.
|Provide SQL Server licensing details|
Create a report
If you were to visit the Reporting Services site at this point, it would be pretty boring since there is nothing to see. Before you can really do anything with Reporting Services, you need to create a report and upload it to your report server. I'm using the copy of Visual Studio .NET 2003 installed on my reporting server to create this sample report. I'll use the sample Northwind database that comes with SQL Server.
When you start Visual Studio .NET 2003 on the reporting server, one of the options is to create a new Business Intelligence Report Project using a wizard, which I have done, as evidence by the screenshot shown in Figure I.
|Your new report can start as easily as this.|
After starting a new report project, you need to define the data source that the report will use to provide you with information. As I mentioned, I'm connecting to the Northwind sample database on the same SQL Server on which I have installed Reporting Services. This information is shown below in Figure J.
|Define the connection parameters for your report. Click the Edit button for help creating the connection.|
With a connection established, now you need to pull data from your source database using a query string. In this example, I'm going to create a report that shows all orders placed by each customer in the Northwind database broken down by customer. Click the Edit button for help creating the query. You'll see the screen shown in Figure K.
|This query pulls the data I want from the Northwind database.|
Now, in Figure L, decide whether you want the report in tabular or matrix form.
|Choose a type—any type.|
In the next step, Figure M, you're asked to do some preliminary layout of your report's data and to decide in which section a particular data element will appear. What you define here dictates at what point pages break and section breaks will occur and, basically, how your data will look. I've opted to start each customer on a new page.
|Decide where you want breaks to occur and data elements to appear|
The next screen, shown in Figure N, looks basic, but has one pretty useful feature. This screen provides a place where you can define the layout—stepped, or tabular—for your report. However, the option "Enable drilldown" can be pretty useful as it will render your report with only top level headings. To get to the detail, you need to click on the element for which you want more information. Of course, for printed reports, this would be useless, but if you just want to provide a summary report, but allow the user the option to see where the summary information came from, this can be incredibly powerful.
|Define the look and feel of the report. Optionally, enable drilldown.|
The next screen, Figure O, asks you to choose your report's overall style, including fonts. I've just used the default here.
|Choose your style.|
Now, tell the wizard to where you want to deploy this report, as you can see in Figure P. I'm deploying this report to my report server into a folder called TR Sample Report Project.
|Deploy your new report.|
Click Finish when you're done.
One last step—your report is not automatically deployed. You need to tell Visual Studio that you're ready to push it to the server. To do so, click to Build | Deploy Solution.
View the report
Open up Internet Explorer on any system and browse to http://yourreportservername/reports. You may or may not need to provide credentials, depending on whether or not your local credentials are passed through. Either way, you'll get a screen similar to the one in Figure Q.
|Your new report shows up on this screen.|
Click your report folder and then click the name of the report itself inside this folder. When you do, you'll get a little hourglass indicating that your report is being run. When it's done, you'll see it in all its glory.
|Here's the report just created in Visual Studio.|
Reporting Services is really pretty powerful and a fall 2005 update promises even more functionality, including multiple data sources. When compared with some solutions out there, Reporting Services is pretty elegant in that it provides a one-stop shop for all of your reports. In a future article, I will outline how you can restrict access to particular reports and create user groups to provide some semblance of security to your reporting system.