this print for content only—size & color not accurate spine = 0.8052" 424 page count
Books for professionals By professionals
®
Pro SQL Server 2008 Reporting Services
Dear Reader,
SQL Services 2008 Reporting Services (SSRS) is the latest version of a technology
that has grown in both popularity and features in the years since its introduction
in SQL Server 2000. As DBAs, report designers, and .NET developers, we each
have pushed all versions to the very limit. Because we have worked so closely
with SSRS over the years and continue to do so in our day-to-day businesses,
we were anxious to learn about the updated features promised in SQL Server
2008 Reporting Services. Now we want to pass on what we have learned to you,
our readers.
Reporting Services in SQL Server 2008 brings a plethora of new and exciting
features to the table. One of the most anticipated new features is the Microsoft
Word rendering capability. We certainly look forward to harnessing the power
of Word to format the reports we give our clients. Those who work in enterprise
environments will appreciate the performance enhancements brought about
by severing the former tie between Reporting Services and Internet Information
Server. Still others will be very pleased at the new ability to use rich text in sup-
port of mail merge form letters. And there are the new Tablix properties, which
combine the best of the Matrix and Table report objects.
New features abound in SQL Server 2008 Reporting Services, and we hope
you’ll be as eager to learn about them as we were. You will find all the new features
just mentioned, and more besides, described in this, the third edition of a book
we have been especially fond of writing. It is our hope—as with the first two
editions—that you are able to utilize the real-world approach of this book to
produce and deliver professional quality reports.
Cheers,
Rodney Landrum, Shawn McGehee, and Walter J. Voytek III
US $39.99

for Developers
Pro T-SQL 2008
Programmer’s Guide
Beginning
SQL Queries
Pro SQL Server 2008
Reporting Services
Beginning
Database Design
www.apress.com
SOURCE CODE ONLINE
Companion eBook

See last page for details
on $10 eBook version
ISBN-13: 978-1-59059-992-1
ISBN-10: 1-59059-992-6
9 781590 599921
5 3 9 9 9
A step-by-step guide to creating effective business
reports using Microsoft’s powerful new reporting
technology
Rodney Landrum
Shawn McGehee
Walter J. Voytek III
Rodney Landrum and
Walter J. Voytek III,
coauthors of
Pro SQL Server 2000
Reporting Services

Compositor: Susan Glinert Stevens
Proofreader: Liz Welch
Indexer: Broccoli Information Management
Artist: Dina Quan
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://
www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every precaution
has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to
any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly
by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com.
Landrum_992-6 FRONT.fm Page ii Friday, August 1, 2008 3:53 PM
This book is dedicated to the memory of John Baumann
Landrum_992-6 FRONT.fm Page iii Friday, August 1, 2008 3:53 PM
Landrum_992-6 FRONT.fm Page iv Friday, August 1, 2008 3:53 PM
v
Contents at a Glance
About the Authors
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewer
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
■
CHAPTER 10 Delivering Business Intelligence with SSRS
. . . . . . . . . . . . . . . . . . 307
■
CHAPTER 11 Creating Reports Using Report Builder 1.0 and 2.0
. . . . . . . . . . . . 337
■
INDEX
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Landrum_992-6 FRONT.fm Page v Friday, August 1, 2008 3:53 PM
Landrum_992-6 FRONT.fm Page vi Friday, August 1, 2008 3:53 PM
vii
Contents
About the Authors
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewer
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Acknowledgments
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
■
CHAPTER 1
Introducing the Reporting Services Architecture
. . . . . . . . . 1
Understanding the Benefits of SSRS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
SQL Server 2008 Reporting Services Enhancements
. . . . . . . . . . . . . 5

Creating an Advanced Query
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Using a Parameterized Stored Procedure
. . . . . . . . . . . . . . . . . . . . . . . . . 29
Using Case and ISNULL to Evaluate the Parameters
. . . . . . . . . . . . 32
Testing the Procedure
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Landrum_992-6 FRONT.fm Page vii Friday, August 1, 2008 3:53 PM
viii
■
CONTENTS
■
CHAPTER 3
Introduction to Reporting Services Design
. . . . . . . . . . . . . . . 35
Exploring the Elements of BIDS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Setting Up a Basic IDE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Understanding Report Definition Language (RDL)
. . . . . . . . . . . . . . . 39
Adding a Report
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Setting Up Data Sources and Datasets
. . . . . . . . . . . . . . . . . . . . . . . 41
Setting Parameters
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Applying a Filter
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Adding a Chart
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Adding Tablix Elements
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Report and Group Variables
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Adding the New Gauge Control
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Adding the Final Touches
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
■
CHAPTER 5
Using Custom .NET Code with Reports
. . . . . . . . . . . . . . . . . . 143
Using Embedded Code in Your Report
. . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Using the ExceedMaxVisits Function
. . . . . . . . . . . . . . . . . . . . . . . . 145
Using the ExceedMaxVisits Function in a Report
. . . . . . . . . . . . . . 148
Accessing .NET Assemblies from Embedded Code
. . . . . . . . . . . . 151
Landrum_992-6 FRONT.fm Page viii Friday, August 1, 2008 3:53 PM
■
CONTENTS
ix

Credential Parameters
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Example URLs
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Integrating SSRS 2008 with .NET Applications
. . . . . . . . . . . . . . . . . . . . 173
Building the Report Viewer Using a WebBrowser Control
. . . . . . . 174
Building the Report Viewer Using a Report Viewer Control
. . . . . . 177
Building the Report Viewer in ASP.NET
. . . . . . . . . . . . . . . . . . . . . . 195
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
■
CHAPTER 7
Deploying Reports
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Using Report Manager
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Using Report Builder 2.0
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Using BIDS and Visual Studio 2008
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Configuring Report Deployment Options
. . . . . . . . . . . . . . . . . . . . . 208
Using the rs.exe Utility
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Using the Report Server Web Service
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

Managing Subscriptions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Performing Execution Auditing and Performance Analysis
. . . . . . . . . . 251
Configuring SSRS Logging
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Monitoring Performance
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Controlling SSRS Programmatically
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Controlling SSRS with SOAP
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Controlling SSRS with WMI
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
■
CHAPTER 9
Securing Reports
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Encrypting Data
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Introducing Encryption
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Securing Network Traffic Using SSL
. . . . . . . . . . . . . . . . . . . . . . . . . 275
Setting Up Authentication and User Access to Data
. . . . . . . . . . . . . . . . 285
Introducing SSRS Roles
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286

Building SSRS Reports for SQL Analysis Services
. . . . . . . . . . . . . . . . . 308
Using Analysis Service Cube with SSRS
. . . . . . . . . . . . . . . . . . . . . 312
Setting Up the Analysis Services Data Source
. . . . . . . . . . . . . . . . 313
Working with the Graphical MDX Query Builder
. . . . . . . . . . . . . . . 314
Incorporating SSRS with Microsoft Office SharePoint
Services 2007
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Installing MOSS and SharePoint on a Stand-Alone Server
. . . . . . . 319
Deploying Reports in a MOSS-Integrated SSRS Installation
. . . . . 327
Creating a Simple Dashboard to Display SSRS Reports
. . . . . . . . . 333
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
■
CHAPTER 11
Creating Reports Using Report Builder 1.0 and 2.0
. . . . . 337
Getting User Feedback
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Introducing the Report Model
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Adding a Report Model to BIDS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Adding a Data Source

meetings or other regional and national technology conferences. He
has been working with SQL Server technologies since 1995.
■
SHAWN MCGEHEE is a former developer currently working as a DBA for
a national health and life insurance company in Pensacola, Florida that
specializes in managed care products. He is an active member in the
Pensacola SQL Server Users Group where he speaks regularly at meetings.
Some of his other writing can be seen on popular SQL sites such as
SQLServerCentral.com and Simple Talk.
■
WALTER J. VOYTEK III (Jim) is the CEO and president of HealthWare
Corporation, a Microsoft Gold Certified Partner, which specializes
in information technology solutions for the post-acute health-care
industry, including home health and hospice. He has worked in infor-
mation technology for more than 30 years and in health-care IT for
over 20 years. He speaks publicly on both technology and health-care
and also represents HealthWare in a variety of settings each year. As
the founder and CEO of HealthWare, Jim has been instrumental in the
design and development of HealthWare’s industry-leading applications
for the health-care industry.
Landrum_992-6 FRONT.fm Page xiii Friday, August 1, 2008 3:53 PM
Landrum_992-6 FRONT.fm Page xiv Friday, August 1, 2008 3:53 PM