Develop Reports With SQL Server 2000 Reporting Services

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 reportingsolutions.

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 creatingorders from a Web site, or you are writing applications that report on yourdata, such as custom Web applications that show sales trends or inventoryinformation. One of the key issues we hear from Web developers is thatbuilding, maintaining, and integrating data into their reporting solutions iscostly, 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, itcurrently is in beta; hopefully by the time you are reading this articleReporting Services will have been released. Reporting Services was originallypart of the SQL Server "Yukon" release, which is due out in 2004. However,based on the overwhelming positive customer feedback for the product, Microsoftdecided to ship Reporting Services earlier and separate from Yukon.

This article will step you through the Reporting Servicesarchitecture and features. Then we'll look at solving a business problem, whichis how to build rich, dynamic reports in the least amount of code, using thesample AdventureWorks database that ships with Reporting Services. We couldbuild 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 ReportingServices, we first need to look at the architecture of Reporting Services.Figure 1 shows the Reporting Services architecture. Let's step through each ofthese components to understand what functionality they provide to yourreporting solution.

Figure 1. Reporting Services is madeup of six key pieces: the Report Server, the SQL Server catalog, Data RetrievalServices, Security Services, Rendering Services, and Scheduling and DeliveryServices.

The Report Server: The Report Server contains theother services in Reporting Services. The interesting part of the Report Serveris that you can create a Web farm of Reporting Servers. This allows you toscale your report infrastructure to very large amounts of users eitherdynamically running reports or having the system send reports on a scheduledbasis. You'll find that when you install Reporting Services, a new servicecalled ReportServer will be installed on the machine. In addition, a newvirtual directory called Reports will be created. These two components make upthe majority of your Reporting Services infrastructure to manage, execute,render, and schedule your reports. Figure 2 shows the Report Manager interfacefrom which you can manage the reports you create with Reporting Services.

Figure 2. The Report Managerinterface lets you manage the reports you create with Reporting Services.

The SQL ServerCatalog: To maintain yourconfiguration settings, report definitions, datasource settings, usersubscription information, and also snapshots of any reports that you wantstored for auditing or other uses, Reporting Services requires a SQL Serversystem to store its catalog information. You can decide whether you want theSQL Server on the same machine as your Report Server or you can put SQL Serveron a different machine to separate the load. You'll want to make sure that the SQLServer you use is highly available since Reporting Services is dependent on theinformation in the SQL Server for its operation.

Data RetrievalServices: As part of anyreport, you are going to want to be able to retrieve data. Reporting Servicessupports 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 themultitude of datasources contained in your companies and report on theinformation contained in those datasources. The interesting aspects ofReporting Services when it comes to data retrieval is that Reporting Servicessupports multiple datasources in a single report, and also supports shareddatasources.

By having multiple datasources on a single report, you cancombine heterogeneous datasources such as SQL Server, Oracle, DB2, Access, oreven comma-delimited textfiles into your reports. Also, you can combinerelational data with OLAP data in Reporting Services using the multipledatasource capabilities.

With shared datasources you can share a datasource betweenmany reports. By providing this capability, you can change parameters on thedatasource once and have all your reports use the new parameter. Imagine thescenario where you create a hundred reports to a datasource individually oneach report and then the password changes for the user account that you use toaccess the datasource. Now, you would have to go back and change the connectionstring in your code for all your reports. With a shared datasource, you changethe shared datasource once and all reports automatically use the new andupdated information in the shared datasource.

Security Services: To support authorization and secureaccess to your reports and your report data, Reporting Services supports anumber of security services. One authorization provider Reporting Servicessupports is Windows-based authentication. You can secure your reports and thedatasources used in your reports using Windows-based authentication. You canalso use other types of authentication, but the most common scenario is to useWindows-based authentication.

Rendering Services: One of the key requirements for anyreporting system is the ability to render the report in many different formatsdepending on the user's requirements. For example, some reports may be ok asHTML but other reports may need to be exported to Excel for more analysis or toa print-quality image, such as a TIFF image. When building your own reportingsolution, you must build these capabilities yourself. However, with ReportingServices, support for outputting reports to many formats without custom code isbuilt 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), andAdobe Acrobat (PDF).

Scheduling andDelivery Services: The finalpiece of the Reporting Services architecture is the Scheduling and DeliveryServices components. These components of the system allow you to either pushreports to users on a schedule, such as 9 am every morning, or run on-demandreports when users browse to a report through the Reporting Services Web site.Furthermore, these components allow you to specify to which delivery channel areport should be delivered. You may want reports sent via e-mail or you maywant them posted to a file share based on a timed snapshot of the report.Reporting Services supports all these different scenarios for you to schedulewhen a report should run, how it should be customized for individual viewers ofthe report, and, finally, what format and delivery mechanism the report shoulduse to deliver the report to the consumer of the report.

Develop a Report

Now that you understand all the pieces that make up theinfrastructure for Reporting Services, we'll look at developing a report usingthis infrastructure. Reporting Services integrates with and extends VisualStudio .NET 2003. Included with Reporting Services is a Report Designer add-infor 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 anyerrors you have. Figure 3 shows the new project types included with the ReportDesigner. Using the wizard helps kick start creating your reports.

Figure 3. Reporting Servicesintegrates with and extends Visual Studio .NET 2003. You can either use theReport Project Wizard or use the Report Project to start from scratch.

After we select the type of project we want to use, weneed to lay out our report. If you use the wizard, you'll find a new reportgenerated 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 numberof 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 thesecontrols to you, the help included with Reporting Services explains in detailthe capabilities of each control. For the most part, you'll use the Table andChart controls when building your first reports.

Beyond built-in controls, Reporting Services also includesthe ability to use the graphical query tools in Visual Studio to create thequeries that will connect to your datasource and return your data. After you'vecreated your query, as shown in Figure 4, you can use the built-in Fieldswindow to drag and drop your fields onto the report surface. For anyone who hasused Access reports, dragging and dropping using the Fields window should feelfamiliar.

Figure 4. Reporting Services letsyou use the graphical query tools in Visual Studio to create the queries thatconnect to your datasource and return your data.

The sample application included with this article showsyou how you can build sales reports using Reporting Services. The sample usesthe AdventureWorks 2000 sample databases included with Reporting Services. Makesure to install the AdventureWorks database when you set up your ReportingServices 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, thenused the Wizard to lay out the report for me automatically. Once I had thebasic report, I customized the report using the built-in Reporting ServicesDesigner tools. Now that you have a basic report that you can start with I'mgoing to step you through some of the more advanced capabilities that you cando with Reporting Services.

One of the common tasks you will want to perform is towork with the Table control. This control provides the ability to drag and dropfields, create headers and footers, and also to set your grouping for yourreport data. The Table control also allows you to add expressions using theVisual 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 coulddo this on the server in a stored procedure or as part of your query, or youcould do it within the report as an expression. You can use the differentintrinsic report values in your expression such as page number, the parametersyou pass to your report (which we will learn about later), and, finally, thefields themselves.

Using the expression language you can do string functions,conditional formatting, and math functions. In the report, I use the Sumfunction to add all the sales to a particular store. You will also findyourself using the conditional formatting capabilities often to performfunctions like mark columns red if they are below plan or green if they areabove plan. The following expression, when used in the Color property orBackgroundColor property of a textbox, performs this functionality in ReportingServices:

=IIf(Fields!Cost.Value > Fields!Revenue.Value,

  "Red","Green")

If you think you'll use an expression in more than oneplace, you can add it to the Fields window. This will allow you to drag anddrop your new expression-based field to many different locations on a report orto multiple reports in your project.

Add Parameters

In many reports, you want to customize the reportdepending on who is requesting the data. For example, a sales manager may onlywant to see the data for his/her sales territory while sales representativeswill want only their particular sales data for their customers, not for theentire territory. Reporting Services supports this functionality through theuse of parameterized queries.

In the sample, there is a parameterized query. If you goto the Sales dataset and then look at the TerritoryID column, you'll see the@Territory parameter (as shown in Figure 5). Territories are contained indifferent 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 thatArea. If you have worked with stored procedures in SQL Server, this should befamiliar. Reporting Services natively supports parameterized queries.

Figure 5. Note that in theTerritoryID column, the @Territory parameter is selected. There are actuallytwo 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 Serviceswould ask us to type in the name of a territory. This is not user friendly, soinstead of having to type the name, the sample adds another dataset whichreturns the names of the territories from the selected Area, so the user canpick from this list rather than having to remember all the territory names. Todo this, all we do is create a new dataset that selects the name of theterritories from the correct table. We assign the dataset a name and then weneed to add it to the report as an overall report parameter. To do this, weneed to select the entire report by clicking in the upper left-hand corner ofthe report and then selecting the Report Parameters menu option from the Reportmenu.

In the Report Parameters dialog box we can set howpossible values for the parameter are filled in, such as from a query orhard-coded values, any default values for the parameter, and to what field onthe report the parameter gets mapped.

Add Charts

The final piece of report designing that you will workwith is adding charts. Many reports have charts on them since this is usuallyeasier for the user to read than the detailed drilldown information. ReportingServices has native charting built in; it is server-side charting, which meansthat the chart is generated on the server and sent down to the client. Adding achart is just a drag and drop operation, followed by setting the properties onthe Chart control to meet your needs. Figure 6 shows the properties for theChart control.

Figure 6. The Chart control supportsmultiple chart types, 3D effects, and filter expressions.

Subscriptions and Notifications

After you have created and deployed your reports in thedesigner, 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 reportserver and configure how these reports are generated, delivered, and secured inyour environment.

The most common operation you'll perform in the ReportManager is to configure subscriptions so that reports are refreshed either whenthe data changes or on a scheduled basis. Furthermore, you may want to schedulewhen a report is delivered to particular users, such as sales reports to salesmanagers weekly on Mondays at 9 am. The Report Manager provides an interface toperform all these functions.

To create a subscription, you must use the Subscriptionstab in the Report Manager. Then, you can set the properties in the userinterface as shown in Figure 7. You can create standard subscriptions, whichare usually initiated by the user based on a timed schedule, or you can createa data-driven subscription. Data-driven subscriptions allow you to deliver thecustomized reports or reports to groups of users who may change over time.

Figure 7. No matter what type ofsubscription you choose, you can use report parameters such as the report nameand the time the report was executed. You can also set the default renderformat and the time to run the subscription.

Pick a Format ... Any Format

The final piece of Reporting Services I will discuss anddemonstrate is the ability to render your reports to many different formatswithout writing code. There may be times when your users want to view theirreports using HTML, Excel, or even as PDF files. With homegrown reportingsolutions, providing this ability is difficult, if not close to impossible.With Reporting Services, this ability is baked right into the product. You, oryour user, can select which format the report should be rendered in andReporting Services figures out how to render the report in that format. Figure8 shows rendering a report out, both in HTML and in Excel.

Figure 8A. You can easily switch between rendering formats, such as HTML orExcel.

Figure 8B. You can easily switch between rendering formats, such as HTML orExcel. Notice the collapsible sections in the Excel spreadsheet, which mimicsthe collapsible sections in the report.

Some other topics you should look at on your own when itcomes to Reporting Services are the programmability and extensibilitycapabilities. These include RDL (Report Definition Language), which is the XMLformat for the report definition; managing and driving Reporting Servicesthrough its API, which is exposed as a Web service; and finally, coding behindreports using VB .NET or C#.

This article has given you an overview of the new SQLServer 2000 Reporting Services. Reporting Services provides infrastructure anddevelopment tools that makes creating rich reports easier. Although you couldbuild your reports using custom ASP.NET code, I hope you will find thatReporting Services gives you the flexibility, power, and scalability that willmake you use Reporting Services rather than roll your own solution. Plus, ifyou already have a SQL Server 2000 license, you can use Reporting Services onthe same machine. In effect, you are already licensed to use the software tosolve your reporting needs. Give Reporting Services a try. I think you willlike what you see.

The sample project used inthis article is available for download.

Tom Rizzo is a Director in the SQL Server ProductGroup. Before working in SQL, Tom worked in a number of other Microsoft Serverbusinesses such as Exchange Server and Microsoft's E-Business Servers. Tom isalso the author of a series of MSPress books that cover developing solutionsusing Microsoft's collaboration technologies. You can reach Tom at mailto:[email protected].