Professional SQL Server 2005 reporting services

Professional

SQL Server™ 2005

Reporting Services

Paul Turley

Todd Bryant

James Counihan

Dave DuVarney

01_584979 ffirs.qxp 1/27/06 7:43 PM Page i

01_584979 ffirs.qxp 1/27/06 7:43 PM Page iv

Professional

SQL Server™ 2005

Reporting Services

Paul Turley

Todd Bryant

James Counihan

Dave DuVarney

01_584979 ffirs.qxp 1/27/06 7:43 PM Page i

Professional SQL Server™ 2005 Reporting Services

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-7645-8497-8

ISBN-10: 0-7645-8497-9

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/RV/QS/QW/IN

Library of Congress Cataloging-in-Publication Data:

Professional SQL Server 2005 reporting services / Paul Turley ... [et al.].

p. cm.

“Wiley Technology Publishing.”

Includes index.

ISBN-13: 978-0-7645-8497-8 (paper/website)

ISBN-10: 0-7645-8497-9 (paper/website)

1. SQL server. 2. Client/server computing. I. Turley, Paul, 1962-

QA76.9.C55P79 2006

005.75’85—dc22

2005036108

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,

electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108

of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization

through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA

01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal

Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355,

or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESEN￾TATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF

THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WAR￾RANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY

SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE

SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS

NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFES￾SIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE

SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM.

THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A

POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER

ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT

MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY

HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services please contact our Customer Care Department within the

United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trade￾marks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries,

and may not be used without written permission. SQL Server is a trademark of Microsoft Corporation in the United States

and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not

associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in

electronic books.

01_584979 ffirs.qxp 1/27/06 7:43 PM Page ii

Acquisitions Editor

Katie Mohr

Development Editor

Tom Dinse

Technical Editor

Todd Meister

Production Editors

Angela Smith

William A. Barton

Copy Editor

Foxxe Editorial Services

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Graphics and Production Specialists

Lauren Goddard

Denny Hager

Jennifer Heleine

Alicia B. South

Quality Control Technicians

Leeann Harney

Joseph Niesen

Charles Spencer

Robert Springer

Media Development Specialists

Angela Denny

Kit Malone

Travis Silvers

Proofreading and Indexing

TECHBOOKS Production Services

Credits

01_584979 ffirs.qxp 1/27/06 7:43 PM Page iii

01_584979 ffirs.qxp 1/27/06 7:43 PM Page iv

About the Authors

Paul Turley

Paul Turley is a senior consultant for Hitachi Consulting. He architects database, reporting and business

intelligence solutions for many prominent consulting clients. In addition to Reporting Services, he has

created reporting solutions using Crystal Reports, Active Reports, and Access. Since 1988, he has man￾aged IT projects, designed and programmed applications using Visual Basic 3, 4, 5, 6, ASP.NET,

ADO.NET, and SQL Server. He obtained his MCSD certification in 1996 and other certifications include

MCDBA, IT Project+, and Microsoft Solutions Framework (MSF) Practitioner.

He designed and maintains www.Scout-Master.com, a web-based service that enables Boy Scout

units to manage their membership and advancement records online using ASP.NET, SQL Server, and

Reporting Services.

Paul authored Beginning Transact SQL for SQL Server 2000 and 2005. He has been a contributing author

on books and articles including Professional Access 2000 Programming, Beginning Access 2002 VBA, and

SQL Server Data Warehousing with Analysis Services, all from WROX Press.

My deepest appreciation goes to my wife, Sherri, and our children: Josh, Rachael, Sara, and Krista for

their support and understanding. Writing two books over the past year and a half has been a challenge

for all of us. Rachael: You did a great job managing my screen shots. Sara, you are a champion and a

fighter, and I appreciate your strength and example to everyone around you. Thanks to my folks and

extended family who make me proud to be who and where I am.

My appreciation to everyone at Hitachi Consulting for their support and contributions. Hitachi is a

stellar organization with top-notch people who know how to get business done. Your support over the

past year is appreciated.

Paul contributed Chapters 1, 2, 4, 5, 6, 7 and Appendixes C and D to this book.

Paul may be contacted at [email protected].

Todd Bryant

Todd Bryant has been creating custom data-focused applications and reporting solutions since the early

eighties. He began using Microsoft technologies in 1998, and the love affair began. Todd has been con￾tract programming, teaching, and developing custom courseware ever since. He is currently working

half-time as a software architect for SoftWyre, a Little Rock, Arkansas, based software development com￾pany as well as training half-time at Netdesk Corporation in Seattle, where he concentrates on enterprise

solutions, Com+ services, and object-oriented programming using both VB.NET and C#. His certifica￾tions include the MCSD, MCSE, MCDBA, and MCT certifications from Microsoft; the CNA certification

from Novell; and both CompTIA’s A+ and CTT+.

I would like to thank my family and friends for putting up with me during many months of late night

work. I want to thankTodd Meister, our technical Editor. He was a joy to work with and made the pro￾cess much more enjoyable.

Todd contributed Chapter 13 and Appendix B to this book.

01_584979 ffirs.qxp 1/27/06 7:43 PM Page v

James Counihan

James Counihan started teaching himself binary in the early 1970s. He is now a Seattle-area consultant

specializing in development on the .NET platform.

I wouldn’t be where I am today if it weren’t for the love and support of the people who care about me.

Especially to my parents and sister, thank you!

James contributed Chapters 10, 11, 12, and Appendix A to this book.

Dave DuVarney

Dave DuVarney is a principal for Statera’s Seattle office. He has broad technical knowledge stemming

from his experiences as a software developer, a certified public accountant, and a technology trainer.

Dave has been involved in multiple software development projects ranging from contract management

systems to human rights auditing. He is proficient in numerous development languages as well as

Microsoft business intelligence technologies. Most recently he has been consulting and delivering on

SQL Server 2005 Analysis Services, Reporting Services, and Integration Services. Dave is the coauthor

of Professional SQL Server Reporting Services.

I want to thank my wife Stephanie for putting up with another summer of weekends behind the com￾puter. I’d also like to thank the other authors for giving so much of their time to make this second book a

reality.

Dave contributed Chapters 2, 3, 8, and 9 to this book.

01_584979 ffirs.qxp 1/27/06 7:43 PM Page vi

Acknowledgments

Our sincere thanks go to the members of the Reporting Services product team at Microsoft, who have been

very supportive and accessible. A large portion of this book’s content is a direct result of the numerous

meetings and phone calls and hundreds of e-mails exchanged with our friends in the Reporting Services

group at Microsoft. We’ve had the privilege of working with many folks at Microsoft on these two books

over the past three years and greatly appreciate their many contributions. In particular, we’d like to thank

Jason Carlson, Brian Welcker, Chris Hays, Carolyn Chau, Tudor Trufinescu, Lukasz Pawlowski, Fang

Wang, and Rajeev Karunakaran.

A big thank you goes to Andrew Bryan at Dundas Software for his help with the charting features. The

integration in the product is awesome, and your assistance and support have been invaluable.

Our editors at Wiley, Katie Mohr and Tom Dinse, have been terrific to work with, and Todd Meister did

an awesome job on the technical review.

The Business Intelligence team at Hitachi Consulting has been a tremendous source of support and

learning. Thanks for giving us the space to push the envelope. Special thanks to Hilary Feier, Mike

Luckevich, Carr Krueger, Reed Jacobson, Tory Tolton, Ted Corbett, Martin Powdrill, Patrick Husting,

Steve Muise, Stacia Misner, Jeanne Barnham, Scott Cameron, and too many others to mention by name.

Our consulting clients deserve a lot of the credit for affording us the opportunity to put this product in

front of real businesses and corporate decision makers. The rules prevent us from acknowledging all of

those we’d like to here. For the many with whom we’ve had the pleasure of sharing your work spaces,

attending your meetings, and bringing your servers to a grinding halt while developing reporting solu￾tions, you deserve our gratitude in a large measure.

01_584979 ffirs.qxp 1/27/06 7:43 PM Page vii

01_584979 ffirs.qxp 1/27/06 7:43 PM Page viii

Contents

Acknowledgments ix

Foreword I xxi

Foreword II xxiii

Introduction xxv

Part One: Getting Started 1

Chapter 1: What Can You Do with Reporting Services? 3

What We’ve Learned 3

Who Uses Reporting Services? 4

Application and Reporting Technology 5

Information, Now! 6

Solution Types 7

Out-of-the-Box Reports 8

Server-Based Reports 8

User-Designed Reports 9

Designing Reports 9

Simple Application Integration 11

Launching Reports from an Application 12

User Interaction and Dynamic Reporting 12

Intranet and Internet Report Access 14

Seamless Application Integration 14

Web Application Integration 14

Portal Integration 15

Windows Application Integration 15

Managing and Customizing the Report Server 16

Summary 17

Chapter 2: Introduction to Microsoft SQL Server Reporting Services 2005 19

Traditional Application Reporting 20

Today’s Reporting Requirements 21

Business Intelligence Defined 21

Automation to the Rescue — a Scenario 22

Challenges of Existing Reporting Solutions 23

How Does SQL Server Reporting Services Meet This Challenge? 24

02_584979 ftoc.qxp 1/27/06 7:33 PM Page ix

x

Contents

Business Intelligence Solutions 25

Who Uses Reports and Why? 25

Executive Leadership 26

Managers 26

Information Workers 26

Customers 26

Vendors and Partners 27

Reporting Solution Alternatives 27

Reporting with Relational Data (OLTP) 27

Relational Data Warehouses 27

Reporting with Multidimensional Data (OLAP) 28

The Reporting Lifecycle 29

Report Delivery Application Types 29

Web Browser 29

Office Applications 30

Programmability 30

Subscriptions 30

Report Formats 30

Importing Data/Exchanging Data 31

Ad Hoc Reporting 31

System Requirements 31

Reporting Services Components 33

Server Components 33

Client Components 33

Getting Help with Books Online 33

Adventure Works Sample Databases 33

Administrative Tools 34

Command-Line and Unattended Installation 34

Log Files 34

Designing Reports 34

Form Reports 34

Tabular Reports 34

Groupings and Drill-Down 35

Drill-Through Reports 35

Multicolumn Reports 35

Matrix 35

Charts 35

Data Sources 35

Queries 35

OLAP Reporting 36

Using Business Intelligence Development Studio 36

Report Wizard 36

The .NET Framework 37

02_584979 ftoc.qxp 1/27/06 7:33 PM Page x

xi

Contents

Extending Reporting Services 37

Data Processing Extensions 37

Delivery Extensions 37

Security Extensions 37

Rendering Extensions 38

Scripting 38

Subscriptions 38

Securing Reports 38

The Report Manager 39

Designing Reports 39

URL Access to Reports 40

Rendering Reports in Program Code 40

Report Definition Language 41

Deploying Reports 41

Designing and Architecting Report Solutions 41

Third-Party Product Integration 41

Summary 42

Chapter 3: Reporting Services Architecture 45

Reporting Lifecycle 46

Authoring 46

Management 46

Delivery 47

Reporting Services 2005 47

Platform Overview 47

Reporting Services XML Web Service 49

Report Server 50

Reporting Services Catalog 61

Report Design 64

Report User Interface 66

Summary 67

Part Two: Report Design 69

Chapter 4: Basic Report Design 71

Report Design 101 72

Using the Report Wizard 72

The Report Wizard 75

Establishing a Data Source 75

Building a Query 78

02_584979 ftoc.qxp 1/27/06 7:33 PM Page xi

xii

Contents

Defining the Report Structure 82

Specifying the Deployment Location 84

The Report Designer 85

Scale Units 86

Report Definition Language 92

Report Migration and Integration 93

Importing Access Reports 93

Plan for Extensibility 94

Browser Compatibility 94

Offline Viewing 94

Mobile Device Support 95

Report Items and Data Regions 95

Textbox Report Item 96

Line Report Item 98

Rectangle Report Item 98

Image Report Item 98

Subreport Item 102

Chart Report Item 102

Drill-Down and Drill-Through Reports 106

Tabular Reports 108

Grouping Data 108

Subtotals 111

Formatting 112

Standard Formatting 114

Explicit Formatting 115

Conditional Formatting 116

Multiple Columns 118

Pagination Control 119

Page Breaks for a Rectangle 119

Page Breaks for a List 120

Page Breaks for a Table 120

Page Breaks for a Group 120

Page Breaks for a Matrix 121

Page Breaks for a Chart 121

Printing Considerations 121

Summary 122

Chapter 5: Designing Data Access 125

Reporting for Relational Data 127

Query Basics 127

Data Sources 128

Data Sources and Query Languages 130

02_584979 ftoc.qxp 1/27/06 7:33 PM Page xii