Microsoft® SQL Server® Reporting Services Recipes: for Designing Expert Reports

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2010 by Paul Turley and Robert M. Bruckner

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-56311-3

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 Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://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 (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2010920661

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.

This book is dedicated to every database professional, developer, and report designer who has gone beyond the call of duty, worked ridiculous hours obsessing over that perfect report—so their business leaders can know what's going on in their organizations.

—Paul Turley and Robert M. Bruckner

Credits

Executive Editor

Robert Elliott

Project Editor

Tom Dinse

Technical Editors

Paul S. Waters

Rob Boek

Production Editor

Kathleen Wisor

Copy Editors

Christopher Jones

Maryann Steinhart

Kim Cofer

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Associate Director of Marketing

David Mayhew

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Word One, New York

Indexer

Johnna VanHoose Dinse

Cover Designer

Mike E. Trent

Cover Image

© Mark Evans

About the Authors

PAUL TURLEY, MVP, MCTS-BI, MCITP-BI, MCT, MCDBA, MCSD, MSF Practitioner

Paul is a BI Solution Architect / Manager for Hitachi Consulting and a Microsoft Most Valuable Professional (MVP). He is part of Hitachi Consulting's national Business Intelligence and Process Management team and works on occasion from Hitachi's regional office in Portland, Oregon.

He has been architecting, managing, and developing applications and business intelligence solutions for large and small businesses since 1992. He has developed custom database, BI, and reporting solutions for many companies including Microsoft, Nike, Disney, Hewlett-Packard, and Boeing. He teaches, develops training courseware, speaks at industry conferences, and has authored and co-authored several technical books. He has presented at the Professional Association for SQL Server (PASS) global summit since 2004.

He is an MSDN forum moderator for SQL Server Reporting Services and has been a Microsoft Certified Solution Developer (MCSD) since 1996 and maintains certifications in software architecture and development, database administration (MCDBA), and project management methodologies (MSF & IT Project+.) He holds the current MCTS and MCITP certifications for SQL Server BI.

Paul has authored and co-authored eleven books including Beginning Transact-SQL for SQL Server 2005 and 2008, Beginning Transact-SQL for SQL Server 2000 and 2005, Professional SQL Server 2008 Reporting Services, Professional SQL Server 2005 Reporting Services, Microsoft Press SQL Server 2005 Integration Services Step by Step, Beginning SQL Server 2005 Administration, and Professional SQL Server Reporting Services (2000).

ROBERT M. BRUCKNER, Technical Lead, Microsoft

Robert is a developer and technical lead with the Microsoft SQL Server product team. His core area of responsibility is the development of the scalable report processing engine, hosted in server and client components that ship in SQL Server Reporting Services and Visual Studio ReportViewer.

Prior to joining Microsoft and the Reporting Services development team in 2003, Robert researched, designed, and implemented database and business intelligence systems for several years as a research assistant at Vienna University of Technology, Austria, and as a system architect at T-Mobile Austria. Robert holds a master's degree and a PhD with highest distinctions in Computer Science from Vienna University of Technology, and holds several patents.

Anyone good with a search engine can find several thousands of Robert's past postings on public newsgroups and MSDN forums sharing his insights, tips, tricks, and expert advice related to Reporting Services, RDL, and other SQL Server technologies. Robert regularly speaks at industry conferences and also maintains a popular blog at: http://blogs.msdn.com/robertbruckner. In his spare time, Robert enjoys mountain biking, skiing, and playing foosball.

About the Contributors

ROB BOEK, MCITP, MCDBA, MCPD, MCSD, MSCE, MCT

Rob is a Sr. Database Developer for Smarsh, Inc. He has been a database administrator, developer, consultant, and Microsoft Certified Trainer for over 12 years. Rob has supported environments with hundreds of SQL Servers and databases as large as 35 TB.

Rob is a co-founder of the Oregon SQL Developers group. He has presented at the Professional Association for SQL Server (PASS) global summit, and is a frequent presenter at user groups, code camps, and SQL Saturday events. Rob participated in the development of the SQL Server 2008 certification exams as a Subject Matter Expert.

C. RYAN CLAY, Senior Architect, Hitachi Consulting

Ryan specializes in business intelligence, data management, portal and collaboration, and integration/interoperability solutions employing Microsoft technologies. Ryan has implemented Microsoft Business Intelligence solutions for a variety of Fortune 500 clients in the retail, construction, finance, and consumer goods industries. Ryan holds degrees in computer science, is a published author, and is active in the Microsoft community through speaking engagements and presentations at regional and national events. He lives in the Dallas area with his wife and daughter.

RISHI JARIWALA, Senior Consultant, Hitachi Consulting

Rishi has been working as a consultant specializing in Microsoft platforms and implementing enterprise-level applications for over six years. Over the last two years, he has been concentrating on the Microsoft BI Platform and has designed and implemented end-to-end BI solutions using the full Microsoft BI Stack, ranging from data warehouse to performance management. Within the Microsoft BI space, Rishi's interests are aligned with SQL Server Reporting Services, SQL Server Analysis Services, and PerformancePoint Server. He has gained several Microsoft certifications including MCDBA, MCTS in SQL Server 2005 BI, and MCTS PerformancePoint Server 2007.

DAN KISTING, Senior Consultant, Dynamics National Team of Hitachi Consulting

Dan specializes in software development, business intelligence, and ERP solution implementation featuring Field Service Automation for Microsoft Dynamics AX (FSA) and other Microsoft technologies. Dan has implemented FSA and Dynamics AX in both functional and technical positions for many Fortune 500 clients in the material testing services, food sorting machine manufacturing, and the heating and air conditioning industries. Dan holds degrees in both computer science and graphic communication, as well as a MBA in Global Management. He lives in the Portland, Oregon area with his wife, son, and two pugs, Chi-Chi Guey and Mariposa.

GRANT PAISLEY, Founder Angry Koala Pty Ltd, SQL Server MVP

Grant has over 15 years of experience in Business Intelligence and in using Reporting Services and Analysis Server since SQL 2000. Currently he designs, develops, and implements business intelligence solutions using the Microsoft BI stack with David Lean, Geoff Orr, Glyn Llewellyn, Lesley Llewellyn, Mark Fitzpatrick, Praveen Chand, Shaun Bliss, and Sue Fitzpatrick. Grant runs the Sydney SQL Server User Group (sqlserver.org.au). He also founded the reporting community (reportsurfer.com), and is a regular speaker at SQL Code Camp, Usergroups, and TechEd Australia. Any spare time he has he spends mountain biking or kitesurfing, and he loves Hawaiian stopovers.

EF ROMERO, Senior Consultant, Hitachi Consulting

Ef is a Senior Consultant at Hitachi Consulting where he specializes in business intelligence development with SQL Server. His work with SQL Server is concentrated in ETL, data warehouse development, database optimization, and reporting. He received his bachelor's and master's degrees, both in pure mathematics, from UTEP, and has worked as a mathematician and BI developer for the past four years.

JOE SALVATORE, Business Intelligence Architect, MicroLink, LLC.

Joe has been a database architect, business intelligence and database developer for more than 12 years, specializing in data access architecture, ETL, data quality profiling, and business intelligence development. Serious about successful business intelligence solutions that incorporate best practices, he has attended many of Ralph Kimball's classes and regularly attended professional conferences such as the Microsoft Business Intelligence Conference and Professional Association for SQL Server (PASS) Summits. Joe also is an accomplished SQL Server author who has contributed to Wrox's Professional SQL Server 2005 Administration, content relicensed for Professional SQL Server 2008 Administration, and he has contributed articles for Information Management and SQL Server Standard magazines. Joe is also a Microsoft Certified IT Professional - Microsoft SQL Server 2005 and 2008 Business Intelligence Developer as well as a Microsoft Certified Technical Specialist - Microsoft SQL Server 2005 and 2008 Business Intelligence Development.

THIAGO SILVA, MCAD, MCTS (ASP.NET), MCPD-Web Developer, Software Architect and Manager of Specialized Services, Hitachi Consulting

Thiago has been developing custom .NET and Reporting Services solutions since the early days of .NET and SQL Server 2000. He is a part of the Microsoft practice within Hitachi Consulting, where he helps create, manage, and deliver solutions for clients around the Microsoft technology stack. Thiago is co-author of Professional SQL Server 2008 Reporting Services, and has been featured on the tech podcast .NET Rocks. He is an active member of the .NET development community, where he presents at local user groups. He writes tech articles for his blog “Silvaware,” found at http://silvaware.blogspot.com. Thiago holds a bachelor's degree in Information and Operations Management from Texas A&M University. He is also a Microsoft Certified Professional with the following titles: MCAD, MCTS (ASP.NET), and MCPD Web Developer.

PAUL S. WATERS, Business Intelligence Architect, AMECO (www.paulswaters.com )

During Paul's 16 years of working in IT, he has held a variety of positions including help desk manager, application developer, network administrator, and Director of IT. For the past several years, he has been focused on business intelligence and data warehouse design and development. Currently he designs, develops, and implements business intelligence solutions using the Microsoft BI stack with his two co-workers Jason S. Burton and Todd Sibley.

Paul is the Founder and President of the SQL Server Innovators Guild (www.ssig.org), a founding board member of the GSA Technology Council (www.gsatc.org), a member of Greenville Tech's Program Advisory Council, and was Co-Chairman of the South Carolina Code Camp 2.0 (www.sccodecamp.com). He is also a presenter at local user groups and conferences.

Acknowledgments

I thank my Leaders at Hitachi Consulting for their support and tolerance during this long project. I would especially recognize Hilary Feier, Patrick Bolin, and John Lauer for allowing this book to materialize. My thanks go to Thierry D'Hers and the entire Reporting Services product team who have provided direct support and have been responsive to my questions and feedback; to Sean Boon, Bob Meyers, Lukasz Pawlowski, Chris Baldwin, Chris Hays and Carolyn Chau for building an incredible product and helping us take it up a notch or two.

Thanks to my family. To my wonderful wife, Sherri, who is the toughest, most selfless, and most caring woman on the planet. To my daughters Rachael, Sara, and Krista for supporting their dad. To my son Josh for doing the right thing to serve his country, and to Angel for being there with him. Thank you especially to Sara for being strong and teaching us about love.

Thank you to my Lord and Savior, for providing for my family and for challenges to make us stronger and better people.

—Paul Turley

I would like to thank in particular Paul Turley and Bob Elliott for initially envisioning and approaching me about this project, and the great collaboration throughout. Furthermore, I would like to express a big “thank you” to all co-authors contributing to this book, and the invaluable editorial work of Tom Dinse, Rob Boek, and Paul Walters to ensure accuracy.

—Robert M. Bruckner

Thanks to the many people who contributed to this material—far too many to name here, but you know who you are. And a very special thanks to my wonderful, beautiful wife, Donna. She is the one who truly inspires me in every aspect of our lives.

—C. Ryan Clay

In addition to the constant support from my wife, I would really like to thank Paul Turley for this great opportunity. I would also like to thank Bob Elliott, Tom Dinse, Paul S. Waters, and Rob Boek for reviewing and editing our work and, last but not least, I would like to thank my co-authors for contributing to this special book.

—Rishi Jariwala

Thanks to the different people who contributed to this effort, including the technical editors, Paul T., Paul W., and Tom D. on the team, Steve Svoboda for giving me the opportunity to learn FSA and AX, and Angela Garcia and Erin Gray for reviewing my material. Most importantly, however, thank you to my incredibly beautiful (and patient) wife Rosa “Cuca” and my equally beautiful son Alejandro for the patience, support, and for doing everything they do on a daily basis to make my life complete.

—Dan Kisting

I wish to thank my wife Sue (who put up with the 3am Skype calls) and our three kids Megan, Lisa, and Zoe for their patience while writing these recipes (“Dad, you're writing recipes? You don't cook!”).

—Grant Paisley

An inestimable measure of love and gratitude is deserved of both my beautiful wife Dawn and my precious daughter Phoebe. Their love, support, and patience were vital not only throughout this project but in every day of my life. I would also like to thank all co-authors for their inspiration during this endeavor, especially Paul Turley for providing the opportunity to work on this project.

Additionally, I would like to express my thanks to our editors Tom Dinse, Rob Boek, and Paul Walters whose hard work provided for content of the highest fidelity.

—Ef Romero

I wish to Recognize the love and support of my wife, Linda, and my two boys, Andrew and Matthew, for their inspiration and motivation behind great endeavors such as professional writing. I am always grateful for the examples and encouragement supplied by both my parents and my extended family. Thanks especially to the top-notch efforts of all the managers, editors, and reviewers without whom our efforts would be less than the stellar results that have been achieved.

—Joe Salvatore

Thanks to my beautiful, loving wife Michelle who supports me and inspires me to be the man that I am, and to my kids, Gabriella and baby boy Silva (who will be joining us soon after we complete this book). They are the most precious gifts from God.

I would like to thank the Lord God for endurance, provision, and inspiration during the writing of these recipes, and throughout the long nights and weekends.

I also acknowledge:

My colleagues and peers at Hitachi Consulting, for challenging and encouraging me throughout this adventure;

The folks from the Reporting Services team at Microsoft, who have been helpful in answering our questions and getting us to the bottom line of the features discussed in this book;

My co-authors for assisting with ideas and insightful discussions (and for all their collaboration);

Bob Elliott, Tom Dinse, Paul Waters, and Rob Boek, the editors and reviewers of this book, for their input and guidance, as well as patience and fantastic job of testing and keeping this book consistent and legible.

—Thiago Silva

I would like to thank my beautiful wife Sarah, and my children, Megan, Laura, Michaela, and Trevor for their love, patience, and support while writing this book.

Thanks to Paul Turley and Robert Bruckner for the opportunity to participate in this project, and to Tom Dinse and Bob Elliott for their guidance through the process.

—Rob Boek

I wish to thank my beautiful wife Jennifer and our two wonderful children Emma and Ian for their love, support, patience, and understanding while working on this book.

—Paul S. Waters

Foreword

When we started work on the Reporting Services 2008 R2 release back in summer 2008 (at the time it was codenamed Kilimanjaro), we set out to focus on end users: on enabling business users and traditional report consumers to serve their own needs by authoring their own reports. Given our goals, I was curious about the type of books that would be authored about this release. I thought the only thing that would be relevant and appropriate would be user- and solution-oriented books. When Paul first talked to me about his project, I was intrigued with the recipe-style chapters idea. It departed from the traditional Administrator-oriented books that attempt to cover all aspects and components of the product. The reason for my interest was not just that it was a newer, fresher approach to authoring a book on our product, it also reflected the spirit and the theme of Reporting Services 2008 R2: self-service and end-user oriented.

Later, while talking with Robert Bruckner, a senior developer in the Reporting Services processing and rendering engine team, I discovered that he also was involved with this project. I was feeling good: that was a solid project well under way. A couple of months later, at TechEd US, I am in a meeting with Brian Larson who is in the middle of a book signing session. Brian is well known for having authored among the most popular Reporting Services best-sellers. As we are discussing various things, we come to talk about book projects for R2, and to my great surprise, I learn that he, too, has contributed material to this recipe book project. I am exultant. Later that day, I am due to have lunch with Grant Paisley to talk about one of his rather unique reports, known, among other names, as the “Australian Sparkline” (If you are curious about it, go to the Cube browser report recipe). It turns out that at the table with Grant and me are Paul and a couple other SSRS MVPs. Inevitably at some point the conversation turns to books, and Grant drop the news that he, too, is on this project. Needless to say, for a second I believe he is teasing me, but he is not. But this is not the end of it; Thiago, Joe, and about 10 other MVPs and book authors contributed to this book. This is unprecedented. Never before have we seen such a concentration of knowledge and experience coming together to author what will probably be one of the most memorable books ever written on Reporting Services.

It is probably not the kind of book you will read front to back, but it is likely to be the one you keep on your desk at all times, close at hand and ready to open at the right time—when your business needs will require a specific report design. You are likely to find a chapter right on topic in this book. You might ask, “Thierry, how can you be so confident that this book will give me just what I need just when I need it?”

Well, the answer is that not only are the authors of this book all practitioners, they are also some of the most actives posters on the Reporting Services forums and some of the most active participants in other Microsoft events, and as such they have a deep knowledge about the most commonly discussed topics and questions asked by users. They are experts whose experience I deeply admire and whose judgment I fully trust. They are considered by the product team as a panel of experts whose advice our team seeks when we need opinions about product direction or validation of product feature prioritization. For us, the product group, they represent the voice of the customers. With you, they become an extension of the product team, showing you some of the best ways to solve real business problems and take full advantage of Reporting Services.

You are in great hands; I hope you will enjoy the book as much as I enjoyed working with its authors.

—Thierry D'Hers

Group Program Manager

Microsoft SQL Server Reporting Services team

Preface

With rapidly increasing data volumes in every organization, the real challenge today is presenting data efficiently in a format that is easy to digest. The Reporting Services platform provides a lot of flexibility and an extensible architecture, which can help you adapt and extend the out-of-the-box experience if needed and implement solutions that fit your specific needs.

If you are looking for examples of interactive reports that you probably never imagined you could accomplish with Reporting Services reports, I recommend you attend the BI Power Hour session at big conferences such as Tech Ed, BI Conference, or SQL PASS. The BI Power Hour session is about demonstrating, in perhaps unusual and fun ways, the power of business intelligence tools. For the most recent conference seasons I built demos for the Reporting Services part of the BI Power Hour that included a multi-player Sea Battle report game (included in this book), and an artificial intelligence Sales Strategy/Risk Assessment reporting game, which will be published to my blog soon.

Some people say I know a lot about available Reporting Services report features. Probably because I implemented many of those, I'm using Reporting Services every day, and I have been answering questions and sharing my insights and advice for many years in various Microsoft internal discussion groups, private early adopter newsgroups, public Reporting Services newsgroups, as well as MSDN forums. Anyone good with a search engine can find several thousands of my postings.

Nonetheless, what continues to fascinate me since the early days of Reporting Services 2000 is seeing the amazing things that people are doing with the Reporting Services product leveraging its flexibility and extensibility, and pushing it in ways that we in the product team may not have thought of originally. I also frequently observe that people realize they could accomplish solutions with Reporting Services that they initially didn't think were feasible, and become curious about what else is possible. I hope the report design recipes in this book will provide you with answers and new ideas.

This reminds me of a related conversation I had with Paul Turley sometime during the fall of 2009. At the time, Paul was preparing a new conference presentation with Reporting Services contents. We had been exchanging frequent emails, in which I explained how to accomplish particular report designs in the best way. At some point in that conversation, Paul pointed out that several aspects of a proposed report design are rather complex to accomplish in Reporting Services 2005. I responded by saying that this is absolutely true and that we in the product team specifically monitor that kind of customer feedback. We consequently decided to introduce the “tablix” in Reporting Services 2008, which simplified that scenario tremendously. Furthermore, I mentioned in that same conversation that I had just finished the implementation for one of the new features in Reporting Services 2008 R2 that enables renderer-dependent layout decisions in reports (another frequent customer request). Paul humorously responded that this was “not being fair,” because there are many consultants out there who make a good living from extending the out-of-the-box experience of Reporting Services. So the next thing the product team does is to close a bunch of these gaps, making it really simple to accomplish those scenarios easily in the next release.

Well, this book is truly special! It draws from the experience of many Reporting Services industry experts and brings together best practices, report design patterns. The recipes provide inspiration on what is possible today, and provide clear steps on how to implement them successfully. Each recipe calls out the versions of Reporting Services it applies to, and as a bonus we included several recipes that specifically target new features enabled in the upcoming Reporting Services 2008 R2 release. Examples include naming worksheets from within the report on Excel export, dynamic page breaks, resetting the page number based on groups, rendering format dependent layout decisions.

Good luck and happy reporting!

—Robert M. Bruckner

Redmond, WA

Introduction

As a reporting services user and report designer, you and I have something in common. We both appreciate the power and simplicity of SQL Server Reporting Services. You may have used SSRS to design common table and matrix reports. Perhaps you have designed reports that use parameters to let your users filter data and you want to help them gain more insight into their business. Like an artist, you've discovered some interesting techniques for using this tool to solve business problems in creative ways, and now you'd like to take the next step and create compelling and dynamic reporting solutions so your users can get more meaningful value from their data. Perhaps you'd like to learn about techniques developed by others who also appreciate this exciting tool.

We've brought together a team of creative and experienced report designers to showcase their recipes for unique report designs. Most of these designs have been tested and used in large organizations with real business data, and for consulting clients who have unique problems to solve and high expectations. Every one of us is passionate about technology, data, and SQL Server Reporting Services, which is why we created this unique book.

This is not a beginning-level book to teach the essentials of basic report design, but it's not exclusively an advanced-level book either. If you understand the basics of report design and have a cursory knowledge of Reporting Services, you should be able to use the recipes in this book to learn more advanced techniques and build useful business reports.

Who This Book Is For

Before using this book to design reports with these recipes, you should know how to do the following:

Use Report Builder 2.0 or 3.0 to create a simple report.

Use Business Intelligence Development Studio or Visual Studio to create a report project.

Design a data source and connect to a SQL Server database.

Create a dataset and use the query builder to design a simple database query.

Add a data region to a report. This may include designing a simple table or matrix.

Add a group to a table or matrix.

Create report parameters.

If you can master these essentials on your own, we'll show you how to apply these basic skills to create useful business reports. With these skills, you'll learn to apply more advanced design techniques to take your report designs to the next level.

If you are familiar with Report Builder 2.0, 3.0, or Business Intelligence Development Studio report designer, you'll find that the differences between these report design tools are subtle, and you should be able to easily make the necessary adjustments to follow a recipe while using a report designer other than the one the recipe was written for.

We don't provide beginning-level, step-by-step instructions for every task; rather, we summarize the essentials and focus on design techniques. For example, in nearly every recipe you will be instructed to create a data source for a particular database, add a dataset, add parameters, and design a simple report data region. You should be able to create a dataset and build a query. The recipe author will provide the query script for you to type or enter into the query design window.

What This Book Covers

This is a book of report recipes—instructions for designing specific report styles and solutions. It will help an experienced Reporting Services report designer create specific types of reports for solving unique business problems. The complexity and difficulty of these recipes ranges from 200 to 400 level. If you possess basic skills, as you work through these recipes, you may learn to improve your report design skills and advance to more complex recipes. However, to get started, you will need a working knowledge of fundamental report design using SQL Server Reporting Services.

You will find chapters and report recipes organized into categorized sections. The introductory chapters in this book are comprised, in part, from previous writings of the same authors, in order to be a comprehensive reference guide for experienced report authors. They are designed to get you started in a high-level overview of the Reporting Services architecture, design principles, deployment strategies, and basic report design techniques. This overview is provided as a refresher course and a quick reference guide for experienced Reporting Services report design users.

If you need help getting started with report design, before you use this book, we recommend that you first read Professional SQL Server 2008 Reporting Services (Wrox, 2008).

How This Book Is Structured

The book is organized in two main sections. We begin with three chapters that introduce the SSRS product, architecture, and design tools. These chapters cover report design essentials at a high-level to help fill-in gaps for moderately skilled report authors. These chapters also provide a reference for the essential techniques required to design and build basic reports. These techniques are the building blocks for most of the recipes in the sections that follow.

The bulk of the materials in this book are several short report recipes, grouped by category. Each recipe begins with a brief description of its purpose and provides a business case for its use and the problems that it solves. The versions of Reporting Services for which the design techniques apply are listed, followed by a list of skills or other resources you will need to design the recipe report. This is like a list of ingredients in a cookbook.

This is followed by an illustration of the finished report, followed in turn by a set of step-by-step instructions, fully-illustrated, that will lead you through the process to create the recipe report. In most cases, the author will instruct the reader to use well-known techniques, rather than the detailed steps, to perform common design tasks—tasks like creating a data source, adding a dataset, adding a parameter, or drawing a table. The recipes are written in plain language to address the skill level of a moderately experienced SSRS report design user. At the end of each recipe, you will see and experience a functioning example of the completed recipe design.

In most cases, the author concludes by discussing similar applications for the technique and different ways that the report may be adapted to address different reporting needs.

What You Need to Use This Book

To use the techniques in the book, you will need a report design and report hosting environment running on SQL Server Developer, Standard or Enterprise Edition. The majority of the examples and instructions are written for SSRS 2008 using Report Builder 2.0. All of these recipes may also be applied using SQL Server 2008 R2 with Report Builder 3.0. These are the tools we recommend that you use. Some of the recipes showcase new features introduced in SSRS 2008 R2 and will only work with that version, forward. Each recipe indicates the version(s) of Reporting Services that it supports and the database you will need.

If you are using an older version of Reporting Services, several of the recipes will also work with SSRS 2000 and 2005, as indicated. Some include alternate instructions for the earlier report designers. Some, but not all, of the techniques written for the newer report designers may be adapted to earlier versions, but in order to do so, you will need to be familiar with the differences and make these adaptations yourself.

The databases used in these recipes may be downloaded from the book companion site at www.wrox.com. These databases include AdventureWorks2008, AdventureWorksDW2008, Northwind, and the Adventure Works DW database for Analysis Services. For those recipes that support earlier SSRS versions, you may also use the AdventureWorks and Adventure Works DW databases for SQL Server 2005, which may be obtained from Microsoft at www.codeplex.com. Any of the recipes that use specialized databases, other than the standard samples, will include these in the download files provided for that recipe.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of 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 file names, URLs, and code within the text like so: persistence.properties.

We present code in a special monofont typeface:

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

Source Code

As you work through the examples in this book, the necessary expressions, function code, and query scripts are provided for you. You can either type in all the code manually, or use the completed report files that accompany the book. All of the finished reports and source code used in this book are available for download at http://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 sample files 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-56311-3.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at http://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 http://www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has 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 a number of 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 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.

Chapter 1

Business Reporting Paradigms

In the world of business, we use a lot of different styles and types of reports. To appreciate how pervasive reports are in the world of business, in different organizations, and in our day-to-day lives, we must first define what a report is. Any formatted output of data from a database or any other type of data source could be called a report. Some types are obvious and may include sales reports, end-of-period summaries, trend analysis, and comparisons. These are some traditional report styles. Reports are used in all areas of business and practically every business function involves printing, displaying, browsing, or using some other method to present data to business leaders, workers, service providers, customers, inspectors, analysts, and others for a variety of reasons. Reports, in many different forms, are everywhere.

Less traditional report types that may be used in different business scenarios include things like product labels, name badges, routing tags, invoices, claim forms, request forms, government documents, and shipping manifests. You probably have types of reports that are unique to your specific business or industry that outsiders aren't even aware of. To provide a better understanding about the various functions that reports perform, this chapter explores different types of reports. Chapters 2 and 3 will review the basic building blocks of report design and development that you will need to know to apply the report design recipes in this book. We expect that you already have some hands-on experience with SQL Server Reporting Services but we will review the basics as a quick refresher.

For the purpose of simplicity, we will group all of these report types into general categories. You will no doubt be able to identify some of your own unique reporting requirements but they generally fall into one of the following major categories:

Process and operational support

Business intelligence and analytical reports

Application integration

Forms, labels, and letters

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!