Getting Started with Reporting Services

Rick Dobson

Reporting Services is an enterprise system that integrates Visual Studio .NET 2003 and SQL Server 2000 to facilitate the authoring, publishing, management, and user access to data-based reports. It's a free add-on to SQL Server 2000 from Microsoft, and an upgraded version is likely to become an integral component of SQL Server 2005. Rick Dobson provides an overview of Reporting Services and shows how to get started authoring reports with it. The Reporting Services Books Online is well done, and it provides a thorough overview of the product. This article complements the built-in documentation by highlighting the integral role Visual Studio .NET plays in authoring reports with Reporting Services.

While Reporting Services requires SQL Server 2000, it's not limited to processing SQL Server data. You can author reports based on OLE DB, ODBC, and Oracle data sources, as well as with data from SQL Server databases. As a Visual Studio .NET professional, you're likely to encounter questions about Reporting Services because its Report Designer is implemented through Visual Studio .NET. Reporting Services supports an XML grammar for defining reports, so you may eventually be able to use third-party products to author reports, but the default designer for reports is Visual Studio .NET.

An overview of Reporting Services

Much of my experience over the past several years is with Visual Basic .NET and SQL Server, and before that I did database development with Access, SQL Server, and VBA. While Access is often used for report generation, SQL Server Reporting Services has an entirely different feel. Access enabled departmental developers and end users to generate reports with little or no participation from an IT department. Reporting Services does require attention from both SQL Server and Windows systems administrators. The initial release of Reporting Services manages report access and authoring through role-based security. The permissions for a role apply exclusively to Windows users and groups.

Reporting Services is exceedingly rich in its capabilities. It supports a variety of report formats and objects. You can use a standard table format as well as a matrix object for crosstab reports. Authors can also include charts and sub-reports in their reports. You can optionally enable drill-down capabilities for the values within a report and make reports dynamic via parameter values. You can even drill down from one report to another. There's also a Freeform format for displaying data as a form. You can publish reports in several formats, including HTML, Excel, comma-delimited text, PDF, TIFF, Office Web Components, and XML.

One important reason for Visual Studio .NET developers to focus on Reporting Services is that Visual Studio .NET implements the Report Designer. After installing Reporting Services, you'll notice a new project type when you choose to start a new project with Visual Studio .NET. The Business Intelligence Projects type includes two templates. Use the Report Project Wizard template to walk through a series of screens that automates report authoring. Use the Report Project template to generate a blank report that you can format manually. With Visual Studio .NET, you can connect to a data source, develop a query, format a report based on the query, and deploy a finished report to a Report Server.

Microsoft created a Reporting Services site (www.microsoft.com/sql/reporting/default.asp) with many resources to help you ramp up to speed on this powerful new capability. You can install Reporting Services on any computer with SQL Server 2000. You'll also need Visual Studio .NET 2003 on any computer used for report authoring. Reporting Services is implemented as a Web Service, and users and administrators can connect to the site for the Web Service at http://hostsitename/reports. The service automatically exposes the Report Manager UI that facilitates such tasks as surveying, accessing, and managing reports. The range of capabilities exposed through the UI depends on the permissions associated with the role for the Windows user making the connection to the Report Server through Report Manager.

Starting a project for a report

You can either create a report with the help of the Report Wizard or manually compose a report by starting with a blank report. In either case, you start by creating a new project with Visual Studio .NET. Select Business Intelligence Projects, and then click one of the two report template icons. Figure 1 shows the selection for using the Report Wizard in a project named HCVSReporting.

Whether you create a report with the wizard or manually, you'll need to designate a data source, specify a query, format a report layout, preview your report, edit the report until it meets your requirements, and eventually deploy the report to a Report Server. You can fulfill these goals with either approach to creating a report, but the Report Wizard is particularly convenient for getting started.

Creating a report to list customers

After the Welcome screen, the Report Wizard allows you to specify a connection for the report's data source. By clicking an Edit button, you can open a Data Link Properties dialog box on top the wizard screen for selecting a data source. You can use the Data Link Properties dialog box to initially specify a report's data source or to edit a data source before exiting the wizard. Optionally, the wizard allows a report author to type or copy the connection information for a database. Figure 2 shows the Data Link Properties dialog box for a connection to the Northwind database on the local SQL Server using integrated security.

The next wizard screen lets you specify a query based on the data source to populate the report. You can use Microsoft's familiar graphical query designer or just type in a SQL string. Figure 3 shows a query that returns four columns from the Customers table in the Northwind database.

Additional wizard screens allow you to format a report's layout. For example, the customer list report for this section uses a table to display the data for customers in its successive rows. You can use built-in report bands as well as add your own bands for positioning a report's data. In the customer list report, all column values appear in the built-in details band. This specification designates that all column values appear on each row of the table. The next-to-last wizard page allows you to specify a Report Server and folder on the server to host the report from the project. Subsequent reports from the project go to the same folder on the server. The final wizard page allows you to override the default name for your report and summarizes your choices. You can back up to prior screens and edit your selections from this final page before clicking Finish to create your report.

After clicking Finish in the Report Wizard, the focus returns to Visual Studio .NET. Figure 4 shows the Layout tab for the CustomerList report. Notice that two additional tabs give you alternative report views. By selecting the Preview tab, you can display the report in a way that looks similar to how a browser shows the report. The Data tab shows a graphical view of the report's query. You can edit the query from this tab to select different information for the report.

Figure 4 reveals Solution Explorer and the Properties window to the right of the report's layout view. You can see the CustomerList.rdl file selected in Solution Explorer. This file contains the XML for the report. Right-clicking the icon for CustomerList.rdl and choosing View Code shows the XML defining the report. The Properties window shows the Report object selected with selected property settings. The Report object is a top-level object that contains other objects, such as Body, Table, and TextBox instances for displaying labels and column values. You can select individual objects by clicking on them in the Layout tab or selecting them from the dropdown list at the top of the Properties window. This capability permits an author to revise a report's formatting by editing property settings for a selected object.

After fine-tuning a report so that it appears the way you prefer, you can deploy the report. Initially, you can deploy a whole project to the Report Server you designated when initially specifying the report by choosing Build | Deploy Solution. This creates a folder for the reports associated with a project on the Report Server. The folder has the same name as the project. You can view the contents of report folders with Report Manager. To open the Report Manager on a computer running a Report Server, from the Windows Start button choose All Programs | Microsoft SQL Server | Reporting Services | Report Manager. Figure 5 shows the CustomerList report in the HCVSReporting folder of the Home folder on the cab233a Report Server. I installed Reporting Services on the cab233a computer in my office. The Home, HCVSReporting folder path is highlighted at the top of Report Manager, and the report appears in a window at the bottom of Report Manager.

As its name implies, Report Manager allows you to manage the reports on a Report Server. For example, you can select an Excel format for a report in the "Select a format" dropdown box above the report and export the report as an .xls file.

Creating a grouped report

The Report Designer includes numerous features to simplify report formatting. This section builds on the preceding one by highlighting how to create a report that groups rows from a query. In addition, discussion of the report preparation demonstrates how to apply a specialized format to a column of values–namely, a currency format.

After creating a new or opening an existing Visual Studio .NET project, you can still invoke the Report Wizard from Solution Explorer. Right-click Reports in Solution Explorer and select New Report. This opens the Report Wizard Welcome screen. The second sample report for this article used this approach to start a new report based on a connection to the Northwind database. The query for the report (shown in the following code) draws on the Customers and Orders tables along with the Order Subtotals view.

SELECT Customers.CustomerID, 
  Customers.CompanyName, 
  [Order Subtotals].OrderID, 
  [Order Subtotals].Subtotal
FROM Customers 
  INNER JOIN Orders 
  ON Customers.CustomerID 
  = Orders.CustomerID 
  INNER JOIN [Order Subtotals] 
  ON Orders.OrderID 
  = [Order Subtotals].OrderID

The query returns CustomerID and CompanyName column values from the Customers tables along with OrderID and Subtotal column values from the Order Subtotals view. Although the query returns a results set that repeats the CustomerID and CompanyName column values for each unique pair of OrderID and Subtotal column values, the two columns from the Order Subtotals view nest within the two columns from the Customers table. The sample report for this section highlights this nested relationship by grouping OrderID and Subtotal values within the CustomerID and CompanyName column values for a customer.

The Report Wizard's "Design the Table" page enables a report author to specify a grouped relationship in a report between the column values in its query. Figure 6 shows the selection of CustomerID and CompanyName as columns that group sets of one or more OrderID and Subtotal column values.

Unfortunately, columns in the group box nest hierarchically. Therefore, CompanyName nests within CustomerID although both columns are at the same grouping level. You can manually resolve this inappropriate nesting from the report's Layout tab. The trick is to move the CompanyName field to the same grouping level as the CustomerID field and delete the wizard-generated grouping level for CompanyName.

Because the Subtotal column values are currency values, you can clarify the interpretation of report values by applying a currency format to the column values. You can do this by right-clicking the Subtotal field in the Layout tab. Within the Textbox Properties dialog box, select Currency from the Standard list box. Then, click OK. Figure 7 shows how the report looks from the Preview tab.

After designing a report, you'll typically want to deploy it. If you just have one report to deploy to a previously existing folder on a Report Server for a project, you can right-click the report in Solution Explorer. Then, choose Deploy to distribute that report to the Report Server. Choose Build | Deploy Solution to deploy multiple reports, and perhaps other objects, to a Report Server.

Creating and using shared data sources

The two preceding reports used custom data sources for each report. Using custom data sources is particularly appropriate for cases in which a project contains a collection of reports that each connects to a different database. However, when multiple reports in a project connect to the same database, it's more efficient to create a shared data source and reuse that shared data source in multiple reports. You can create a shared data source from a Visual Studio .NET project, and then assign it to new reports created with the Report Wizard or reports that you create manually. Shared data sources can be deployed to a Report Server just like a report. A Report Manager administrator can edit the data source for a deployed report from a custom data source to a shared data source and vice versa.

You can easily add a shared data source to a project by right-clicking Shared Data Sources in Solution Explorer and choosing Add New Data Source. This opens a Data Link Properties dialog box just like the one that the Report Wizard opens. You can make selections from the dialog box and click OK to add a shared data source to the project. The data source has a name that you can edit using the Rename context-sensitive menu command or the Properties window for the data source. Right-click the data source and choose Deploy to copy the data source to the Report Server from the project. Alternatively, choose Build | Deploy Solution to distribute objects from a project to its Report Server. You can assign a deployed shared data source to a report from Report Manager. In the sample project for this article, I created and deployed a shared data source named Northwind. The shared data source specified a connection to the Northwind database with integrated security.

To assign the Northwind shared data source to a report, open Report Manager from the Windows Start button by choosing All Programs | Microsoft SQL Server | Reporting Services | Report Manager. Click the folder for a project in Report Manager and then click the name for the report, such as GroupedReport, to receive a new data source assignment. This displays the report. Next, select the Properties tab for the report and then choose the Data Sources item on the left banner. This displays a page for assigning a new data source to a report.

Figure 8 shows the Data Source Properties page after editing to use the Northwind shared data source. Recall that the sample project for this article has the name HCVSReporting, and Report Manager assigns the same name to a folder for holding objects deployed from the project. The Data Source Properties window shows the shared data source radio button selected. The name for the shared data source begins with a slash, followed by the folder name, a second slash, and the project name for the shared data source. Clicking Apply at the bottom of the page replaces the custom data source created for the report by the Report Wizard with the shared data source created later.

Creating a report manually with a table

To add a new report to a project without the help of the Report Wizard, choose Project | Add New Item, highlight the Report icon in the Templates pane, and assign the report a name, such as ManualReportWithTable. Before formatting the report, you need to specify a data source and query for the data source. Reporting Services uses the name dataset to refer to the query, but the term dataset doesn't designate an ADO.NET DataSet object. Instead, the term dataset in Reporting Services designates a SQL statement that returns a set of rows from a data source.

When you install Reporting Services, you can opt to install the SQL Server AdventureWorks2000 database. To give you exposure to this sample database, the manual report sample uses it. You can apply the guidelines from the preceding sample to create a shared data source for the AdventureWorks2000 database. After adding a new blank report to a project, you must initially assign it a dataset. This task actually involves two steps. First, you must designate a data source, such as a shared data source for the AdventureWorks2000 database. Second, you must specify a SQL statement that returns a row set with the column values that you need for your report.

Figure 9 shows the Dataset dialog box for designating a data source and specifying a SQL string for the ManualReportWithTable report. The query returns CustomerID and the store name along with the full name and phone of a store's contact person. In addition, the first letter of the store name is returned as a grouping variable for the rows, which are sorted by store name.

After specifying the data for a report, you can start formatting the report. The Layout tab can expose a Toolbox with items to hold data for a report, such as a Table control. The table is a banded object that has a built-in set of table header, details, and table footer bands. You can add grouping bands for selected fields. The Fields window, which you can show with the View | Fields command, includes a list of field names. You can drag field names from the Fields window to populate table cells within a table's bands. By right-clicking a column or a band border, you can add extra columns or grouping bands. Figure 10 shows the ManualReportWithTable report with a Table control and some fields dragged to built-in bands. In addition, you can see a group band for the FirstLetter field. The overall process of formatting a report is a cross between laying out a Windows form and an Access report.

Conclusions

Reporting Services offers a new capability for SQL Server 2000. Report authoring via this new SQL Server capability depends critically on Visual Studio .NET. Therefore, Visual Studio .NET developers with a grasp of Reporting Services are in a position to train clients to create their own ad hoc reports as well as create advanced report generation and management capabilities for their clients. In any event, Reporting Services increases the value of Visual Studio .NET and developers who know how to use it to organizations with reporting requirements.

Download 408DOBSON.ZIP

To find out more about Hardcore Visual Studio and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the August 2004 issue of Hardcore Visual Studio. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Hardcore Visual Studio is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.