4983FM.qxd 10/7/05 12:05 PM Page i Pro SQL Server 2005 Reporting Services Rodney Landrum and Walter J. Voytek II 4983FM.qxd 10/7/05 12:05 PM Page ii Pro SQL Server 2005 Reporting Services Copyright © 2006 by Rodney Landrum and Walter J.Voytek II All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN (pbk): 1-59059-498-3 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Tony Davis Technical Reviewer: Trish Middleton, Chris Rausch, Thomas Rizzo Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Project Manager: Sofia Marchant Copy Edit Manager: Nicole LeClerc Copy Editor: Kim Wimpsett, Julie McNamee Assistant Production Director: Kari Brooks-Copony Production Editor: Laura Cheu Compositor and Artist: Kinetic Publishing Services, LLC Proofreader: April Eddy, Linda Seifert Indexer: Broccoli Information Management 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 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. 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.comin the Source Code section. 4983FM.qxd 10/7/05 12:05 PM Page iii To all the victims of hurricanes Dennis,Katrina,and Rita, which ravaged the Gulf Coast during the 2005 hurricane season. Rodney Landrum andWalter J.Voytek II 4983FM.qxd 10/7/05 12:05 PM Page iv 4983FM.qxd 10/7/05 12:05 PM Page v Contents at a Glance About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix ■ CHAPTER 1 Introducing the Reporting Services Architecture. . . . . . . . . . . . . . . . . 1 ■ CHAPTER 2 Report Authoring:Designing Efficient Queries . . . . . . . . . . . . . . . . . . 17 ■ CHAPTER 3 Using Report Designer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 ■ CHAPTER 4 Building Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 ■ CHAPTER 5 Using Custom .NET Code with Reports . . . . . . . . . . . . . . . . . . . . . . . . 123 ■ CHAPTER 6 Rendering Reports from .NET Applications. . . . . . . . . . . . . . . . . . . . . 147 ■ CHAPTER 7 Deploying Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 ■ CHAPTER 8 Managing Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 ■ CHAPTER 9 Securing Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 ■ CHAPTER 10 Delivering Business Intelligence with SSRS. . . . . . . . . . . . . . . . . . . . 285 ■ CHAPTER 11 Performing Ad Hoc Reporting Using Report Builder . . . . . . . . . . . . 321 ■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 v 4983FM.qxd 10/7/05 12:05 PM Page vi 4983FM.qxd 10/7/05 12:05 PM Page vii Contents About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix ■ CHAPTER 1 Introducing the Reporting Services Architecture. . . . . . . . . . 1 Understanding the Benefits of SSRS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 SQL Server 2005 Reporting Services Enhancements. . . . . . . . . . . . . 4 SSRS and Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Exploring the SSRS Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 SSRS Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 The SSRS Report Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Client Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Installing and Configuring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Deploying SSRS Securely. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 ■ CHAPTER 2 Report Authoring: Designing Efficient Queries. . . . . . . . . . . . 17 Introducing the Sample Relational Database. . . . . . . . . . . . . . . . . . . . . . . . 18 Introducing the Schema Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Knowing Your Data:A Quick Trick with a Small Procedure. . . . . . . . 19 Introducing Query Design Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Creating a Simple Query Graphically. . . . . . . . . . . . . . . . . . . . . . . . . . 21 Creating an Advanced Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Using a Parameterized Stored Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Using Case and ISNULL to Evaluate the Parameters. . . . . . . . . . . . . 32 Testing the Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 vii 4983FM.qxd 10/7/05 12:05 PM Page viii viii ■ CONTENTS ■ CHAPTER 3 Using Report Designer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 Setting Up Filters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Laying Out a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Setting Up Pagination. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Using Report Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Implementing Two Simple Report Design Tips. . . . . . . . . . . . . . . . . . 77 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 ■ CHAPTER 4 Building Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Creating a Report with the Report Wizard. . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Building Reports from Scratch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Formatting the Output. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Adding Subtotals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Adding Interactivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Setting Report Parameters with Stored Procedures . . . . . . . . . . . . . . . . . 105 Applying a Filter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Adding a Chart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Adding the Final Touches. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Working with Multivalued Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 ■ CHAPTER 5 Using Custom .NET Code with Reports . . . . . . . . . . . . . . . . . . . 123 Using Embedded Code in Your Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Using the ExceedMaxVisits Function . . . . . . . . . . . . . . . . . . . . . . . . . 125 Using the ExceedMaxVisits Function in a Report. . . . . . . . . . . . . . . 128 Accessing .NET Assemblies from Embedded Code. . . . . . . . . . . . . 131 Using Custom Assemblies with Your Report. . . . . . . . . . . . . . . . . . . . . . . . 132 Adding a Class Library Project to Your Reporting Solution. . . . . . . 133 Deploying a Custom Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Adding an Assembly Reference to a Report . . . . . . . . . . . . . . . . . . . 140 Debugging Custom Assemblies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Troubleshooting Your Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 4983FM.qxd 10/7/05 12:05 PM Page ix ■ CONTENTS ix ■ CHAPTER 6 Rendering Reports from .NET Applications . . . . . . . . . . . . . . 147 Implementing URL Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 URL Report Access Path Format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 URL Parameters and Prefixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Report Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 HTML Viewer Commands. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Report Server Command Parameters. . . . . . . . . . . . . . . . . . . . . . . . . 151 Credential Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Example URLs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Integrating SSRS 2005 with .NET Applications . . . . . . . . . . . . . . . . . . . . . 153 Building the Report Viewer Using a WebBrowser Control. . . . . . . . 153 Building the Report Viewer Using a Report Viewer Control. . . . . . . 155 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 ■ CHAPTER 7 Deploying Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Using Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 Using SQL Server Management Studio (SSMS). . . . . . . . . . . . . . . . . . . . . 179 Using BIDS and Visual Studio 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Configuring Report Deployment Options. . . . . . . . . . . . . . . . . . . . . . 181 Using the Report Server Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 Accessing the Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Laying Out the Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Coding the Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Running the Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 ■ CHAPTER 8 Managing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 Exploring Management Roles in SSRS Deployment . . . . . . . . . . . . . . . . . 197 Managing Content. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Setting Up Shared Schedules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Setting Up a Data Source for the Report. . . . . . . . . . . . . . . . . . . . . . 205 Creating Snapshots for the Report History . . . . . . . . . . . . . . . . . . . . 206 Executing Reports and Performing Caching . . . . . . . . . . . . . . . . . . . 209 Managing Subscriptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 Using SSMS and SSRS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Performing Execution Auditing and Performance Analysis. . . . . . . . . . . . 225 Configuring SSRS Logging. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226 Monitoring Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231