Professional Microsoft® SQL Server® 2008 Reporting Services

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

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

Published simultaneously in Canada

ISBN: 978-0-470-24201-8

Library of Congress Cataloging-in-Publication Data

Professional Microsoft SQL server 2008 reporting services / Paul Turley ... [et al.].

p. cm.

Includes index.

ISBN 978-0-470-24201-8 (paper/website)

1. SQL server. 2. Database management. I. Turley, Paul.

QA76.9.D3P7663 2009

005.75'85—dc22

2009042930

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 www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties 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 professional 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 web site 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 web site may provide or recommendations it may make. Further, readers should be aware that Internet web sites 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, Wrox Programmer to Programmer, and related trade dress are trademarks 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. Microsoft and SQL Server are registered trademarks 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.

This is dedicated to the memory of my Mom, who always believed in me. To my family: my wife, Sherri, for her unwavering love and support; my wonderful daughters and son for their understanding and patience; and to my Dad, Mark, for always being there.

—Paul Turley

I dedicate this book to the women in my life: my lovely wife, Michelle, who has patiently put up with me and supported me in this venture; my beautiful daughter, Gabriella, whose sunshine smiles have lit up my every day; and, finally, I dedicate this to my mother, Lucia, who has encouraged me and given me words of wisdom my entire life.

—Thiago Silva

I dedicate this book to my family, immediate and extended, for their support and encouragement throughout the development of this book.

—Bryan C. Smith

I dedicate this book to my wife, Rosemarie, and thank her for giving up nearly every weekend outing during the summer of 2008 while I was writing this book. Rosemarie has been my strength in this world, and her presence in my life has taken me further than I ever dreamed was possible. I love you!

—Ken Withee

About the Authors

Paul Turley (Vancouver, WA) is a Manager of Specialized Services for Hitachi Consulting Education Services. Paul manages the Business Intelligence training team and teaches classes for companies throughout the world on Microsoft SQL Server technologies. He works with companies to design architecture for and build BI and reporting solutions. He has been developing business database solutions since 1991 for companies like Microsoft, Disney, Nike, and Hewlett-Packard. He has been a Microsoft Certified Trainer since 1996 and holds several industry certifications, including MCTS and MCITP for BI, MCSD, MCDBA, MSF Practitioner, and IT Project+.

Paul has authored and coauthored several books and courses on database, business intelligence, and application development technologies. He is the lead courseware developer for the Hitachi Consulting courses: “SQL Server 2008 Business Intelligence Solutions” and “SQL Server 2008 Reporting Services Solutions.” His books include Professional SQL Server 2005 Reporting Services, Professional SQL Server Reporting Services (SQL Server 2000), Beginning Transact-SQL with SQL Server 2000 and 2005, Beginning SQL Server 2005 Administration, Beginning Access 2002 VBA, Data Warehousing with SQL Server 2000 Analysis Services, and Professional Access 2000 Programming — all from Wrox. He is also a contributing author to SQL Server 2005 Integration Services Step by Step from Microsoft Press.

Thiago Silva (Dallas, TX) is a Manager of Specialized Services for Hitachi Consulting. Thiago has also been designing and developing custom .NET, business intelligence, and Reporting Services solutions since the early days of .NET and SQL Server 2000. He is a part of the Microsoft Strategic Alliance leadership group within Hitachi Consulting, where he helps create, manage, and deliver internal training materials and intellectual capital around the Microsoft technology stack. He also teaches the SQL Server Reporting Services course offered by Hitachi Consulting.

Thiago has been a featured guest on the talk show podcast .NET Rocks and is an active member of the .NET development community, frequently writing on his blog Silvaware, at http://silvaware.blogspot.com. Thiago holds a Bachelor of Business Administration degree in Management Information Systems from Texas A&M University, and a MCAD.NET technical certification.

Bryan C. Smith (Irving, TX) is a Manager of Specialized Services for Hitachi Consulting. Bryan has been developing and administering database solutions since 1997 for clients in a variety of industries. These days, he focuses on helping clients build Business Intelligence solutions using the Microsoft SQL Server product suite. Bryan holds MCITP, MCTS, MCDBA, A+, Network+, and Server+ certifications, and serves as an instructor for Hitachi Consulting’s SQL Server Analysis Services course.

Ken Withee (Seattle, WA) is a Senior Consultant with Hitachi Consulting. He earned a Master of Science degree in Computer Science studying under Dr. Edward Lank at San Francisco State University. Their work has been published in the LNCS journals and was the focus of a presentation at the IASTED conference in Phoenix. Their work has also been presented at various other Human Computer Interaction conferences throughout the world.

Ken has more than 7 years of professional computer and management experience working with a vast range of technologies.

Ken is a Microsoft Certified Technology Specialist and is certified with Microsoft Office SharePoint Server 2007 and SQL Server 2005, and has passed the certification exam for .NET 2.0.

Ken currently lives with his wife Rosemarie in Seattle, WA.

Credits

Acquisitions Editor

Katie Mohr

Development Editor

John Sleeva

Technical Editor

David Dillon

Production Editor

Christine O’Connor

Copy Editor

Cate Caffrey

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

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Publication Services, Inc.

Indexer

Johnna VanHoose Dinse

Acknowledgments

I acknowledge Thierry D’Hers, Brian Welcker, Jason Carlson, Chris Hays, Sean Boon, Lukasz Pawlowski, and all the other members of the Reporting Services product team who have been so accessible and supportive of this effort.

Thanks to Lance Baldwin for his guidance; to Drew Naukam for his leadership; and to Stephanie Gulick, Chris Leiter, D.J. Norton, and the rest of the Microsoft National team at Hitachi Consulting for their support and many contributions. Thanks to Reed Jacobson for his contributions and to everyone at Hitachi Consulting for being a world-class organization, co-workers, and friends.

Thanks to Katie Mohr and John Sleeva at Wrox for their patience, support, and encouragement.

Finally, thanks to the dedication of my coauthors: Thiago Silva for his hard work and resourcefulness, Bryan C. Smith for his help and advice, and Ken Withee for stepping up to try something new. You’re all true professionals.

—Paul Turley

I acknowledge:

Thierry D’Hers, Robert Bruckner, Lukasz Pawlowski, Chris Hays, Chris Baldwin, and Brian Welcker, at Microsoft, for their guidance and technical assistance during the authoring of the book;

Lead author Paul Turley for giving me the opportunity to be a part of this project, as well as my coauthors Bryan C. Smith and Ken Withee for assisting with ideas and insightful discussions throughout the entire writing process.

John Sleeva and Katie Mohr at Wrox for their flexibility and guidance during the development of the book.

My colleagues at Hitachi Consulting, who have helped me sharpen my skills and become a better consultant and developer.

My family in Brazil, who have provided encouragement during the writing of this book.

And, finally, God, who gives me strength each day and lifts me up when I’ve fallen down.

—Thiago Silva

I acknowledge:

John Sleeva and Katie Mohr at Wrox for their guidance on the book.

Carolyn Chau, Chris Baldwin, John Sirmon, and Matthew Hofacker at Microsoft for their response to technical questions.

Coauthors Paul Turley, Ken Withee, and Thiago Silva for their ideas, suggestions, and assistance in designing and authoring the book.

Haruka, Akimori Charles, and Harumi Katheryn for their patience and support throughout the last year.

—Bryan C. Smith

I acknowledge my mother, Maggie Blair, who has taught me persistence and compassion and who gave me the best upbringing I could ever have hoped for; and my father, Ken Withee, who offered to autograph copies for a price since he has the same name. Dad also taught me that commonsense will take you very far in this world. Thanks, also, to my sister, Kate Henneinke, who is working her way through college and who, I am confident, will achieve all of her goals. I am confident, as her older brother, that she is a stronger person because of all the karate moves I pounded her with growing up. I also acknowledge my grandma, Tiny Withee, who recently turned 95 and is as strong as ever; and in memory of my grandpa, Del Withee, who taught me there is never a problem so difficult that it cannot be solved with the right amount of thought and effort. Thanks also to my parents-in-law, Alfonso and Lourdes Supetran, and family, for all their support and practicality. They have truly made me a global thinker and taught me how to be a successful businessman. I only hope that Rosemarie and I can have a fraction of the success they have had in their lifetimes.

Thanks to Dr. Edward Lank, who got me started down the publishing path back in graduate school.

I also acknowledge the Microsoft SSRS team (especially Thierry D’Hers), who have always taken the time to answer questions and clarifications, and my fearless Microsoft National team at Hitachi Consulting, including our leader Drew Naukam and my career advisor Dave Cooper.

Thanks to Katie Mohr and John Sleeva at Wrox for their patience and support.

Thanks to my coauthors: Paul Turley, who gave me the chance to be a part of this book; Bryan C. Smith, for the long weekend chats as we nailed down technology; and Thiago Silva, for always going the extra step to help through all aspects of the book. It has been a great experience, and I truly enjoy working with all of you at Hitachi Consulting.

—Ken Withee

Foreword by Jason Carlson

Many people have asked me, “How can you be so passionate about reporting when it is so mundane?” To me, the most exciting thing about reporting is that it is so very common. Like basic transportation, everybody uses it in some way or another. A report is a piece of art meant to convey a message; but unlike traditional art, that message changes based on the data driving it. The potential to help, and be used by, millions of people and companies is one of the reasons I started writing software and eventually joined Microsoft. No other company can reach out to so many people by making great products accessible.

Reporting is a very broad topic, covering areas ranging from packing lists and telephone bills to ad hoc analysis and Excel spreadsheets. When designing Microsoft SQL Server Reporting Services, I started with a simple definition for it: an information delivery platform. Although this definition is also very broad, it did allow us to focus on our design while leaving us significant room to expand in later versions. This book will help you understand the power of Reporting Services and to fully utilize its capabilities.

Information is not just data; it is data that has been transformed into something meaningful. This transformation is important. Any tool can read and display data; what people really need for doing their jobs is well-thought-out, correct, and pertinent information. There are many tools that let anyone with access to data build “views” or “reports.” However, often these users are unfamiliar with all of the nuances of the data storage and this can produce inaccurate results or inadvertently affect the performance of the data engine. Reporting Services acts as the official source so that there is only one version of the truth that everyone uses.

In the future, Microsoft plans to take this even further by integrating with Information Rights Management so that not only will the information come from a single source, but it will also be certified, can expire, and can be access-controlled even after it is delivered to the end-user. The data does not always exist in one database or even come from a database. For those of us who have spent careers working with corporate data, this is a painful truth. Very few reports (or sets of reports that give you sufficient insight) come from a single source. Building some type of data mart or data warehouse is the best solution, but it is not always possible because of timing, policy, or budgetary constraints. Reports must be able to retrieve data from any source and combine them in a single report.

What good is information if you do not have it when you need it? Delivering information is more than just processing it and making it available; it is providing information when you need it, in any format, and on any device you have. The common case today is the ubiquitous online access via HTML in a browser. This is perfect when you have a computer and connectivity to the server. However, as we all know, nothing is perfect. We need the reports when we are on a plane, in a car, with the customer, at the game, on the production floor, and so on. This may include on your pager, telephone, fax machine, laptop, paper, and other devices. We also need different capabilities: interactivity, pixel-perfect printing, integration into applications like MS Excel for “what if” scenarios and additional analysis, universal access via PDF, and the like. A single format and a single delivery channel are not enough, but how do you know which ones you will need? Reporting Services insulates you from these choices. All reports can be distributed in any channel or rendered in any format. Report design is independent of how it will be consumed. It is the responsibility of the system to provide the report as accurately as possible, given the constraints of the specific format or channel requested.

Building a platform is very different from building a solution. In fact, the goals are in many cases completely opposed. A platform is successful if the developers and administrators have complete access to all aspects of the product. They need to be able to optimize, extend, restrict, embed, and replace parts of the product to meet their needs. This means that all of the APIs are available and documented, all formats are open and described, and every component is configurable or replaceable. While there are always restrictions due to the many trade-offs in software design, this was the goal when building Reporting Services. Very much like Windows, SQL Server, or Visual Studio, Reporting Services is designed to enable developers to build on a solid foundation and mold it to meet their business needs in significantly less time and with more functionality, but without losing the flexibility and power of building it themselves.

Looking into the future, there’s an endless list of features and scenarios that Microsoft will add to make the platform more powerful with little or no additional in-house development required. I have mentioned some, and there are many that haven’t even been considered yet.

We look forward to hearing from all our customers about what is important to them and how we can make designing, building, and operating their information delivery systems easier, faster, and (I hope) more fun.

—Jason Carlson

Product Unit Manager

SQL Server Reporting Services

Microsoft

Jason Carlson is the Product Unit Manager for SQL Server Reporting Services. He joined Microsoft in 1996 as a Program Manager for Visual Source Safe and Repository. In 1997, the Repository team joined SQL Server, and Jason became the development manager for SQL Server Meta Data Services. In 2001 he built a team and started work on V1 of Reporting Services. Before joining Microsoft, Jason owned and operated an independent software development company. This company provided consulting and vertical software solutions for healthcare and telecommunications.

Foreword by Thierry D’hers

Six months ago, a major change happened in the SQL Server Reporting Services team. Brian Welcker, who had been a key figure of the team since its inception, decided to change course in his career. Brian Welcker is one of the reasons Reporting Services is where it is today. When Tom Casey (General Manager for SQL Server Business Intelligence Unit) and Jason Carlson (Reporting Service Product Unit Manager) approached me 6 months ago to offer me Brian’s position, I was honored, excited, and scared at the same time. How does one step into Brian’s shoes? But at the same time, how do you pass on such a great opportunity to work on one of the coolest and most successful products at Microsoft?

You see, SSRS is not just a reporting product; it is a platform, with pieces shipping in many different Microsoft products — from Microsoft Office SharePoint to Microsoft Visual Studio to the Microsoft Business Division suite of business-line applications. In less than 6 years, Microsoft Reporting Services has grown from a small incubation reporting server to a full product suite, with information worker client tools, DBA management and configuration tools, a scalable enterprise-ready server, developer controls, and Office add-ins. It is now a mature product that stands its ground very well against the competition.

With SQL Server 2008, Reporting Services is now in its third version — but it is not just the third version of the same code. We reached a point where the Reporting Services team realized that in order to break new ground in terms of usability, performance, and scale, some core pieces of the product had to be re-architected and rebuilt. We also decided to remove a major dependency on IIS, which required even more re-design. As a result, not only the server, but most of the tools also had to be majorly revamped and redesigned. The result is a brand new product that you will discover in this very detailed and thoroughly written book.

You might ask, “Why join a product group that is now mature and well built? Some could think not much else can be done.” Really? That is never the case in software development. Reporting is an area in full swing and is evolving rapidly. The needs and expectations of users are changing, as is the role of traditional IT and its relationship with its user base. Report Builder and our SharePoint integration are a first step toward empowering more information workers to take an active role in authoring and collaborating with reports. There are so many things we can do, and so many ideas we have, to make information workers more efficient and more proficient with reports, while providing the right management and provisioning tools for IT to ensure data quality availability and consistency. The future is bright; the future is exciting; the future will be fast paced with a lot of momentum. I fully believe we are on the edge of entering the Reporting 2.0 phase . . . but that will be the topic of a future book.

For now, I leave you in the hands of Paul Turley and his team from Hitachi Consulting. Hitachi has been a very close partner to Microsoft for years and, most importantly, has always kept close ties with the Reporting Services R&D team. Since I joined the team, I don’t think a week has gone by that I haven’t interacted with Paul, Thiago, Bryan, and Ken. It has been a real pleasure and blessing to have them work on the product while it is still in development. As they were writing chapters of the book, they were testing every single feature, trying every API, playing with the tools, building samples. Not only did this allow us to have interesting conversations and gather very useful early feedback on our design, but as a result, Paul’s team is almost a virtual extension of our test team. They know the product very intimately and have developed a level of expertise that is pretty unique in the industry at this time. So, I hope you enjoy reading this book as much as I have, and just as much as the rest of the Reporting Services R&D team have enjoyed helping Paul and his team write it.

—Thierry D’hers

Group Program Manager

SQL Server Reporting Services Microsoft

Thierry D’hers is the Group Program Manager for the SQL Server Reporting Services team. His group oversees the requirement gathering, features definition and design, as well as evangelization of the product. Prior to this position, Thierry occupied various roles on the SQL Server Analysis Services team and the Microsoft PerformancePoint team. Thierry has more than 18 years of experience in the BI software industry, and has been at the source of many products at Microsoft, and prior to that, with Hyperion Solutions. When he is not building teams and designing BI software, Thierry enjoys skiing, mountain climbing, and playing the guitar.

Introduction

The book in your hands has been written and improved over the past 6 years and is now in its third edition. As consultants, solution architects, and instructors, we spend our time at companies and in front of many people who need serious solutions to meet business problems. We’ve been using SQL Server Reporting Services to build reporting, business intelligence, and decision-support solutions for large and small companies. The authors work for Hitachi Consulting, a leading business intelligence, information technology, and business services solution provider. Since the earliest release of Reporting Services for SQL Server 2000, we have developed and deployed many reporting solutions in dozens of corporate environments, including the world’s largest software company; the largest airplane manufacturer; the largest meat and poultry distributor; the leading cosmetics company; savings and investment banks; the largest investment and insurance company; the world’s most recognizable sportswear company; and the largest theme park, film, and entertainment company — to mention a few.

We’ve helped business users understand their reporting needs and then designed reporting solutions for many types of organizations. Reports were integrated into web sites and portals, intranet sites, and desktop applications. We’ve trained hundreds of users, developers, and administrators, and have presented at conferences. With this experience, we’ve learned a lot about how not to design reports and how to build reporting solutions more efficiently. This book is based on this foundation of expertise.

Who This Book Is For

There are several other books about Reporting Services. Some are for beginners and others for serious developers and advanced report designers. Leonard Nimoy’s character Mr. Spock once said, “The needs of the many outweigh the needs of the few.” While this generally may be a true statement, we’ve made it a point to address the needs of the many without sacrificing the needs of the few. We wanted to write a book that would meet the needs of the broad audience of report designers, developers, administrators, and business professionals, without sacrificing any content. To meet this objective, we’ve divided this book into six sections — “mini-books,” if you will. Depending on your needs, you may spend more of your time focusing on the material in one of these sections and using the others for reference. This book is written for the novice report designer and the expert interested in learning to use advanced functionality. For the application developer, we will cover programming in reports and custom applications that integrate reports. You will also learn about report server administration and security issues.

A common practice among development groups at Microsoft is to profile their target users and to even give these personas names and profiles. As we’ve come to know more about the types of folks who use Reporting Services in various ways, we thought it might be interesting to do something similar. The following are descriptions of three fictitious people who are characteristic of the more common Reporting Services users we have worked with. See if you can identify with any of these descriptions.

Report Designer

Mary works in the financial group for a company that provides consumer services. She is a computer-savvy worker who possesses a wide range of office skills. She has worked in this group for several years and could easily do her boss’s job. She understands her company’s business processes, financial reporting practices, invoicing, and billing systems. She’s not a computer genius, but she knows her way around word processing, spreadsheets, e-mail, and simple database reporting. Mary started using Microsoft Access a few years ago and used the wizards to create some simple reports from data exported from the HR and customer billing systems. After a while, she learned how to write queries and build Access reports without the wizards, with custom formatting, groups, and summaries. Two years ago, she learned to use Crystal Reports to report on the data in the company’s data warehouse. She has designed several reports with charts and pivots to analyze sales trends and profitability.

Mary’s focus is on out-of-the-box reporting, getting reports designed and deployed as easily as possible, using the tools readily available within the product. She may design standard server-based reports that users will access from a central report server via the corporate intranet. She may also want to create her own ad hoc, client-side reports from data models created by an administrator or more advanced designer.

The following sections of the book will be of most interest to Mary:

Part I: Getting Started

Part II: Report Design

Part IV: Enabling End-User Reporting with Report Builder 1.0

Application Developer

Joe has been writing database applications for several years. In 2004 he began using Microsoft .NET programming tools and landed a programming position in the company’s Information Technology group. Joe has designed many of the company’s web sites and portals using the Visual Basic .NET and C# programming languages. Most of the reports Joe has created were written from scratch as custom web pages. He has worked a little with a few specialized reporting applications. He wants to add reporting capabilities to some of the company’s custom business applications.

As far as Joe is concerned, writing simple reports is for others to do. His focus will likely be to add filtering, custom formatting, and conditional logic using program code and query script. He will also design his reports so that they fit right into applications as an integrated part of a solution. He may also want to create customized management utilities to automate report server maintenance routines.

Joe understands that Reporting Services offers many flexible options for integrating reports into different application interfaces. He may want to build reports into a custom Windows desktop application, web application, SharePoint Portal, or mobile device application.

Joe will be most interested in these sections:

Part II: Report Design

Part III: Business Intelligence Reporting

Part VI: Reporting Services Integration and Custom Programming

Systems Engineer

Bob is our Network Engineer and Database Administrator. He is more concerned with the security and stability of the corporate servers than with the aesthetics and features of each report. He will want to make sure that our report managers, designers, developers, and users are organized into roles and that the report server is appropriately secured. Bob will install and configure options on the report server. He will schedule maintenance tasks, optimize the database and queries, and provide ongoing maintenance and disaster recovery.

Bob will find these sections most useful:

Part I: Getting Started

Part V: Administering Reporting Services

Business Leader

As a business owner, corporate executive, or project manager, you may be the consumer of a reporting solution or the director of the development effort. Perhaps you have enlisted the services of a business intelligence consulting firm to architect a decision-support system to help you run your business. You need to be informed about your options and understand the capabilities of the products and technologies used to create your solution. This book will help you to understand these features and the choices necessary to put them into practice. The implementers of this solution will look to you for business requirements and feature choices. Chapters 1 and 2 are a good place to start. Part III, Business Intelligence Reporting, may be of particular interest. The first section of Chapter 10 discusses how to define and manage reporting business requirements and specifications. This will serve as a communication forum between you and your report designers.

What This Book Covers

This book is divided into six sections:

Part I: Getting Started

Part II: Report Design

Part III: Business Intelligence Reporting

Part IV: Enabling End-User Reporting with Report Builder 1.0

Part V: Administering Reporting Services

Part VI: Reporting Services Integration and Custom Programming

Part I: Getting Started

Chapters 1–4 provide an introduction to the capabilities and features of Reporting Services. You’ll learn about its extensible architecture, which makes it a very powerful and flexible addition to nearly all existing business systems. This section builds a foundation of understanding upon which you will learn to design, deploy, manage, and, perhaps, customize business intelligence and reporting solutions.

Chapter 1, “Introducing Reporting Services,” gets you started with a high-level view of the uses and applications for reporting solutions. Reporting Services can be used to easily deploy reports to a central report server for use with the Report Manager web interface; or be integrated into a business portal using Microsoft Office SharePoint Services. Reports can also be integrated into a simple application or a fully customized business application. You’ll learn about report user profiles and report application and solution types. The new report design tools for SQL Server 2008 will be introduced. These include the new Business Intelligence Development Studio 2008 Report Designer and the new Report Builder 2.0.

Chapter 2, “Business Intelligence Solutions,” explores reporting tool options. You will learn about the Reporting Services scalable architecture and how a reporting solution can be used in businesses of all sizes with relative ease. You will see how to plan for reporting in a small business, department and business enterprise. Information-worker users can perform ad hoc and self-service reporting using report models, the Report Builder 1.0, and the new Report Builder 2.0. This chapter discusses the components of a complete BI solution that enables Reporting Services to work with a variety of data sources, including operational data stores, a data warehouse, data marts, and OLAP cubes.

Chapter 3, “Installation,” details the process by which Reporting Services is installed in your development environment and introduces concepts critical to planning your enterprise deployment.

Chapter 4, “Reporting Services Architecture,” will help you understand the core services and technologies used by Reporting Services. This chapter addresses server and solution design. By learning how the product works and how the components interact, you will be better prepared to design and maintain a scalable solution.

Part II: Report Design

Designing reports can be as simple as running a wizard, or it can be a highly complex development process to define advanced features. In Chapters 5–8, you’ll learn about how reports actually process and render data and then how to use parameters and expressions to define creative report solutions.

Chapter 5, “Basic Report Design,” starts with the fundamentals and teaches you to create basic reports using simple design tools. You’ll learn the essentials about what you need to get started building basic reports using the Report Wizard and common Report Designer features. You’ll be introduced to the fundamental building blocks of report design: report items and report layout properties. This chapter introduces the two report design tools used to create standard reports: Report Builder 2.0, for users who want a simple, straightforward tool for designing simple reports; and the Business Intelligence Development Studio, to manage report projects and to design reports in the Microsoft Visual Studio development environment.

Chapter 6, “Report Layout and Formatting,” addresses different report design layouts and the components used to assemble a report. This chapter will introduce the report body, headers, footers, and page-formatting properties. You will learn about the capabilities of each report item and the data range components that are used to organize and present data.

After you explore the basics, you’ll learn about grouping data, lists, and data regions; using tables and the matrix reports; defining drill-through reports; and using charts. You’ll also learn to write expressions and custom code to extend formatting and apply business logic, and to design reports for mobile devices.

Chapter 7, “Designing Data Access,” reveals that reports are based on a data source and that Reporting Services may be used to present data from many different data sources. You’ll learn to define stand-alone and shared data sources, queries, and datasets and to use parameters to filter data at the database and at the Report Server. You’ll learn to use new parameter features introduced in the latest version of the product.

This chapter is a primer on T-SQL queries and stored procedures. You’ll also learn to build reports using Analysis Services and the MDX Query Builder. Query examples are provided for Oracle PL/SQL, Sybase, and Access SQL dialects.

Chapter 8, “Advanced Report Design,” helps you take design elements to the next level and learn to creatively use data groups and combinations of report items. Calculations and conditional formatting may be added by using simple programming code. Whether you are an application developer or a report designer, this chapter contains important information to help you design reports to meet your users’ requirements and raise the bar with compelling report features.

Part III: Business Intelligence Reporting

Chapter 9, “Reporting with Analysis Services,” specifically addresses the design techniques and unique best practices for reporting on OLAP cube data. You will learn to create MDX queries with parameters, calculations, and aggregations.

Chapter 10, “Report Solution Patterns and Recipes,” takes you into the real world of business problems and reporting solutions. You’ll start by learning how to document business requirements and to manage successful report projects.

This chapter presents report design from a different view — not the nuts and bolts but the overall pattern of design. We have assembled an extensive list of models and instructions to show you how to build several detailed report solutions to address a variety of specific business problems. This chapter serves as a practical guide to designing reports and building reporting solutions in the real world. It contains several examples of advanced report designs as recipes to solve specific business problems. You will apply the techniques you’ve learned in the previous three chapters to implement specific functionality.

Part IV: Enabling End-User Reporting with Report Builder 1.0

Report Builder 1.0 technology puts simple report design into the hands of everyday users without requiring complex design tools. Chapters 11 and 12 introduce the Report Builder 1.0 platform and the tools used to define data sources and semantic metadata models. Using the elements you deploy, your users can create simple reports without installing software or learning the intricacies of report design.

Chapter 11, “Report Models,” shows you that a report model is the key component behind performing ad hoc, end-user queries. A model provides the means to navigate through either a SQL Server database or an Analysis Services database. This chapter will teach you to build a Reporting Services report model using sample data.

Chapter 12, “Report Builder 1.0,” covers Report Builder 1.0, a platform for defining ad hoc reports using prepared data structures. You’ll learn to use the Report Builder 1.0 application with a familiar Microsoft Office interface for building reports. Using predefined report layouts, users can fulfill various reporting needs with ease. You’ll learn to easily format, to sort and filter data, and to perform calculations. Finally, you’ll learn how to manage and administer models and reports.

Part V: Administering Reporting Services

Report server administration has an important job: to keep data secure and available to the right users. Server-side reports can be configured and secured to optimize performance and to provide the right information to the appropriate user communities. Chapters 13 and 14 teach you how to use all of the tools necessary to configure and manage your Report Server.

Chapter 13, “Content Management,” teaches you how to use management tools and Reporting Services features to publish reports and manage execution and delivery. You’ll learn to create automated scripts and custom solutions to manage all the Report Server content. You’ll revisit the stages of report execution from an administrator’s point of view and learn how to optimize them. You’ll also learn how to automate report delivery and server management.

Chapter 14, “Report Server Administration,” is a comprehensive administrator’s guide. You’ll explore the related considerations for reporting requirements and deployment scenarios for Reporting Services. You’ll learn about the configuration tools and utilities, backup and restore procedures, and monitoring a Reporting Services instance for issues and optimal performance.

Part VI: Reporting Services Integration and Custom Programming

Practically all the built-in functionality in Reporting Services can be automated and performed through custom program code. This includes report rendering and the core services of the reporting environment: data access, rendering formats, security, and delivery.

Chapter 15, “Integrating Reports into Custom Applications,” shows you that Reporting Services is a flexible reporting tool that can be easily incorporated in different applications. In this chapter, you’ll learn to use URLs to access reports from document and web page links, use the Reporting Services Web service to programmatically render reports, and use the ReportViewer controls to embed reports into custom Windows Forms and ASP.NET Web Forms applications. You’ll learn to display reports in web portals using SharePoint web parts and other techniques. You can use programmatic rendering, URL, or the ReportViewer controls to create custom report viewers and parameter interfaces. Examples are provided in C# and VB.NET.

Chapter 16, “Integrating Reports with SharePoint,” shows you how to integrate Reporting Services with the SharePoint technologies. SharePoint has quickly become widely adopted by many business organizations and, as such, sits at the center of the information workers world. Integrating reports into this world is a natural fit, and Microsoft has made it a priority to provide a tight and seamless integration between these two products. This chapter walks you through the types of integration available and how reports can be created, hosted, surfaced, secured, and managed in the SharePoint environment.

Chapter 17, “Extending Reporting Services,” is written for serious application developers using object-oriented programming techniques, with examples in C# and VB.NET. You will learn how to create custom libraries and extensions to add functionality to the reports and Report Server features. These extensions may be used to access unique data sources, to render reports to specific formats, to authenticate users, and to deliver reports outside of the default methods provided with the product.

Appendixes

The appendixes at the end of this book include a comprehensive Reporting Services object programming reference, T-SQL syntax, commands and functions, and MDX language functions and reference.

What You Need to Use This Book

To use SQL Server Reporting Services and to run the samples presented in this book, you will need:

SQL Server 2008, any edition. An evaluation version of SQL Server and Reporting Services may be downloaded from Microsoft at www.microsoft.com/sql.

The sample databases, AdventureWorks2008 and AdventureWorksDW2008, may be downloaded from www.codeplex.com. In addition to the relational database sample, Chapter 9 uses the Adventure Works DW 2008 sample Analysis Services database.

Windows Server 2003 SP2, Windows Server 2008, Windows Vista, or Windows XP Professional SP2

Pentium III class PC with a 1 GHz processor or better (2.0 GHz recommended) and at least 1 GB of RAM

Note that for Chapter 16, SharePoint has its own requirements. In particular, SharePoint requires a server operating system (OS) such as Windows Server 2003 SP2 or Windows Server 2008. Although IIS is no longer required by Reporting Services, it is still required by SharePoint. Refer to the Microsoft SharePoint site for additional and specific recommendations around SharePoint requirements.

The complete source code for the samples is available for download from our web site at www.wrox.com. For programming examples, there are versions available in both Visual Basic .NET and C#.

Conventions

To help you get the most from the text and keep track of what’s happening, we’ve used several conventions throughout the book.

Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

Notes, tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

We highlight new terms and important words when we introduce them.

We show keyboard strokes like this: Ctrl+A.

We show filenames, URLs, and code within the text like so: persistence.properties.

We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use gray highlighting to emphasize code that's particularly important in the present context.

Source Code

As you work through the examples in this book, you may choose to either type in all the code manually or use the source code files that accompany the book. All of the source code used in this book is available for download at www.wrox.com. Once at the site, simply locate the book’s title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book’s Detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; this book’s ISBN is 978-0-470-24201-8.

Once you download the code, just decompress it with your favorite compression tool. Alternatively, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time you will be helping us provide even higher-quality information.

To find the errata page for this book, go to www.wrox.com, and locate the title using the Search box or one of the title lists. Then on the book Details page, click on the Book Errata link. On this page you can view all errata that have been submitted for this book and posted by Wrox editors. A complete book list including links to each book’s errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don’t spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml, and complete the form there to send us the error you have found. We’ll check the information and, if appropriate, post a message to the book’s Errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com, you will find several different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P, but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages that other users post. You can read messages at any time on the web. If you would like to have new messages from a particular forum e-mailed to you, click the “Subscribe to this Forum” icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

Part I

Getting Started

Chapter 1: Introducing Reporting Services

Chapter 2: Business Intelligence Solutions

Chapter 3: Reporting Services Installation

Chapter 4: Reporting Services Architecture

Chapter 1

Introducing Reporting Services

What a long, strange trip it’s been. . . . SQL Server Reporting Services is all grown up now. This product has matured quite a lot over the past five years or so since enjoying a favorable start in the industry. This is our third edition of this book, about a product in its third version. We’ve seen it grow from what was essentially a free download for SQL Server 2000, to a substantial but relatively untested component of SQL Server 2005, to a serious force in the industry — and a very capable, enterprise-ready reporting tool.

Since we started writing about Reporting Services for the first edition of this book in 2003, there is much more to say about this product and the rest of the integrated Microsoft SQL Server Business Intelligence platform. There are stories to tell about IT projects, training classes, and consulting engagements. Along the way, we’ve learned quite a lot from other members of the IT community about the many creative ways to use Reporting Services. We’ll tell some of those stories and discuss our experience with the past three generations of this product. But for now, let’s focus our attention on the fundamental applications and capabilities. In other words,

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!