SQL Server 2005 Reporting Services in Action
Revised Edition of Microsoft Reporting Services in Action
Bret Updegraff
  • November 2006
  • ISBN 9781932394764
  • 600 pages
  • printed in black & white

Helps you unlock Reporting Services.

FROM THE FOREWORD by Brian Welcker, Microsoft SQL Server Reporting Services

Manning author Bret Updegraff interviewed on .Net Rocks!
Listen as Bret Updegraff, author of SQL Server 2005 Reporting Services in Action shares his expertise in SQL Reporting Services in an interview with .Net Rocks!, the internet audio show for .Net developers.

SQL Server 2005 Reporting Services in Action helps you build and manage flexible reporting solutions and develop report-enabled applications. In this clear, well-illustrated book, you'll follow a report from creation to publication. Along the way you'll explore flexible delivery options like web-based, on-demand, and subscribed reports — complete with cool new features like direct printing and client-side sorting.

For applications that require custom reporting, you'll learn to define reports with RDL and push them to the Report Server using the Report Manager Web Service API. You'll also see how to write server extensions to expand the range of data processing and report delivery options.

Table of Contents show full


foreword to the first edition


preface to the first edition


about this book

about the source code

about the authors

about the cover illustration

1. Introducing SQL Server 2005 Reporting Services

1.1. What is Reporting Services?

1.1.1. Solving reporting problems with Reporting Services

1.1.2. Choosing a Reporting Services edition

1.1.3. Reporting Services and the Microsoft SQL Server platform

1.2. Reporting Services at a glance

1.2.1. Authoring features

1.2.2. Management features

1.2.3. Delivery features

1.2.4. Extensibility features

1.2.5. Scalability features

1.2.6. Security features

1.2.7. Deployment features

1.3. RS architecture

1.3.1. The Report Server engine

1.3.2. The Report Server database

1.3.3. The Report Manager

1.4. Understanding report processing

1.4.1. Execution stage

1.4.2. Rendering stage

1.5. Delivering reports

1.5.1. On-demand delivery

1.5.2. Subscribed delivery

1.6. What is the report lifecycle?

1.7. RS in action

1.7.1. About the Adventure Works Reporter

1.7.2. Your first report

1.8. Evaluating RS

1.9. Summary

Part 1 Authoring reports

2. Report authoring basics

2.1. The report-authoring process: step by step

2.1.1. Analysis

2.1.2. Construction

2.1.3. Testing

2.1.4. Deployment

2.2. Authoring reports in VS .NET

2.2.1. Authoring reports with the Report Wizard

2.2.2. Authoring reports with the Report Designer

2.2.3. Importing reports from Microsoft Access

2.3. Creating reports programmatically

2.3.1. Generating RDL: The AW Ad Hoc Reporter

2.3.2. Implementation details

2.4. Summary

3. Working with data

3.1. Working with data sources

3.1.1. Connecting to the database

3.1.2. Choosing an authentication mechanism

3.1.3. Deploying data sources

3.2. Working with report datasets

3.2.1. Understanding the dataset definition

3.2.2. Creating a report dataset

3.2.3. Using multiple datasets

3.3. Authoring dataset queries

3.3.1. Using the Graphical Query Designer

3.3.2. Using the Generic Query Designer

3.4. Parameter-driven reports

3.4.1. The role of parameters

3.4.2. Building parameter-driven queries

3.4.3. Setting up the report-level parameters

3.5. Working with stored procedures

3.5.1. Using a stored procedure as a dataset query

3.5.2. Defining query-based lookup parameter values

3.5.3. Creating cascading parameters

3.5.4. Working with multivalued parameters

3.6. Summary

4. Designing reports

4.1. Anatomy of a report

4.1.1. Getting started with a new report

4.1.2. Understanding report sections

4.1.3. Understanding report items

4.1.4. Understanding data regions

4.2. Designing tabular reports

4.2.1. Tabular reports with groups

4.2.2. Parameterized tabular reports

4.2.3. Tabular reports with interactive features

4.2.4. Table region limitations

4.3. Designing freeform reports

4.3.1. Freeform reports with nested regions

4.3.2. Grouping freeform data

4.3.3. Freeform reports with side-by-side data regions

4.4. Designing chart reports

4.4.1. The chart data region

4.4.2. Working with charts

4.4.3. Nesting chart regions

4.5. Designing crosstab (matrix) reports

4.5.1. Matrix region advantages

4.5.2. Working with the matrix region

4.5.3. Adjusting the report layout

4.6. Using other design features in your reports

4.6.1. Setting up multiple columns

4.6.2. Adding interactive sort

4.6.3. Passing multivalued parameters into a stored procedure

4.6.4. Designing subreports

4.7. Summary

5. Using expressions and functions

5.1. Understanding expressions

5.1.1. Using the Expression Editor

5.1.2. Expression syntax

5.1.3. Determining expression execution order

5.1.4. Understanding expression scope

5.1.5. Dealing with expression errors

5.2. Exploring the Report Object Model

5.2.1. Using the ReportItems collection

5.2.2. Using the Fields collection

5.2.3. Using the Parameters collection

5.2.4. Using the Globals collection

5.2.5. Using the User collection

5.3. Working with functions

5.3.1. Referencing external functions

5.3.2. Using aggregate functions

5.3.3. Using other internal functions

5.4. Designing reports with navigational features

5.4.2. Reports with document maps

5.5. Report rendering considerations

5.5.1. Exporting reports to HTML

5.5.2. Exporting reports to MHTML

5.5.3. Exporting reports to other formats

5.6. Summary

6. Using custom code

6.1. Understanding custom code

6.1.1. Using embedded code

6.1.2. Using external assemblies

6.2. Custom code in action: implementing report forecasting

6.2.1. Forecasting with OpenForecast

6.2.2. Implementing report forecasting features

6.3. Using XML-based reports

6.3.1. Understanding XML exporting

6.3.2. Exposing the report content as an RSS feed

6.4. Summary

7. Ad hoc reporting with the Report Builder application

7.1. About the Report Builder application

7.1.1. A quick tour of the Report Builder

7.1.2. Setting up for ad hoc reporting

7.1.3. The Developer toolset for ad hoc reporting

7.2. Building the Report Model using BIDS

7.2.1. Building a report model project

7.2.2. Setting up the data source

7.2.3. Creating a data source view

7.2.4. Building the report model

7.2.5. Deploying report models

7.3. Ad hoc reporting in action

7.3.1. Launching the Report Builder

7.3.2. Creating the Product Profit Margin report

7.3.3. Getting more with infinite drill-through reporting

7.3.4. Advanced filtering with the Report Builder

7.4. Implementing Report Builder security

7.4.1. Securing the Report Builder with roles

7.4.2. Securing report model items

7.5. Summary

Part 2 Managing reports

8. Managing the Reporting Services environment

8.1. Managing RS with the Report Manager

8.1.1. How the Report Manager works

8.1.2. Managing Report Server settings

8.1.3. Managing content

8.1.4. Managing report execution

8.1.5. Managing linked reports

8.2. Managing RS with the Web service

8.2.1. Using the Web service management API

8.2.2. Tracing calls to the SOAP API

8.2.3. Deploying reports programmatically

8.2.4. Batching methods together

8.3. Managing RS with the WMI provider

8.3.1. Understanding the WMI provider

8.3.2. Implementing an RS management console

8.4. Other ways to manage Reporting Services

8.4.1. Managing RS with SQL Management Studio

8.4.2. Managing RS with the script host

8.4.3. Using the Reporting Services configuration tool

8.5. Analyzing report execution

8.5.1. Analyzing the Report Server execution log

8.5.2. Analyzing trace log files

8.6. Summary

9. Securing Reporting Services

9.1. Role-based security basics

9.1.1. The purpose of role-based security

9.1.2. Authentication models: using Windows or creating your own

9.2. Windows authentication: a closer look

9.2.1. Exploring the Client-to-Report Server model

9.2.2. Exploring the Client-to-Façade-to-Report Server model

9.3. Using role-based authorization

9.3.1. Understanding tasks

9.3.2. Defining roles

9.3.3. Understanding securable items

9.3.4. Defining policies

9.4. Managing role-based security with the Report Manager

9.4.1. Creating Windows user accounts and groups

9.4.2. Creating custom roles

9.4.3. Defining security policies

9.5. Managing role-based security with the Web service

9.5.1. Determining role-based security policies

9.5.3. Implementing "pseudo" report events

9.6. Techniques for advanced security issues

9.6.1. Filtering data

9.6.2. Using dynamic dataset queries

9.6.3. Hiding data

9.6.4. Implementing custom security models

9.6.5. Enforcing a secured connection to the Report Server

9.7. Summary

Part 3 Delivering reports

10. On-demand report delivery

10.1. How RS provides on-demand report delivery

10.2. URL-based report access

10.2.1. Understanding URL syntax

10.2.2. Requesting resources by URL

10.2.3. Requesting reports by URL

10.2.4. Working with report commands

10.2.5. Working with the HTML Viewer

10.3. URL access in action

10.3.1. URL Access with WinForms: AccessOptions

10.3.2. URL Access with WebForms: AWReporterWeb

10.4. Web service-based report access

10.4.1. Requesting reports with SOAP

10.4.2. Rendering images

10.4.3. Handling report sessions

10.5. Web service access (SOAP) in action

10.5.1. Generating report requests with SOAP on the client side

10.5.2. An automation solution: AW Campaigner

10.6. Evaluating URL and Web service access options

10.6.1. Evaluating URL access

10.6.2. Evaluating Web service access

10.6.3. Choosing an integration approach

10.7. Summary

11. Mastering the ReportViewer controls

11.1. How the .NET ReportViewer controls work

11.1.1. Controls for web and Windows applications

11.1.2. Choosing remote or local mode

11.1.3. Managing properties of the ReportViewer controls

11.2. Using ReportViewer in remote mode

11.2.1. Creating, configuring, and running the control

11.2.2. Additional customizations for the ReportViewer control

11.3. Using ReportViewer in local mode

11.3.1. Creating a local report with a database as the data source

11.3.2. Creating a local report with an object as the data source

11.4. Custom validation with the ReportViewer control

11.4.1. Creating a parameters section

11.4.2. Creating event methods

11.4.3. Write validation code

11.5. Converting report files

11.5.1. Converting RDL files into RDLC files

11.5.2. Converting RDLC files into RDL files

11.6. Deploying applications that use ReportViewer controls

11.6.1. Redistributing the ReportViewer controls

11.6.2. ReportViewer deployment for Windows applications

11.6.3. ReportViewer deployment for web applications

11.6.4. Using the ReportViewer web server control in a web farm

11.7. Summary

12. Subscribed report delivery

12.1. Understanding subscribed report delivery

12.1.1. Subscription-based reporting scenarios

12.1.2. The subscriber-publisher design pattern

12.1.3. How the RS subscription-based model works

12.2. Configuring subscribed report delivery

12.2.1. Creating a new subscription

12.2.2. Choosing the subscription type

12.2.3. Configuring delivery extensions

12.2.4. Managing subscriptions

12.3. Subscribed report delivery in action

12.3.1. "Pushing" reports via standard e-mail subscriptions

12.3.2. Archiving reports to a file share

12.3.3. Sending reports to a data-driven list of recipients

12.3.4. Triggering subscriptions programmatically

12.4. Summary

Part 4 Advanced reporting

13. Extending Reporting Services

13.1. Understanding Reporting Services extensibility

13.1.1. Interface-based programming

13.1.2. Working with interface inheritance

13.1.3. Extending RS with interface inheritance

13.2. Reporting with a custom dataset data extension

13.2.1. Identifying design goals and trade-offs

13.2.2. Authoring dataset-bound reports

13.2.3. Implementing the custom dataset extension

13.2.4. Debugging dataset extensions

13.3. Distributing reports to Web services using custom delivery extensions

13.3.1. Design goals and trade-offs

13.3.2. Using the custom delivery extension

13.3.3. Implementing the custom delivery extension

13.3.4. Debugging custom delivery extensions

13.4. Implementing custom security

13.4.1. Design goals and trade-offs

13.4.2. Intranet reporting with custom security

13.4.3. Implementing the custom security extension

13.4.4. Debugging the custom security extension

13.5. Summary

14. Performance and scalability

14.1. Understanding capacity planning

14.1.1. Capacity-planning fundamentals

14.1.2. The capacity-planning process

14.2. Capacity planning for Reporting Services in action

14.2.1. Determining requirements

14.2.2. Setting up the testing environment

14.2.3. Performance testing

14.2.4. Analyzing performance results

14.2.5. Identifying resource constraints

14.2.6. Eliminating resource constraints

14.3. Summary


Appendix A: Installing SQL Server Reporting Services

A.1. Software requirements

A.2. Installing SQL Server Reporting Services

Appendix B: Understanding .NET code access security

B.1. Code access security basics

B.2. Understanding code access terminology

B.3. Exploring the RS default security policy

B.4. Managing RS code access security



About the Technology

Reports are the lifeline of business, so a good reporting environment is a big deal. With a powerful tool like Microsoft Reporting Services, .NET developers can add reporting to any type of application, regardless of its target platform or development language. Greatly improved for SQL Server 2005, Reporting Services now provides tighter integration with SQL Server, improved developer tools, and an expanded array of options to empower end users.

About the book

This book is a revised edition of Microsoft Reporting Services in Action by Teo Lachev, Manning, 2004.

What's inside

  • How to build reporting solutions, instead of just walking through product features.
  • What you need to know before deciding whether to buy a third-party tool or invent a homegrown solution.
  • Code samples showcase many aspects of programming with Reporting Services and teach the reader best development practices.
  • Report Builder and ReportViewer.
  • Ad-hoc reporting and client-side sorting.
  • ASP.NET web control for server-side report generation.
  • Create reports off ADO.NET datasets.

About the reader

Written for developers with a solid foundation in .NET and SQL Server.

About the author

Bret Updegraff holds the MCSD, MCAD, and MCDBA certifications, and is a 2006 Microsoft Server System MVP. Bret is the president of the Indianapolis Professional Association of SQL Server (PASS) users group.

eBook $39.99 PDF only

FREE domestic shipping on three or more pBooks

Insightful, professional, and full of information.

Dan Hounshell, Telligent Systems

Incredible technical depth, interesting examples and solutions. Very well done.

Dave Corun, Director of Microsoft Technologies, Catalyst IT Services

Great case studies!

Nuo Yan, Microsoft MVP, MCDBA

Excellent writing, very detailed.

Berndt Hamboeck, Team Leader, United Nations