Introducing Microsoft Reporting Services, Part 2
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.
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
|Authoring||Create BI project.||Create a new BI project in Visual Studio .NET.|
|Create the report data source.||Use the Report Designer Data tab to configure a database connection to the AdventureWorks2000 database.|
|Set the report dataset.||Define a dataset query to retrieve the report data.|
|Lay out the report.||Use the Report Wizard and Report Designer to author the report.|
|Test the report.||Use the Report Designer Preview tab to preview and test the report.|
|Management||Deploy the report.||Use Visual Studio .NET to deploy the report to the Report Server catalog.|
|Delivery||Ensure on-demand report delivery.||Use the Report Manager to navigate and render the report.|
As you'll recall, the first phase of the report lifecycle is authoring the report.
Page 2 of 4