Professional SQL Server 2005 Reporting Services

723 Pages·2006·22.788 MB·English

by  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
Paul Turley
Todd Bryant
James Counihan
Dave DuVarney
01_584979 ffirs.qxp 1/27/06 7:43 PM Page iv
01_584979 ffirs.qxp 1/27/06 7:43 PM Page i
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 ii
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 SQLServer 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. SQLserver. 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, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal
Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone],
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 ACCURACYOR COMPLETENESS OF THE CONTENTS OF
THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WAR-
RANTIES OF FITNESS FOR APARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY
SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE
SUITABLE FOR EVERYSITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS
NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFES-
SIONALASSISTANCE IS REQUIRED, THE SERVICES OF ACOMPETENT PROFESSIONALPERSON SHOULD BE
SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALLBE LIABLE FOR DAMAGES ARISING HEREFROM.
THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS ACITATION AND/OR A
POTENTIALSOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER
ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAYPROVIDE OR RECOMMENDATIONS IT
MAYMAKE. 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 [phone], outside the United States at [phone] or fax [phone].
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. SQLServer 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 iii
Credits
Acquisitions Editor Vice President and Executive Publisher
Katie Mohr Joseph B. Wikert
Development Editor Graphics and Production Specialists
Tom Dinse Lauren Goddard
Denny Hager
Technical Editor Jennifer Heleine
Todd Meister Alicia B. South
Production Editors Quality Control Technicians
Angela Smith Leeann Harney
William A. Barton Joseph Niesen
Charles Spencer
Copy Editor Robert Springer
Foxxe Editorial Services
Media Development Specialists
Editorial Manager Angela Denny
Mary Beth Wakefield Kit Malone
Travis Silvers
Production Manager
Tim Tate Proofreading and Indexing
TECHBOOKS Production Services
Vice President and Executive Group Publisher
Richard Swadley
01_584979 ffirs.qxp 1/27/06 7:43 PM Page iv
01_584979 ffirs.qxp 1/27/06 7:43 PM Page v
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 SQLServer. 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, SQLServer, and
Reporting Services.
Paul authored BeginningTransact SQLfor SQLServer 2000 and2005. He has been a contributing author
on books and articles including Professional Access 2000 Programming, Beginning Access 2002 VBA, and
SQLServer 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 Bryanthas 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 CNAcertification
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 vi
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 Ato 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
SQLServer 2005 Analysis Services, Reporting Services, and Integration Services. Dave is the coauthor
of Professional SQLServer 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 vii
Acknowledgments
Our sincere thanks go to the members of the Reporting Services product team at Microsoft, who have been
very supportive and accessible. Alarge 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.
Abig thank yougoes 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.