Introducing Microsoft Reporting Services, Part 2

[Editor’s Note: This article picks up where the article Introducing Microsoft Reporting Services left off.]

1.4 UNDERSTANDING REPORT PROCESSING

Report processing encompasses all activities performed by the Report Server to generate a report. To understand how the Report Server processes a report, let’s see what happens when the report is requested on demand.

Figure 1.6 depicts what happens when a report hosted under the Report Server is requested by a report consumer. First, the consumer submits (1) a report request to the Report Server.

Once the report request is intercepted by the Report Server, it is forwarded (2) to the Report Processor. The Report Processor parses the request and retrieves (3) the report definition and metadata from the Report Server Database. The Report Processor checks whether the user is authorized to access this report. If so, the Report Processor processes the report, which involves two stages: execution and rendering.

Let’s get more insight into each of these stages, starting with the execution stage.

Figure 1.6 You can integrate your applications with RS by using the two web communication façades: HTTP Handler and the RS Web service.

1.4.1 Execution stage

The report execution phase starts when the Report Server begins processing the report and finishes when the report is ready for rendering. For the sake of simplicity, let’s assume that the report is requested for the first time.

Generating the raw report

As we explained earlier, when the report is published, the Report Server parses its report definition (RDL), generates a .NET assembly, and saves the assembly in the catalog for the report. During the execution phase, the Report Server loads and executes the assembly. Referring back to figure 1.6, you can see that the Report Server uses a data extension (4) to query (5) the data source to retrieve the report data, combines the resulting dataset and report layout information, and produces (6) the report in a raw form, called intermediate format (IF).

Having the report generated in an intermediate format before it is finally rendered is beneficial in terms of performance. It allows the Report Server to reuse the same IF regardless of the requested export format. Developers who are familiar with the intermediate language (IL) code execution model in .NET can think of IF in a similar way. IL abstracts the platform on which the code executes, while IF abstracts the rendering format. For example, one report consumer can request the report in an HTML format, while another can request the same report as PDF. In either case, the Report Server already has the raw report; the only thing left is to transform it into its final presentation format. During the rendering stage, the Report Server loads the report IF and renders (7) the report in the requested format using a rendering extension.

Once the report IF is generated, it is saved (cached) in the Report Server Temporary Database. Note that if the report is cached, the report execution phase may be bypassed completely for subsequent requests because the Report Server decides to use the cached IF. We will postpone discussing report caching until chapter 7.

1.4.2 Rendering stage

As shown in figure 1.6, the report-rendering stage represents the second (and last) stage in the report-processing pipeline. After the Report Server has the report IF, it renders the report in its final presentation format as per the export format requested by the user. You will be pleasantly surprised to see the plethora of natively supported formats that a report can be exported to. My favorites are HTML and PDF. For example, as figure 1.7 shows, I have loaded a report exported to a PDF file in Adobe Acrobat.

The Report Server delegates the report-rendering process to rendering extensions. RS comes with various rendering extensions that correspond to supported export formats. If the report consumer does not specify the export format explicitly, the report will be rendered in HTML 3.2 or 4.0, depending on the browser capabilities. Table 1.5 lists each out-of-the box RS supported rendering formats.

As we’ve said before, when the supported formats are not enough, you can write your own rendering extensions.

Figure 1.7 With RS you can export your reports to many formats, including Adobe Acrobat PDF. Here, I have exported this report to Adobe PDF and loaded it in Adobe Reader.

Table 1.5 Report rendering options

Once the report is generated it is ready to travel to its final destination: the report user. RS gives you a lot of flexibility to distribute your reports, as you’ll see in the next section.

1.5 DELIVERING REPORTS

As we mentioned earlier, RS supports both on-demand (pull) and subscribed (push) report delivery. To view a report on demand, the user explicitly requests the report from the Report Server. Alternatively, the user can choose to subscribe to a report. With this option, the report is pushed to the subscribers when the report data is refreshed or on a specified schedule.

Let’s take a closer look at each delivery option.

1.5.1 On-demand delivery

One of the most important decisions you have to make when integrating RS reports in your application is how the application will access the Report Server to request reports. While in some cases the system design may dictate the integration option, occasionally the choice won’t be so straightforward and you may have to carefully evaluate the application requirements to determine the best approach. We revisit the on-demand delivery options in more detail in chapter 9. For now, note that reports can be requested on-demand in two ways: URL access and the Web service.

URL-based report access

The report consumer requests a report by URL by submitting an HTTP-GET request to the Report Server. The advantages of URL access are its simplicity and better performance. In the simplest case, the consumer can embed the report URL into a hyperlink.

For example, a web-based application can have a drop-down Reports menu where each link targets a RS report. With the URL access option, the report arguments are passed as query parameters in the report URL. For example, assuming that you have installed the sample reports included with book source code, the following URL will run the Territory Sales Crosstab sample report with the start date 3/1/2003 and an end date of 4/30/2003.

http://localhost/ReportServer?/AWReporter/Territory Sales   Crosstab&Start-Date=3/1/2003&EndDate=4/30/2004

Web service

With RS, reports can also be requested by submitting SOAP-based requests to the Report Server Web service. The main advantage of this service is that its feature set goes well beyond just report rendering. It also encompasses an extensive set of methods to manage all aspects of the Report Server, such as uploading reports, retrieving a list of resources from the report catalog, and securing RS.

You can think of the Report Server Web service as a façade to the Report Server that allows RS to be integrated with a broad array of platforms. For example, if you are building an enterprise application integration (EAI) solution, a BizTalk schedule might invoke the Web service Render() method, get the XML representation of the report, retrieve some data from it, and pass it on to another application. Or, if your reporting application is B2B oriented and your partner has a Web service, you can send the report results to it in XML.

In some cases, a report consumer will use a combination of both access options to integrate with RS. For example, a report consumer can use the RS Web service to find out what parameters a report takes. Then, the application presentation layer can present the parameters to the user so that the user can enter the parameter values. When the user submits the report request, the application can use URL access to send the request to the Report Server.

1.5.2 Subscribed delivery

In the “push” report delivery scenario, the reports are generated and delivered automatically by the Report Server to a delivery target. Reports can also be delivered at a scheduled time. For example, a financial institution can set up a portfolio balance report to be generated and delivered through e-mail to its customers at the end of each month.

The Report Server Windows service (ReportingServicesService.exe) works in tandem with the SQL Server Agent service to generate and deliver subscribed reports.

Note: SQL Server Agent is a component of Microsoft SQL Server, and it is responsible for running scheduled SQL Server tasks.

For example, if the report is to be generated according to a set schedule, the SQL Server Agent will create a job and move the subscription to the Subscriptions table when the time is up. The RS Windows service periodically polls the Report Configuration Database to find out whether there are any new subscription jobs. If this is the case, the Windows service picks up the job, generates the report, and delivers it to the end users through a delivery extension.

Out of the box, RS comes with two delivery extensions: the e-mail delivery extension and the file share delivery extension. The e-mail delivery extension delivers the report via e-mail. The report can be delivered to either subscribed users (opt-in subscription) or to a data-driven list of recipients. The file share extension delivers reports to a network share. When these two options are not enough, you can write custom delivery extensions.

Note that the Report Server Windows service doesn’t communicate with the Report Server through the HTTP Handler or Web service façades. Instead, because it is installed on the same machine as the Report Server, the Windows service directly loads and calls the Report Server assemblies. This is beneficial for two reasons. The first relates to availability. Even if the IIS server is down, the Windows service will still execute scheduled tasks and deliver reports to subscribers. The other reason is better performance—the web façades are completely bypassed.

Another task that the Report Services Windows service is responsible for is performing background database integrity checks, as well as other administrative tasks.

Before we see RS in action, it may be beneficial to get a good high-level understanding of the report lifecycle. This is important because the remaining chapters of this book follow an identical flow.

1.6 WHAT IS THE REPORT LIFECYCLE?

By now, you probably realize that the Report Server is a sophisticated reporting platform with a feature set that goes well beyond a desktop reporting tool. To minimize the learning curve, this book follows a logical path based on the report lifecycle. The report lifecycle is the process that you typically follow to work with reports, and it involves three stages: authoring, management, and delivery. Figure 1.8 depicts the report lifecycle stages.

In the report-authoring stage, you create the RDL file through the use of report-authoring tools. For example, you can use the Visual Studio .NET Report Designer to lay out the report. Recall that both report data retrieval and layout information are described in the RDL file. We’ll discuss many more details of the report authoring stage in chapters 2–6.

In the report-management stage, you manage the report catalog. As you recall, the report catalog is stored in the Reporting Services Configuration Database. The report catalog keeps the report and all related items. Typical management tasks include organizing reports in folders, uploading reports, and granting users access to run reports. We’ll take a closer look at report management in chapters 7 and 8.

The report-delivery stage is concerned with distributing the reports to their final destinations, including end users, printers, or archive folders. A managed report can be delivered either on-demand or pushed to the subscribed users. Report delivery is discussed in detail in chapters 9–14.

Figure 1.8 Report lifecycle phases include report authoring, management, and delivery. In the report-authoring stage, you lay out the report. In the report-management stage, you deploy and manage the report. Finally, RS gives you many ways to distribute your reports to their final destination.

Enough theory! Let’s put in practice what we learned so far and get our hands on RS.

1.7 RS IN ACTION

This section has two main objectives. First, we introduce an imaginary company, Adventure Works Cycles (AWC), which we reference throughout the rest of this book. We will discuss various hypothetical reporting challenges that AWC faces and implement solutions to address them.

Second, we get our feet wet and create our first report using the Visual Studio .NET Report Wizard and the AdventureWorks2000 sample database. Granted, this is going to be a simple tabular-style report, but as simple as it is, it showcases all the phases of the report lifecycle. We also use this report in the next three chapters as a practical example to expand our knowledge about RS.

1.7.1 About the Adventure Works Reporter

Let’s start with a hypothetical problem statement. You are a developer with AWC, which manufactures and sells goods to individuals and retailers. The company has enjoyed tremendous success the last few years. Sales are going up exponentially and the customer base is growing fast. Today, AWC has customers both in the United States and overseas. It has already implemented a web-based ordering online transaction processing (OLTP) system to capture sales orders online.

However, success does not come cheap. Data inaccuracy and slow decision making are among the top complaints by the sales managers. Often, data is captured and consolidated in the form of Excel spreadsheets. What is needed is a reporting system to present the company with data in a format that’s both easy to understand and analyze and to allow AWC’s management to discover trends and see how the company is performing. You have been designated as a lead developer for the new Adventure Works (AW) Reporter system. Fascinated by Microsoft SQL Server 2000 RS, you decide to base your reporting system on it.

Note: In the real world, you should abstain from reporting off an OLTP database for performance reasons. As the name suggests, OLTP systems must scale to meet large transaction volumes and handle hundreds and even thousands of users. Reporting applications usually submit queries to retrieve and analyze substantial sets of data, which impose data locks on many records in the database. This can severely tax your OLTP system performance. For this reason, reporting and OLTP are usually two mutually exclusive options. A typical solution involves consolidating OLTP data and then uploading it to a data warehouse database that is optimized and designated for reporting purposes only. We discuss OLAP and data warehousing in detail in chapter 12.

1.7.2 Your first report

One crucial piece of information that the AW management would probably like to know is what the yearly products sales per territory are. With such a report in hand, managers can determine how well AW is doing in each sales region. To meet this requirement, let’s create the Sales by Territory report. Figure 1.9 shows the final version of the report that we’ll create in this section.

Figure 1.9 Our first report is Sales by Territory.

This is just one of the many sample reports we’ll design throughout the course of this book. We’ll use the Sales by Territory report in subsequent chapters to demonstrate other RS features.

Table 1.6 shows the list of tasks that we need to accomplish to create the report organized by the report lifecycle phases.

Table 1.6 The task map for creating our first report

As you’ll recall, the first phase of the report lifecycle is authoring the report.

Authoring the report

Let’s develop our first report using the Report Designer. To do so, we need to create a new Visual Studio .NET Business Intelligence (BI) project.

Task: Create a Business Intelligence Project

To create a project, complete the following steps (see figure 1.10):

1. Open Visual Studio .NET and choose File -> New -> Project.
2. From Project Types, select Business Intelligence Projects.
3. From Templates, select Report Project.
4. In the Location field, enter AWReporter, specify a location, and click OK.
5. Once the project is created, right-click on the AWReporter project node in the Solution Explorer window and select Properties. The Property Pages dialog box appears, as shown in figure 1.11.
6. Verify that the TargetFolder setting is set to AWReporter. This specifies the folder name in the report catalog where all reports defined in the project will be deployed.
7. In the TargetServerURL field, enter the Report Server URL. If RS is installed locally on your machine and you have accepted the defaults during setup, the URL of the Report Server should be http://localhost/ReportServer. Click OK to close the Property Pages dialog box.

Figure 1.10 Use Visual Studio .NET to create a new BI project.

Figure 1.11 Use the report property page to set up the project properties.

Task: Create the Report Data Source

Next, we create a shared data source pointing to the AdventureWorks2000 sample database. Don’t worry if the concept of a shared data source is not immediately clear. When we get to chapter 3 it will all begin to make sense.

1. Right-click on the Shared Data Sources node in the Solution Explorer and choose Add New Data Source. The familiar Data Links Properties appears, as shown in figure 1.12.

   Switch to the Provider tab and verify that the Microsoft OLE DB Provider for SQL Server is selected (we will be connecting to a SQL Server database). Back to the Connection tab, specify:

• The name of the SQL Server that you use to install RS. In my case, the database is installed locally, which is why the data source name is “.”
• A valid username and password combination for an SQL Server account that has permissions to query the tables in the AdventureWorks2000 database. Select the Allow Saving Password check box.
• Select the AdventureWorks2000 database from the “Select the database on the server” drop-down list. Test the connection by clicking the Test Connection button. If all is well, click OK.



Figure 1.12 Use the Data Link Properties dialog box to establish to set up a data source pointing to the AdventureWorks2000 database.

2. By default, RS names the data source with the same name as the database. Since we are going to use this data source for most of the sample reports in this book, let’s make the name more descriptive.

   Double-click on the AdventureWorks2000.rds file. The Shared Data Source dialog box appears, as shown in figure 1.13.

   Change the Name property of the data source to AW2000 Shared DS and click OK. Optionally, in the Solution Explorer rename the data source file to AW2000 Shared DS.rds.

Now it’s time to author the report. We’ll use the handy Report Wizard to save some time.

Task: Set the Report Dataset

1. Right-click on the Reports node in the Solution Explorer and choose Add New Report.
2. On the Report Wizard welcome screen, click Next.
3. On the Select the Data Source screen, make sure that the Shared Data Source radio option is selected and that AW2000 Shared DS appears in the Shared Data Source drop-down list. Click Next.
4. In the Design the Query screen, click the Edit button. The familiar query designer window appears.



Figure 1.13 Setting up the shared data source to AdventureWorks2000 database.

5. Enter the following SQL statement in the query pane:

SELECT        ST.Name AS Territory, PC.ProductCategoryID,              PC.Name AS ProductCategory,              SUM(SOD.UnitPrice * SOD.OrderQty) AS SalesFROM          SalesOrderDetail SODINNER JOIN    Product P ON SOD.ProductID = P.ProductIDINNER JOIN    SalesOrderHeader SOH ON              SOD.SalesOrderID = SOH.SalesOrderIDINNER JOIN    SalesTerritory ST ON              SOH.TerritoryID = ST.TerritoryIDINNER JOIN    ProductSubCategory PSC ON              P.ProductSubCategoryID = PSC.ProductSubCategoryIDINNER JOIN    ProductCategory PC ON PSC.ProductCategoryID =              PC.ProductCategoryIDWHERE         DATEPART(YY, SOH.OrderDate) = DATEPART(yy, GETDATE())GROUP BY      ST.Name, PC.Name, PC.ProductCategoryIDORDER BY      ST.Name, PC.Name

This query retrieves the product sales orders grouped by territory and product category. The AW database groups products in subcategories, which are then rolled up to product categories. For the purposes of this report, we summarize the sales data by product categories since this represents the most consolidated level in the product hierarchy, which is exactly what upper management is interested in seeing. The sales amount is retrieved from the SalesOrderDetail table. In addition, the query filters the orders created for the current year. In chapter 3, we’ll make the report parameter driven by allowing the user to pass an arbitrary date. At this point, click Next.

Task: Lay Out the Report

To lay out the report, perform the following steps:

1. On the Select the Report Type screen, leave the report type set to Tabular. Click Next.
2. On the Design the Table screen, select all fields except ProductCategoryID and click Details so the fields appear in the report details section, as shown in figure 1.14. Click Next.
3. On the Choose the Table Style screen, click Corporate, the click Next.
4. Finally, on the Completing the Report Wizard screen, enter Sales by Territory as the name of the report. Click Finish, and we’re done!

Visual Studio displays the Report Designer with the Layout tab selected, as shown in figure 1.15.

The integration with Visual Studio.NET Report Designer allows you to easily preview and test your reports without leaving the Visual Studio .NET IDE.

Figure 1.14 In the Design the Table step, you choose which fields will appearon the report and how data will be grouped.

Figure 1.15 Use the Report Designer Layout tab to lay out your report.

Task: Test the Report

Let’s make some cosmetic changes to enhance our report.

1. Click on the Report Designer Preview tab to see the HTML representation of the report. Notice the report toolbar at the top, which allows you to zoom, print, and save the report in different formats. The Sales field needs some formatting work.
2. Click the Layout tab again to go back to design mode.
3. Right-click on the Sales text box and choose Properties. Specify the format settings, as shown in figure 1.16. Click OK to close the Textbox Properties dialog box.
4. Increase the width of the Territory and Product Category columns; stretch them out as far as there is space within the report width.
5. Right-click again on the Territory text box and go to the field properties.
6. Click the Advanced button and, in the Font tab, change the font weight to bold and style to Italic. Click OK.
7. Back to the Textbox Properties dialog box, hide the repeating territory names by selecting the Hide Duplicates check box, as shown in figure 1.17.

Preview the report again. Now it should look like the report shown in figure 1.9. Still not very pleasing to the eye, but not bad for a few minutes of work!

Figure 1.16 Use the Textbox properties page to set up format settings.

Figure 1.17 Select the Hide Duplicates check box to hide the territory name duplicates.

Report management

Once you are satisfied with the report, you will probably need to deploy it to make it available to all users. This is a report management task that you can accomplish by using the Report Manager. However, if your Windows account has local administrator rights on the computer where the Report Server is installed, you can deploy the report straight from within Visual Studio .NET. Let’s do just that.

Task: Deploy the Report

1. Save your changes.
2. From the Solution Explorer, select the Sales by Territory.rdl node, right-click, and select View Code. Visual Studio .NET shows you the report definition of the report. Note that the report RDL includes the report query and layout information. Since we chose to create a shared data source, the data source information is not included in the report RDL.
3. In the Solution Explorer, right-click on Sales by Territory.rdl and choose Deploy. This compiles the report and uploads the report to the report catalog.

Report delivery

Once the report has been promoted to a managed report, it can be delivered to your end users. Let’s see how users can request the report on-demand by using the Report Manager as a quick-and-easy report-delivery tool.

Task: On-Demand Report Delivery

1. Open the browser and navigate to the Report Manager URL, which by default is http://<reportservername>/reports. Notice that below the Report Manager Home folder there is a new folder, AWReporter, and that its name matches the TargetFolder setting you specified in the report project settings.
2. Click on the AWReporter folder link to see its content. You should find the AW2000 Shared DS data source and Sales by Territory report links.
3. Click on the Sales by Territory report link to request the report with the Report Manager.

As you can see, authoring, managing, and delivering reports with RS is straightforward. At this point, you may decide to compare RS at a high level with other reporting tools you’ve used in the past. The next section discusses how RS stacks up against the competition.

1.8 EVALUATING RS

By the time you read this book, comparison charts will probably be available from Microsoft and other sources to show how RS compares with other popular reporting tools. For example, the Resource section at the end of this chapter lists a link to a detailed feature comparison document between RS and Crystal Reports.

Based on my experience with integrating applications with third-party reporting packages, I think that as a first iteration, RS is surprisingly feature-rich. My favorite top ten features, where I believe RS excels, are as follows:

1. Natively exposed as a Web service—The RS reports are widely accessible, and you don’t have to do anything special to publish your reports as web services because they are hosted under the Report Server, which provides a web service façade.
2. Support of plethora of export formats—You may be delighted to learn that the ability to export reports to PDF and Excel is provided out the box. In addition, reports can be delivered in many other popular formats, including web formats (HTML), popular image formats (such as TIFF and JPEG), and data formats (Excel, XML, CSV).
3. On-demand and subscribed report delivery—Another huge plus is the subscribed report delivery option, which allows developers to implement opt-in report features in their applications.
4. Documented report definition format—Developers can create reports to be published to the Report Server using Microsoft or third-party design tools that support the RS XML RDL.
5. .NET Framework integration—In the extensibility area, you’ll appreciate the fact that you are not locked from a programmability standpoint. As we mentioned earlier, when built-in features are not enough, you can reach out and borrow from the power of the .NET Framework by integrating your reports with .NET code. In addition, the Report Services programming model is 100 percent .NET-based.
6. Extensible architecture—The RS architecture is fully extensible and allows developers to plug in their own security, data, delivery, and rendering extensions.
7. Zero deployment—Thanks to its service-oriented architecture, RS has no client footprint and offers true zero deployment for all application types.
8. Scalability—RS can scale better, since it is designed from the ground up to scale in web farm environments.
9. Visual Studio .NET integrationReport authors will enjoy the familiar IDE environment when designing and testing reports.
10. CostFrom a cost perspective, it is hard to beat the bundled with the SQL Server RS pricing model, especially if you compare it with the five-digit price tag of third-party reporting tools.

Of course, nothing is perfect, and Report Services has its own shortcomings, some of which I would like to mention here. As a .NET developer, I would like to see a future version of RS bring a tighter integration with Visual Studio .NET. Ideally, working with BI projects should not be much different than working with .NET code projects, for example, Windows Forms. In the future, I would expect RS to evolve and add the following features:

• Allow developers to add code-behind files to their reports.
• Instead of Visual Basic .NET only, support all .NET-compatible languages for writing expressions and report-specific code.
• Use the Visual Studio .NET code editor instead of the Notepad-like Custom Code Editor.
• Support events; currently, developers cannot write event handlers to respond to runtime conditions. Microsoft Access, for example, has been enjoying a report object model with events since its first release. Since the RS generation process is not event-driven, the only option for implementing runtime code customization with Report Services is to use expressions.
• Include more flexible object model, for example, creating report elements dynamically, and referencing and changing report items from custom code.
• Convert reports from reporting tools other than Microsoft Access.

I hope that the above shortcomings will be addressed in future releases of RS to make this tool an even more compelling choice for enterprise reporting.

1.9 SUMMARY

This chapter took you on a whirlwind tour of the RS platform. We’ve discussed its role in the Microsoft BI initiative, as well as its features and high-level architecture. You have even had a chance to use RS and create a simple report based on the AdventureWorks2000 sample database. Now that you have a good high-level understanding of its features, you can begin using RS to report-enable your own applications.

By now, you should understand the major components of RS and their role in the report lifecycle. In addition, you should see the advantages that the service-oriented and web-enabled RS architecture has to offer.

Perhaps most important, you should be familiar with the three stages of the report lifecycle: report authoring, management, and delivery. The remaining chapters explore each of these stages in this order. In the next chapter, we discuss different ways to create RS reports.

1.10 RESOURCES

Microsoft RS website (www.microsoft.com/sql/reporting/)—First stop for the latest on RS.

Microsoft Business Intelligence Platform website (www.microsoft.com/sql/evaluation/BI/default.asp)—The Microsoft BI portal home page.

A feature comparison between RS vs. Crystal Reports (http://certia.ramblainf.com/pdf/RSvsBO_En_v1.pdf)—Ceria’s Business Intelligence Team has developed a detailed feature comparison document that outlines how Microsoft RS stacks against Crystal Enterprise.

A Guide to Developing and Running Connected Systems with Indigo (http://msdn.microsoft.com/msdnmag/issues/04/01/Indigo/)—In section 1.3 I emphasized the role of the RS service-oriented programming model. Read Don Box’s article for more information about SOA.

About the Author

Teo Lachev has more than 11 years of experience designing and developing Microsoft-centered solutions. He is currently working as a technology consultant with the Enterprise Application Services practice of Hewlett-Packard. Teo is a Microsoft Certified Solution Developer and Microsoft Certified Trainer. He lives in Atlanta, GA.

About the Book

Microsoft Reporting Services in Action
By Teo Lachev

Published July 2004, Softbound, 656 pages
Published by Manning Publications Co.
ISBN ISBN 1932394222
Retail price: $49.95
Ebook price: $25.00. To purchase the ebook go to http://www.manning.com/lachev.
This material is from Chapter 1 of the book.