asp:feature
LANGUAGES: SQL | All .NET Languages
ASP.NET VERSIONS: 1.0 | 1.1
Develop Reports With SQL Server 2000 Reporting Services
Simplify the integration of data in your reporting solutions.
By Tom Rizzo
As a developer, you deal with data in your everyday life. Either you are building custom applications that work on data, such as creating orders from a Web site, or you are writing applications that report on your data, such as custom Web applications that show sales trends or inventory information. One of the key issues we hear from Web developers is that building, maintaining, and integrating data into their reporting solutions is costly, time-consuming, and in the end, difficult to do. To solve this problem, SQL Server 2000 Reporting Services was born.
For those who have not heard of this new product, it currently is in beta; hopefully by the time you are reading this article Reporting Services will have been released. Reporting Services was originally part of the SQL Server "Yukon" release, which is due out in 2004. However, based on the overwhelming positive customer feedback for the product, Microsoft decided to ship Reporting Services earlier and separate from Yukon.
This article will step you through the Reporting Services architecture and features. Then we'll look at solving a business problem, which is how to build rich, dynamic reports in the least amount of code, using the sample AdventureWorks database that ships with Reporting Services. We could build our solution using only custom ASP.NET code, but Reporting Services, which builds on ASP.NET, makes writing our reports faster and easier.
Understand the Architecture
Before diving into how to develop against Reporting Services, we first need to look at the architecture of Reporting Services. Figure 1 shows the Reporting Services architecture. Let's step through each of these components to understand what functionality they provide to your reporting solution.
Figure 1. Reporting Services is made
up of six key pieces: the Report Server, the SQL Server catalog, Data Retrieval
Services, Security Services, Rendering Services, and Scheduling and Delivery
Services.
The Report Server: The Report Server contains the other services in Reporting Services. The interesting part of the Report Server is that you can create a Web farm of Reporting Servers. This allows you to scale your report infrastructure to very large amounts of users either dynamically running reports or having the system send reports on a scheduled basis. You'll find that when you install Reporting Services, a new service called ReportServer will be installed on the machine. In addition, a new virtual directory called Reports will be created. These two components make up the majority of your Reporting Services infrastructure to manage, execute, render, and schedule your reports. Figure 2 shows the Report Manager interface from which you can manage the reports you create with Reporting Services.
Figure 2. The Report Manager
interface lets you manage the reports you create with Reporting Services.
The SQL Server Catalog: To maintain your configuration settings, report definitions, datasource settings, user subscription information, and also snapshots of any reports that you want stored for auditing or other uses, Reporting Services requires a SQL Server system to store its catalog information. You can decide whether you want the SQL Server on the same machine as your Report Server or you can put SQL Server on a different machine to separate the load. You'll want to make sure that the SQL Server you use is highly available since Reporting Services is dependent on the information in the SQL Server for its operation.
Data Retrieval Services: As part of any report, you are going to want to be able to retrieve data. Reporting Services supports many different standard ways to retrieve data, such as ADO.NET, OLEDB, and ODBC. Using these built-in data access methods you can connect to the multitude of datasources contained in your companies and report on the information contained in those datasources. The interesting aspects of Reporting Services when it comes to data retrieval is that Reporting Services supports multiple datasources in a single report, and also supports shared datasources.
By having multiple datasources on a single report, you can combine heterogeneous datasources such as SQL Server, Oracle, DB2, Access, or even comma-delimited textfiles into your reports. Also, you can combine relational data with OLAP data in Reporting Services using the multiple datasource capabilities.
With shared datasources you can share a datasource between many reports. By providing this capability, you can change parameters on the datasource once and have all your reports use the new parameter. Imagine the scenario where you create a hundred reports to a datasource individually on each report and then the password changes for the user account that you use to access the datasource. Now, you would have to go back and change the connection string in your code for all your reports. With a shared datasource, you change the shared datasource once and all reports automatically use the new and updated information in the shared datasource.
Security Services: To support authorization and secure access to your reports and your report data, Reporting Services supports a number of security services. One authorization provider Reporting Services supports is Windows-based authentication. You can secure your reports and the datasources used in your reports using Windows-based authentication. You can also use other types of authentication, but the most common scenario is to use Windows-based authentication.
Rendering Services: One of the key requirements for any reporting system is the ability to render the report in many different formats depending on the user's requirements. For example, some reports may be ok as HTML but other reports may need to be exported to Excel for more analysis or to a print-quality image, such as a TIFF image. When building your own reporting solution, you must build these capabilities yourself. However, with Reporting Services, support for outputting reports to many formats without custom code is built in. The formats that Reporting Services supports are HTML 3.2, HTML 4.0, CSV files, XML, Microsoft Excel (2002 and later), Images (such as TIFF), and Adobe Acrobat (PDF).
Scheduling and Delivery Services: The final piece of the Reporting Services architecture is the Scheduling and Delivery Services components. These components of the system allow you to either push reports to users on a schedule, such as 9 am every morning, or run on-demand reports when users browse to a report through the Reporting Services Web site. Furthermore, these components allow you to specify to which delivery channel a report should be delivered. You may want reports sent via e-mail or you may want them posted to a file share based on a timed snapshot of the report. Reporting Services supports all these different scenarios for you to schedule when a report should run, how it should be customized for individual viewers of the report, and, finally, what format and delivery mechanism the report should use to deliver the report to the consumer of the report.
Develop a Report
Now that you understand all the pieces that make up the infrastructure for Reporting Services, we'll look at developing a report using this infrastructure. Reporting Services integrates with and extends Visual Studio .NET 2003. Included with Reporting Services is a Report Designer add-in for Visual Studio .NET. Using this add-in, you can quickly build your reports, deploy them to your report server, or debug your reports to determine any errors you have. Figure 3 shows the new project types included with the Report Designer. Using the wizard helps kick start creating your reports.
Figure 3. Reporting Services
integrates with and extends Visual Studio .NET 2003. You can either use the
Report Project Wizard or use the Report Project to start from scratch.
After we select the type of project we want to use, we need to lay out our report. If you use the wizard, you'll find a new report generated for you depending on how you answer the questions through the wizard. You can also start from a blank report. The Report Designer includes a number of built-in controls that you use to design your report, including the Textbox, Line, Table, Matrix, Rectangle, List, Image, Subreport, and Chart controls. Although I don't have the time and space in this article to explain all these controls to you, the help included with Reporting Services explains in detail the capabilities of each control. For the most part, you'll use the Table and Chart controls when building your first reports.
Beyond built-in controls, Reporting Services also includes the ability to use the graphical query tools in Visual Studio to create the queries that will connect to your datasource and return your data. After you've created your query, as shown in Figure 4, you can use the built-in Fields window to drag and drop your fields onto the report surface. For anyone who has used Access reports, dragging and dropping using the Fields window should feel familiar.
Figure 4. Reporting Services lets
you use the graphical query tools in Visual Studio to create the queries that
connect to your datasource and return your data.
The sample application included with this article shows you how you can build sales reports using Reporting Services. The sample uses the AdventureWorks 2000 sample databases included with Reporting Services. Make sure to install the AdventureWorks database when you set up your Reporting Services environment.
To create the sample, I used the Report Project Wizard, entered a SQL query string to get back the sales data from the database, then used the Wizard to lay out the report for me automatically. Once I had the basic report, I customized the report using the built-in Reporting Services Designer tools. Now that you have a basic report that you can start with I'm going to step you through some of the more advanced capabilities that you can do with Reporting Services.
One of the common tasks you will want to perform is to work with the Table control. This control provides the ability to drag and drop fields, create headers and footers, and also to set your grouping for your report data. The Table control also allows you to add expressions using the Visual Basic .NET expression language as a column in the table. For example, you may want to add a total column for all the sales in your reports. You could do this on the server in a stored procedure or as part of your query, or you could do it within the report as an expression. You can use the different intrinsic report values in your expression such as page number, the parameters you pass to your report (which we will learn about later), and, finally, the fields themselves.
Using the expression language you can do string functions, conditional formatting, and math functions. In the report, I use the Sum function to add all the sales to a particular store. You will also find yourself using the conditional formatting capabilities often to perform functions like mark columns red if they are below plan or green if they are above plan. The following expression, when used in the Color property or BackgroundColor property of a textbox, performs this functionality in Reporting Services:
=IIf(Fields!Cost.Value > Fields!Revenue.Value,
"Red", "Green")
If you think you'll use an expression in more than one place, you can add it to the Fields window. This will allow you to drag and drop your new expression-based field to many different locations on a report or to multiple reports in your project.
Add Parameters
In many reports, you want to customize the report depending on who is requesting the data. For example, a sales manager may only want to see the data for his/her sales territory while sales representatives will want only their particular sales data for their customers, not for the entire territory. Reporting Services supports this functionality through the use of parameterized queries.
In the sample, there is a parameterized query. If you go to the Sales dataset and then look at the TerritoryID column, you'll see the @Territory parameter (as shown in Figure 5). Territories are contained in different Areas in our dataset, so there are actually two different parameters - one for the Area and then based on the selected Area, the Territories in that Area. If you have worked with stored procedures in SQL Server, this should be familiar. Reporting Services natively supports parameterized queries.
Figure 5. Note that in the
TerritoryID column, the @Territory parameter is selected. There are actually
two different parameters, one for the Area and then based on the selected Area,
the Territories in that Area.
If we were to preview our report now, Reporting Services would ask us to type in the name of a territory. This is not user friendly, so instead of having to type the name, the sample adds another dataset which returns the names of the territories from the selected Area, so the user can pick from this list rather than having to remember all the territory names. To do this, all we do is create a new dataset that selects the name of the territories from the correct table. We assign the dataset a name and then we need to add it to the report as an overall report parameter. To do this, we need to select the entire report by clicking in the upper left-hand corner of the report and then selecting the Report Parameters menu option from the Report menu.
In the Report Parameters dialog box we can set how possible values for the parameter are filled in, such as from a query or hard-coded values, any default values for the parameter, and to what field on the report the parameter gets mapped.
Add Charts
The final piece of report designing that you will work with is adding charts. Many reports have charts on them since this is usually easier for the user to read than the detailed drilldown information. Reporting Services has native charting built in; it is server-side charting, which means that the chart is generated on the server and sent down to the client. Adding a chart is just a drag and drop operation, followed by setting the properties on the Chart control to meet your needs. Figure 6 shows the properties for the Chart control.
Figure 6. The Chart control supports
multiple chart types, 3D effects, and filter expressions.
Subscriptions and Notifications
After you have created and deployed your reports in the designer, you'll want to manage your reports via the Report Manager interface. This Web interface is the way you see what reports are available on your report server and configure how these reports are generated, delivered, and secured in your environment.
The most common operation you'll perform in the Report Manager is to configure subscriptions so that reports are refreshed either when the data changes or on a scheduled basis. Furthermore, you may want to schedule when a report is delivered to particular users, such as sales reports to sales managers weekly on Mondays at 9 am. The Report Manager provides an interface to perform all these functions.
To create a subscription, you must use the Subscriptions tab in the Report Manager. Then, you can set the properties in the user interface as shown in Figure 7. You can create standard subscriptions, which are usually initiated by the user based on a timed schedule, or you can create a data-driven subscription. Data-driven subscriptions allow you to deliver the customized reports or reports to groups of users who may change over time.
Figure 7. No matter what type of
subscription you choose, you can use report parameters such as the report name
and the time the report was executed. You can also set the default render
format and the time to run the subscription.
Pick a Format ... Any Format
The final piece of Reporting Services I will discuss and demonstrate is the ability to render your reports to many different formats without writing code. There may be times when your users want to view their reports using HTML, Excel, or even as PDF files. With homegrown reporting solutions, providing this ability is difficult, if not close to impossible. With Reporting Services, this ability is baked right into the product. You, or your user, can select which format the report should be rendered in and Reporting Services figures out how to render the report in that format. Figure 8 shows rendering a report out, both in HTML and in Excel.
Figure 8A. You can easily switch between rendering formats, such as HTML or
Excel.
Figure 8B. You can easily switch between rendering formats, such as HTML or
Excel. Notice the collapsible sections in the Excel spreadsheet, which mimics
the collapsible sections in the report.
Some other topics you should look at on your own when it comes to Reporting Services are the programmability and extensibility capabilities. These include RDL (Report Definition Language), which is the XML format for the report definition; managing and driving Reporting Services through its API, which is exposed as a Web service; and finally, coding behind reports using VB .NET or C#.
This article has given you an overview of the new SQL Server 2000 Reporting Services. Reporting Services provides infrastructure and development tools that makes creating rich reports easier. Although you could build your reports using custom ASP.NET code, I hope you will find that Reporting Services gives you the flexibility, power, and scalability that will make you use Reporting Services rather than roll your own solution. Plus, if you already have a SQL Server 2000 license, you can use Reporting Services on the same machine. In effect, you are already licensed to use the software to solve your reporting needs. Give Reporting Services a try. I think you will like what you see.
The sample project used in this article is available for download.
Tom Rizzo is a Director in the SQL Server Product Group. Before working in SQL, Tom worked in a number of other Microsoft Server businesses such as Exchange Server and Microsoft's E-Business Servers. Tom is also the author of a series of MSPress books that cover developing solutions using Microsoft's collaboration technologies. You can reach Tom at mailto:[email protected].
