Professional SQL Server™ 2005 Reporting Services

Chia sẻ: Phung Tuyet | Ngày: | Loại File: PDF | Số trang:723

lượt xem

Professional SQL Server™ 2005 Reporting Services

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

Contents Acknowledgments Foreword I Foreword II Introduction Part One: Getting Started Chapter 1: What Can You Do with Reporting Services? Chapter 2: Introduction to Microsoft SQL Server Reporting Services 2005 Chapter 3: Reporting Services Architecture Part Two: Report Design Chapter 4: Basic Report Design Chapter 5: Designing Data Access Chapter 6: Advanced Report Design Chapter 7: Report Solution Patterns and Recipes Part Three

Chủ đề:

Nội dung Text: Professional SQL Server™ 2005 Reporting Services

  1. Professional SQL Server™ 2005 Reporting Services Paul Turley Todd Bryant James Counihan Dave DuVarney
  2. Professional SQL Server™ 2005 Reporting Services Paul Turley Todd Bryant James Counihan Dave DuVarney
  3. Professional SQL Server™ 2005 Reporting Services Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 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 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.
  4. 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 Jennifer Heleine Technical Editor Todd Meister Alicia B. South Production Editors Quality Control Technicians Angela Smith Leeann Harney William A. Barton Joseph Niesen Charles Spencer Robert Springer Copy Editor Foxxe Editorial Services Media Development Specialists Angela Denny Editorial Manager 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
  5. 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, 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 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.
  6. 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.
  7. 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.
  8. 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
  9. 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 x
  10. 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 xi
  11. 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 xii
  12. Contents Filtering Techniques 131 Parameter Concepts 132 Filtering Data with Query Parameters 134 Report Parameters 135 Basing a Parameter on a Query 137 Cascading Parameters 138 Using Stored Procedures 148 Filtering Data with Report Parameters 151 Reporting for Analytical Data 155 SQL Server Analysis Services 155 Using Other Data Sources 173 Microsoft Access 174 Microsoft Excel 178 Oracle P/L SQL 178 Sybase Adaptive Server 179 Best Practices 180 Summary 180 Chapter 6: Advanced Report Design 183 Anatomy of a Text Box 184 Grouping Data 186 Data Regions 187 Using the List Item 187 Creating a Tabular Report Using a Table 192 Column Placement and Indentation 199 Headers and Footers 203 Aggregate Functions and Totals 205 Using the Expression Builder 206 Drill-Down Reports 208 Creating a Document Map 210 Links and Drill-Through Reports 212 Bookmarks and Links 212 Drill-Through Reports 212 Recursive Relationships 214 Subreports 218 Designing Matrix Reports 221 Subtotals and Summaries 224 Drill-Down in a Matrix 225 Chart Reports 228 Chart Types 228 Column Charts 230 Area and Line Charts 233 xiii
  13. Contents Pie Charts 233 Bubble Charts 235 The Anatomy of a Chart 237 Chart Report Exercise 239 Custom Fields 245 Conditional Expressions 246 IIF() Is Your Friend 246 Using Custom Code 249 Why Visual Basic? 249 Using Custom Code in a Report 250 Using a Custom Assembly 251 Custom Assembly Security 253 Errors, Warnings, and Debugging Code 254 Designing for Mobility 254 Best Practices and Tips 259 Summary 260 Chapter 7: Report Solution Patterns and Recipes 261 Reporting Project Requirement Guidelines 262 Key Success Factors 262 Reporting on Existing Data Sources 263 Building an End-to-End Reporting Solution 264 Report Specifications 265 Development Phases 266 Migrating and Converting Reports 268 Working with the Strengths and Limitations of the Architecture 269 Report Recipes 272 Greenbar Reports 272 Multiple Criterion Report Filtering 277 Multi-field Data Point Charts 279 TOP X and “Other” Chart 285 Dynamic Images: Scales and Gauges 286 Creating a Business Scorecard 289 Creating Sparklines 297 Using Field Values in Page Headers and Footers 303 Group Continued in Page Header or Footer 304 Dynamic Grouping 305 Sorting on Column Headers 308 Dynamic Fields and Columns 315 Modifying Data from a Report 316 Selected and Deselected Item List 320 xiv
  14. Contents Using Advanced and Third-Party Report Items 324 Dynamic Image Content 328 Using Advanced and Third-Party Controls for Parameter Selection 335 Summary 337 Part Three: Enabling End User Reporting with Report Builder 339 Chapter 8: Reporting Services Report Models 341 Getting Started 341 Creating the Report Model Data Source 342 Building a Data Source View 345 Building the Report Model 353 Report Model Wizard 353 Working with Reporting Services Report Models 357 Deploying the Report Model 362 Building Report Models from Analysis Services Databases 363 Summary 366 Chapter 9: Report Builder 369 Building Report Models 369 Working with the Report Builder 370 Accessing the Report Builder 370 Building a Report 371 Formatting a Report 385 Filtering and Sorting Reports 389 Adding Calculations with Expressions 394 Administration 397 Summary 397 Part Four: Administering Reporting Services 399 Chapter 10: Report Management 401 Report Server Content 401 Managing Content Items 402 Report Manager 402 SQL Server Management Studio 404 Other Utilities 405 Programmatic Interfaces 405 Securing Report Server Content 406 Role-Based Security 406 Tasks 407 xv
  15. Contents Roles 407 Role Assignments 410 Security Inheritance 413 Item-Level Security 413 System-Level Security 413 Site Settings 414 Properties 414 My Reports 415 Securing My Reports 416 Managing Reports 417 Working in Folders 417 Report Properties 418 Linked Reports 420 Publishing Reports 421 Updating Reports 423 Working with Data Sources 423 Private Data Sources 424 Shared Data Sources 424 Creating Data Sources 424 Data Source Credentials 426 Updating Data Sources 426 Managing Report Execution 427 Report Execution Process 427 On-demand Report Generation 428 Working with Cached Instances 428 Snapshots and History 429 Report History 431 Scheduling Reports 432 Delivering Reports 433 E-Mail Delivery 433 File Share Delivery 434 Report Subscriptions 435 Snapshot-Triggered Subscriptions 436 Schedule-Triggered Subscriptions 436 Data-Driven Subscriptions 437 Automating Content Management 437 Automating Subscription Management 437 Automating Report Deployment 447 Creating Scripts 448 Using the RS WMI Provider 448 Creating a Script Using Code 449 Summary 455 xvi
  16. Contents Chapter 11: Report Server Administration 457 Deploying Reporting Services 458 Reporting Services Components 459 The Report Server 463 Server Configurations 467 Configuration Tools 470 Configuration Utilities 471 Configuration Files 474 Backup and Restore Procedures 474 Backing Up the Report Server Catalog 474 Backing Up the Encryption Key 475 Monitoring and Performance 476 Report Execution 477 Caching 477 Exploring the Execution Log 478 Performance Counters 480 Summary 484 Part Five: Reporting Services Integration and the Reporting Services Web Services 485 Chapter 12: Integrating Reporting Services into Custom Applications 487 URL Access 488 URL Syntax 488 Accessing Reporting Services Objects 489 Reporting Services URL Parameters 493 Passing Report Information through the URL 498 Programmatic Rendering 500 Common Scenarios 501 Rendering through Windows 502 Rendering to the Web 518 Using the ReportViewer Control 526 Embedding a Server-Side Report in a Windows Application 527 SharePoint Web Parts 534 Report Explorer Control 534 ReportViewer Control 535 Summary 535 xvii



Đồng bộ tài khoản