SQL Server 2000 Reporting Services, the SQL Server-based enterprise reporting solution Microsoft released in January 2004, is positioned to become one of the most popular SQL Server components. Nearly all organizations need to produce reports from their data, and with Reporting Services, Microsoft filled this large hole in SQL Server's toolkit. You can install Reporting Services on any SQL Server 2000 computer at no additional cost, and you'll be able to install it as part of SQL Server 2005.
In spite of the solution's benefits and the excitement surrounding its initial release, many SQL Server professionals have limited or no hands-on experience with Reporting Services. If you're like many database professionals, you might have put off using Reporting Services because of its relative newness, the fact that it requires a separate SQL Server installation that works along with your production SQL Server, or maybe its list of prerequisites. But Reporting Services isn't so new any more, and Microsoft has released Reporting Services Service Pack 1 (SP1), which fixes the bugs in the initial release. In addition, Microsoft is integrating Reporting Services with SQL Server 2005, so learning how to use Reporting Services now will give you a head start on SQL Server 2005. This article gives you the basics for getting started with Reporting Services and includes SP1 examples that you can reproduce in your test environment. I start by giving you the prerequisites for using Reporting Services and explaining where to get it. Then, I walk you through the steps for authoring two reports and for deploying those reports to the Report Server, Reporting Services' main component. Finally, I teach you two ways to view deployed reports.
Installing Reporting Services
To properly install Reporting Services, your system needs four elements. First, you need Windows Server 2003, Windows XP, or Windows 2000 with the most recent service packs installed. (For a list of service packs for each OS, see the Microsoft article "Reporting Services System Requirements," which is listed in Related Reading.) Second, you need Microsoft IIS because Reporting Services runs as an XML Web service. Third, you need the standard, enterprise, or developer edition of SQL Server 2000. (Reporting Services isn't compatible with earlier SQL Server releases.) Fourth, report designers need Visual Studio .NET 2003, which hosts Reporting Services' Report Designer component. (For administrators who don't design reports, Reporting Services provides a different UI that permits the creation of folders, data sources, and users and the assignment of permissions to users.)
After you make sure your system meets the prerequisites, you can install Reporting Services, then install SP1 to update the initial release. You can download a trial version of Reporting Services at the URL in Related Reading.
Creating Your First Report
The only Report Designer Microsoft offers for authoring Reporting Services reports is in Visual Studio .NET 2003. When you install Reporting Services, the installation process automatically updates Visual Studio .NET by adding a new project type called Business Intelligence Projects. You don't necessarily need to have Visual Studio .NET installed on the same server as Reporting Services. As I explain in a moment, you can reference a target-server URL for Reporting Services, which can be different from the location of the workstation you use to run Visual Studio .NET. Within this project type are two templates named Report Project Wizard and Report Project. Both templates let you perform the steps to create a report: defining a report's data source, specifying a report's layout, previewing a report, and deploying a finished report to the Report Server.
To create your first report, start a new Business Intelligence project in Visual Studio .NET, and choose the Report Wizard Project template. Name your project SSMRS-Intro. Read the wizard's welcome screen, then click Next to go to the Select the Data Source screen and specify the report's data source. Click Edit to open the familiar Data Link Properties dialog box that Figure 1 shows. On the dialog box's Provider tab, select Microsoft OLE DB Provider for SQL Server as the type of data you want to connect to. As Figure 1 shows, the dialog box's Connections tab lets you specify on the local SQL Server instance a Windows NT Integrated security-based connection to the Northwind database. Click Test Connection, then click OK to return to the Select the Data Source screen, which now shows a connection string that points to a data source named after the database. Note that unless you select the Make this a shared data source check box at the bottom of the screen, the wizard embeds the data source so that you can use it exclusively for this one report.
Clicking Next opens the wizard's Design the Query screen. You can either type an SQL query statement into the Query string text box or click Edit to open a graphical query designer that operates like the query builder in Enterprise Manager. For this example, you can use the following query:
SELECT CompanyName, ContactName, Phone, Country FROM Customers WHERE (Country = 'Canada') OR (Country = 'Mexico') OR (Country = 'USA')
Then, click Next to open the Select the Report Type screen. The wizard offers two report types: tabular and matrix. The matrix type is for a cross-tab report, which we won't create in this article's examples. For this demonstration, select Tabular.
Figure 2 shows the next wizard screen, Design the Table, which lets you put the query fields where you want them in the report. Click Details to move the field names from the Available fields list box to the Details list box. These selections cause the fields to appear in a report's Details section. You can optionally create additional groupings around the Details section by adding fields to the Group list box. Clicking Next opens the Choose the Table Style screen. You can accept the default selection of Bold or highlight one of the other report styles. A preview window gives you a feel for how the different styles present your data.
When you're running the Report Wizard for the first time in a project, the Choose the Deployment Location screen appears next. The wizard automatically populates the Report Server and Deployment folder text boxes. Because the Report Server for this article's examples runs from the local IIS Web server, the Report Server text box shows the path http://localhost/ReportServer. During installation, you specify the name of the Web server that hosts Reporting Services. By default, the wizard names the deployment folder after the project's name—in this case, SSMRSIntro.
The final wizard screen assigns a default name to the report and shows a summary of the selections from the previous screens. The initial default report name in a project is Report1. When you're creating your own reports, you can change the default name to something more meaningful.
After you close the wizard, you're in the Visual Studio .NET report-design environment. Each report has three tabs: one to specify its data source, another for its layout, and a third to preview how it displays data. Figure 3 shows part of the Preview tab for Report1, which shows how the report will look after you deploy it. Report1 is for one specific data source, but Reporting Services lets you use parameters to vary the output in a report. For information about how to use parameters in Reporting Services, see Rodney Landrum's article "Pushing the Parameters," August 2004.
Creating a Drilldown Report
For your second report, let's use a shared data source instead of an embedded one, as you did to create Report1. A shared data source is useful because you can reuse it in multiple reports. Start by right-clicking Shared Data Sources in the Solution Explorer, which you see in Figure 3's right pane, then choosing Add New Data Source to open a Data Link Properties dialog box like the one that Figure 1 shows. Complete the dialog box to specify Northwind as the data source, as you did for Report1. This process adds a new entry with the name Northwind.rds nested below Shared Data Sources in the Solution Explorer.
Open the Report Wizard by right-clicking Reports in the Solution Explorer and choosing Add New Report. In the Select the Data Source screen, the wizard automatically selects Northwind as the database, referring to the Northwind.rds shared data source. If you had more than one shared data source, you could open the Shared Data Source drop-down box and select another shared data source.
For the second report, enter the same query that you used for Report1 and select a tabular report style. In the Design the Table screen, add Country to the Group list box, and add CompanyName, ContactName, and Phone to the Details list box. Because you selected an item for the Group list box, a new screen called Choose the Table Layout appears before the Choose the Table Style screen. The table layout screen includes a check box called Enable drilldown. (You must select the Stepped button to make the Enable drilldown check box available.) Select Enable drilldown so that CompanyName, ContactName, and Phone column values will appear only after a user drills down to them by expanding a Country column value. Click Finish, and accept Report2 as the second report's name.
Figure 4 shows how Report2 looks in the Preview tab. Clicking the expand icon (+) next to a country name drills down to the fields nested within the group value and changes the + to a -. Notice that in Figure 4, you can view the CompanyName, ContactName, and Phone column value for the customers in Mexico, but not for either of the other two countries. Clicking the expanders for either of the other two countries will expose their hidden nested column values.
Deploying a Solution
In Reporting Services, deploying a solution is the process of publishing the reports, shared data sources, and related file items from a Visual Studio .NET project to a folder on a Report Server. Administrators can set permissions to restrict user access to reports and other solution items (e.g., shared data sources) on a Report Server.
When you right-click a project in the Solution Explorer and invoke the Build, Deploy Solution command from a Visual Studio .NET project, you publish items from a solution to a folder on a Report Server. The first time you run the Report Wizard, the folder's name and the Report Server URL appear on the Choose the Deployment Location screen. If the folder's name doesn't exist on a Report Server when a report author invokes the Build, Deploy Solution command, Report Server creates a new folder.
You can view and update the deployment folder and Report Server URL settings from a project's Property Pages. Right-click the project name in the Solution Explorer pane and choose Properties to open a project's Property Pages dialog box. The TargetFolder setting corresponds to the deployment folder for a project, and the TargetServerURL setting contains the URL for the Report Server that hosts a solution's target folder. Figure 5 shows the Property Pages dialog box for the SSMRSIntro example project. Alternatively, you can change a report's deployment location by using the Reporting Services Report Manager application after you publish the report.
Viewing Deployed Solution Items
After you deploy reports and related items from a project to a Report Server, you can view them in one of two ways. First, you can use URL access to read the contents of reports with read-only permissions. Second, you can invoke Report Server for a richer mix of capabilities, including Reporting Services administration. Both approaches require a Windows account on the local Windows server or a Windows account from another trusted Windows server. Administrators have unlimited permissions, including assigning users to predefined and custom roles with permissions to perform tasks, such as reading a report.
Connecting to Report Server through URL access. You can connect to a Report Server by navigating to its URL address from any user account that has permission to connect to it. For example, the IIS server hosting the Reporting Services Report Server in my office is called cab233a. Other computers in my office can connect to the Report Server at the URL http://cab233a/ReportServer. A user who has an authorized user account can navigate a browser to this URL and view a page showing links to folders on the Report Server. The link for the SSMRSIntro folder opens a Web page containing links for the two example reports in this article and the shared data source. The links are named after the item names in the SSMRSIntro project; the Report1 link opens Report1 in the browser.
Figure 6 shows an excerpt from the URL-accessed view of Report1. Notice that the report appears the same as it does in Figure 3, but the Address box shows a URL that contains a command to render the report (rs:Command=Render). In addition, the Select a format drop-down box near the top of the pane lets users save the report in a variety of useful formats. For example, selecting Acrobat (PDF) file from the drop-down box lets users save a local copy of the report in PDF format for offline use.
Invoking Report Server. Users who have appropriate permissions can connect to the Report Server by navigating to http://servername/reports. For this article's examples, the server name is cab233a. Figure 7 shows a connection to the cab233a Report Server and a folder list in the Home folder. Clicking any folder (e.g., SSMRSIntro) in a Home folder reveals the clicked folder's contents. Users can use the Report Server folders to perform tasks according to the role assignments for their Windows account and any Windows groups they belong to. An administrator has all possible permissions. Report Server automatically adjusts its UI to expose permissions and items consistent with the role of each user.
Beyond the Basics
Reporting Services is Microsoft's first entry into the enterprise reporting platform market. I like Reporting Services because it's easy to install and use. Reporting Services will be even more tightly integrated in SQL Server 2005. Learning it now will help you later as you start learning SQL Server 2005. As you work with Reporting Services you'll discover that its capabilities go far beyond what I cover in this tutorial, but you can use the information in this article as a first step to expanding your enterprise reporting capabilities.
"Pushing the Parameters," August 2004
BRIAN LARSON AND MARTIN VOEGELE
"Reporting Services: It's De-lightful," December 2003
Trial version of Reporting Services
"SQL Server 2000 Reporting Services Service Pack 1"
"Reporting Services System Requirements"
"Reporting Services Trial Software"
"Walkthrough—Creating a Basic Report"
"Walkthrough—Adding Grouping, Sorting, and Formatting to a Basic Report"
"Get to Know Reporting Services," SQL Server Perspectives, February 6, 2004
"An Offer You Can't Refuse," February 2004
SCOT J. REAGIN
"Investigating Reporting," September 2004