McGraw-Hill - Microsoft SQL Server 2008 Reporting Services (2008)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:40

0
119
lượt xem
31
download

McGraw-Hill - Microsoft SQL Server 2008 Reporting Services (2008)01

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

McGraw-Hill - Microsoft SQL Server 2008 Reporting Services (2008)01

Chủ đề:
Lưu

Nội dung Text: McGraw-Hill - Microsoft SQL Server 2008 Reporting Services (2008)01

  1. Microsoft® SQL Server™ 2008 REPORTING SERVICES
  2. About the Author Brian Larson is a Phi Beta Kappa graduate of Luther College in Decorah, Iowa, with degrees in physics and computer science. Brian has 23 years of experience in the computer industry and 19 years experience as a consultant creating custom database applications. He is currently the Chief of Technology for Superior Consulting Services in Minneapolis, Minnesota, a Microsoft Consulting Partner for Reporting Services. Brian is a Microsoft Certified Solution Developer (MCSD) and a Microsoft Certified Database Administrator (MCDBA). Brian served as a member of the original Reporting Services development team as a consultant to Microsoft. In that role, he contributed to the original code base of Reporting Services. Brian has presented at national conferences and events, including the SQL Server Magazine Connections Conference, the PASS Community Summit, and the Microsoft Business Intelligence Conference, and has provided training and mentoring on Reporting Services across the country. He has been a contributor and columnist for SQL Server Magazine. In addition to this book, Brian is the author of Delivering Business Intelligence with Microsoft SQL Server 2008, also from McGraw-Hill. Brian and his wife Pam have been married for 23 years. Pam will tell you that their first date took place at the campus computer center. If that doesn’t qualify someone to write a computer book, then I don’t know what does. Brian and Pam have two children, Jessica and Corey. About the Technical Editor Brian Welcker is a Senior Product Manager in Microsoft’s Health Solution Group, helping to build software solutions for hospitals around the world. Before joining the Health Solutions Group, Brian was Group Program Manager for Microsoft SQL Server Reporting Services, where his team was focused on delivering the premier managed, ad hoc, and developer reporting platform as part of SQL Server and Visual Studio. A 12-year Microsoft veteran, Brian was one of the founding members of the Reporting Services team; he has also worked as the lead program manager for SQL Server Meta Data Services, as well as a technical evangelist in Microsoft’s Developer Relations Group. He joined Microsoft after working for a healthcare software company in his home town of Raleigh, North Carolina. Brian resides in Seattle, Washington with his wife and two children. Copyright © 2009 by The McGraw-Hill Companies. Click here for terms of use.
  3. Microsoft® SQL Server™ 2008 REPORTING SERVICES Brian Larson New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
  4. Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of America. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. 0-07-154809-2 The material in this eBook also appears in the print version of this title: 0-07-154808-4. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904-4069. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inabil- ity to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. DOI: 10.1036/0071548084
  5. This book is dedicated to my family. To my children, Jessica and Corey, who gave up many hours of “dad time” during the writing of this book. And especially to my wife, Pam, who, in addition to allowing me to commit to this project, gave countless hours of her own time to make sure things were done right.
  6. This page intentionally left blank
  7. Contents at a Glance Part I Getting Started Chapter 1 Let’s Start at the Very Beginning . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Chapter 2 Putting the Pieces in Place: Installing Reporting Services . . . . . . . . . . . . 23 Part II Report Authoring Chapter 3 DB 101: Database Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Chapter 4 A Visit to Emerald City: The Report Wizard . . . . . . . . . . . . . . . . . . . . . 111 Chapter 5 Removing the Training Wheels: Building Basic Reports . . . . . . . . . . . . . 171 Chapter 6 Graphic Expression: Using Charts, Images, and Gauges . . . . . . . . . . . . . . 219 Chapter 7 Kicking It Up a Notch: Intermediate Reporting . . . . . . . . . . . . . . . . . . . 297 Chapter 8 Beyond Wow: Advanced Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . 365 Chapter 9 A Leading Exporter: Exporting Reports to Other Rendering Formats . . . . . . 441 Part III Report Serving Chapter 10 How Did We Ever Manage Without You? The Report Manager . . . . . . . . . . 467 Chapter 11 Delivering the Goods: Report Delivery . . . . . . . . . . . . . . . . . . . . . . . . 529 Chapter 12 Extending Outside the Box: Customizing Reporting Services . . . . . . . . . . 571 Part IV Appendixes Appendix A Report Item Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 Appendix B Web Service Interface Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . 709 Appendix C Ad Hoc Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823 vii
  8. This page intentionally left blank
  9. For more information about this title, click here Contents Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi The Galactic Database and Other Supporting Materials . . . . . . . . . . . . . . . xxii Part I Getting Started Chapter 1 Let’s Start at the Very Beginning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Sharing Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 The Need to Share . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Possible Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Microsoft Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Report Authoring Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 The Business Intelligence Project Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Report Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Report Designer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Standalone Report Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Report-Serving Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Report Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Diving In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Chapter 2 Putting the Pieces in Place: Installing Reporting Services . . . . . . . . . . . . . . 23 Preparing for the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 The Parts of the Whole . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Editions of Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Types of Reporting Services Installations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Installation Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Other Installation Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 The Installation Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 The SQL Server 2008 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 The Reporting Services Configuration Manager . . . . . . . . . . . . . . . . . . . . . . . . . 49 ix
  10. x Microsoft SQL Server 2008 Reporting Services Common Installation Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Administrative Rights . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Server Components Not Shown on the Feature Selection Screen . . . . . . . . . . . . . . . 60 Installation Error 2755 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Reporting Services and IIS on the Same Server . . . . . . . . . . . . . . . . . . . . . . . . . 61 The Repair Utility and Installation Log File . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Spending Some Time in Basic Training . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Part II Report Authoring Chapter 3 DB 101: Database Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Getting Organized . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Galactic Delivery Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Company Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Querying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 The SELECT Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 On to the Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Chapter 4 A Visit to Emerald City: The Report Wizard . . . . . . . . . . . . . . . . . . . . . . . . 111 Obtaining the Galactic Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Your First Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 The Customer List Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 An Interactive Table Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 The Customer-Invoice Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Creating Matrix Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 The Invoice-Batch Number Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Report Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 The Parameterized Invoice-Batch Number Report . . . . . . . . . . . . . . . . . . . . . . . 160 Flying Solo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Chapter 5 Removing the Training Wheels: Building Basic Reports . . . . . . . . . . . . . . . 171 Riding Down Familiar Roads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 The Transport List Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 The Tablix and Data Regions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 The Repair Count By Type Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 New Territory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 The Transport Information Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 Getting Graphical . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
  11. Contents xi Chapter 6 Graphic Expression: Using Charts, Images, and Gauges . . . . . . . . . . . . . . . 219 Chart Your Course . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220 The Deliveries versus Lost Packages Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220 The Fuel Price Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 The Fuel Price Chart, Version 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244 The Business Type Distribution Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 The Days in Maintenance Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 Gauging the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 The Digital Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Image Is Everything . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273 Conference Nametags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 Conference Place Cards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 The Rate Sheet Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288 Building Higher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Chapter 7 Kicking It Up a Notch: Intermediate Reporting . . . . . . . . . . . . . . . . . . . . . 297 Never Having to Say “I’m Sorry” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 The Report Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 Handling Errors in Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306 The Employee Time Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307 Data Caching During Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 The Employee List Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324 The Employee Mailing Labels Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 The Overtime Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 The Revised Employee Time Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 Under the Hood . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 Viewing the RDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 Practicing Safe Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361 Using Visual SourceSafe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362 Advance, Never Retreat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364 Chapter 8 Beyond Wow: Advanced Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365 Speaking in Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366 The Delivery Status Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 The Lost Delivery Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377 The Customer List Report—Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 Payroll Checks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
  12. xii Microsoft SQL Server 2008 Reporting Services The Weather Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 The Delivery Analysis Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404 Reports Within Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411 The Employee Evaluation Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412 The Invoice Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424 Interacting with Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428 The Invoice Front-End Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 The Transport Monitor Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432 A Conversion Experience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437 The Paid Invoices Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437 What’s Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440 Chapter 9 A Leading Exporter: Exporting Reports to Other Rendering Formats . . . . . . . 441 A Report in Any Other Format Would Look as Good . . . . . . . . . . . . . . . . . . . . . . . . . . . 443 Exporting and Printing a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443 Presentation Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445 TIFF Image Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447 Adobe PDF Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450 Web Archive Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 Excel Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 Word Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 Printed Presentation Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 Data Exchange Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 Comma-Separated Values (CSV) Data Exchange Format . . . . . . . . . . . . . . . . . . . . 460 XML Data Exchange Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461 Call the Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464 Part III Report Serving Chapter 10 How Did We Ever Manage Without You? The Report Manager . . . . . . . . . . . 467 Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468 The Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469 Moving Reports and Supporting Files to the Report Server . . . . . . . . . . . . . . . . . . . . . . . 472 Deploying Reports Using the Report Designer . . . . . . . . . . . . . . . . . . . . . . . . . . 472 Uploading Reports Using Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479 Uploading Other Items Using Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Uploading Reports Using .NET Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 Modifying Reports from the Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
  13. Contents xiii Managing Items in Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502 Moving Items Between Folders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502 Deleting a Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503 Renaming a Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504 Seek and Ye Shall Find: Search and Find Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504 Searching for a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504 Finding Text Within a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505 Printing from Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506 Printing Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506 Managing Reports on the Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508 Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511 Linked Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524 Creating a Linked Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525 Delivering the Goods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 Chapter 11 Delivering the Goods: Report Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . 529 Caching In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530 Report Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531 Enabling Report Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 Execution Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 Enabling Execution Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 Report History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542 Enabling Report History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542 Managing Report History Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546 Updating Report Definitions and Report History Snapshots . . . . . . . . . . . . . . . . . . 548 Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549 Standard Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549 Managing Your Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554 Data-Driven Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 Site Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563 The General Site Settings Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563 Other Pages Accessed from the Site Settings Page . . . . . . . . . . . . . . . . . . . . . . . 565 Managing Reporting Services Through the SQL Server Management Studio . . . . . . . . 566 Additional Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569 A Sense of Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569 The ReportingServices Style Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569 Building On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 570
  14. xiv Microsoft SQL Server 2008 Reporting Services Chapter 12 Extending Outside the Box: Customizing Reporting Services . . . . . . . . . . . . 571 Using Reporting Services Without the Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . 572 URL Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572 Web Service Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585 The Report Viewer Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 SharePoint Web Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 Reporting Services Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597 Custom Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608 Authentication and Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609 Issues with Custom Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611 Creating a Custom Security Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613 Deploying a Custom Security Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621 Using the Custom Security Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627 Other Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 Report-Authoring Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629 Report Deployment Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631 Where Do We Go from Here? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 Part IV Appendixes Appendix A Report Item Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 Report Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638 Layout Areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638 Data Regions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639 Report Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645 Property Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 Appendix B Web Service Interface Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709 Reporting Services Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710 Creating a Web Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710 Credentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711 Compatibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712 ReportExecution2005 Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712 ReportExecution2005 Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713 ReportService2005 Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721
  15. Contents xv ReportService2005 Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 722 ReportService2006 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755 ReportService2005 and ReportExecution2005 Web Service Classes . . . . . . . . . . . . . . 757 Appendix C Ad Hoc Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781 The Report Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782 Creating a Report Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783 Creating Reports with the Report Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800 Report Builder Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800 Creating a Table Layout Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805 Creating a Matrix Layout Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 817 Creating a Chart Layout Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 819 Give It a Try . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 822 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823
  16. This page intentionally left blank
  17. Foreword A t the end of 2005, the Reporting Services development team had some tough decisions to make. Unlike most of the SQL Server development team, who had been working on the 2005 release for close to five years, it had only been a year and a half since we shipped the first version of Reporting Services. In that same timeframe, we shipped two service packs, acquired a new ad hoc reporting tool that we delivered as Report Builder, and built a set of report controls that shipped in Visual Studio 2005. The follow-up release, code-named “Katmai,” was scheduled for a relatively quick two- to three-year turnaround. During this time, we also learned a lot about how people were using Reporting Services. When we started development, our initial assumption was that customers wanted to enable web-based delivery of reports with small to medium-sized data sets. While this was the case for most customers, there was another set of users that wanted to be able to export reports as single documents with up to several thousand pages. As the memory required for generating these reports could be far beyond the actual memory available on the server, supporting this scenario wasn’t a trivial task. In fact, it would require a large development effort that would consume most of the schedule allocated for the 2008 release. At the same time, users had also requested a number of smaller features that would make their lives easier and their reports better. We couldn’t fit both, so a decision had to be made. After much debate, we decided to take a long-term approach and build a new engine to support report scalability. New reporting features were put on the back burner until this work was completed. This means that while you won’t see as many new “bells and whistles” in the 2008 release, there has been a massive amount of work below the surface to ensure that Reporting Services can handle your biggest reporting workloads. In the end, we did manage to squeeze in quite a few nice features into the release that you will like, even if you aren’t pushing the bounds of scalability. At the end of 2007, it was my turn to make a tough decision. I had been working on the SQL Server team for over ten years, with six of those on Reporting Services. While I was extremely proud of the team that we had assembled and excited by the upcoming release of SQL Server 2008, I felt that it was time to move on to new challenges. This January, I moved to Microsoft’s new Health Solutions Group, where I am the product manager for a new line of software applications for hospitals. I still keep in touch with the Reporting Services development team and have been trying out the newest features as the product approaches release. xvii Copyright © 2009 by The McGraw-Hill Companies. Click here for terms of use.
  18. xviii Microsoft SQL Server 2008 Reporting Services Brian Larson was one of the early proponents of Reporting Services and completed the first edition of this book just as we wrapped up the work on the first release. While many other Reporting Services books have appeared on the market, I always considered Brian’s book to be the best all-around introduction to the product. I enjoyed working with Brian on the first two editions of this book and was excited when he asked me to assist with this update. The new release of Reporting Services is the most powerful and flexible yet. Whether you are a seasoned professional or a first-time user, I hope that you use the information and techniques presented in this book to get the most out of the product. —Brian Welcker Senior Product Manager Microsoft Health Solutions Group
  19. Acknowledgments A journey of a thousand miles begins with a single step.” Perhaps this book project was not a journey of a thousand miles, although it seemed that way in the early hours of the morning with a deadline approaching. Be that as it may, it is possible to identify the first step in this whole process. A coworker of mine at Superior Consulting Services, Marty Voegele, was between assignments, on-the-bench, in consultant-speak. Marty was bored, so he decided to take matters into his own hands. Marty had previously consulted to Microsoft and still had contacts in the SQL Server area. He made a few phone calls and before long, Marty was again consulting to Microsoft, this time creating something called Rosetta. As additional work was added, I had the opportunity to take on part of this assignment as well. It was both challenging and exciting working on code that you knew would be part of a major product from a major software company. What was perhaps most exciting was that Rosetta seemed to be a tool that would fill several needs we had identified while developing custom applications for our own clients. As the beta version of what was now called Reporting Services was released, a brief introductory article on Reporting Services appeared in SQL Server Magazine. One of the sales representatives here at Superior Consulting Services, Mike Nelson, decided this would be a nice bit of marketing material to have as we trumpeted our involvement with Reporting Services. One thing led to another, and before we knew it, Mike had offered Marty’s and my services to write a more in-depth article for SQL Server Magazine. This article became the cover article for the December 2003 issue and has become known as the “Delightful” article. (You’ll have to read the first paragraph of the article to understand why.) It is now available on MSDN. This was where I grabbed the map and compass, and decided on the next path. Because the magazine article came out fairly well, I decided to write a book on the topic. Marty informed me that writing a 700-page book would probably make his fingers fall off, so I could take this next step on my own. So, here we are today, one book and two revisions later. All of this is a rather lengthy way of saying that I owe a big thank you to Marty and Mike. Without a shadow of a doubt, this book would not have happened without them. In addition to the contributions already stated, I want to thank Marty for helping to keep me up-to-speed on Reporting Services information and newsgroup postings. We have learned a great deal preparing presentations on Reporting Services and providing Reporting Services solutions for clients. xix Copyright © 2009 by The McGraw-Hill Companies. Click here for terms of use.
Đồng bộ tài khoản