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 publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2016954844
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, 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. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.
This book is dedicated to my wonderful wife, Sherri. You are beautiful in so many ways and I love you. Thank you for managing the household and the business, and helping me preserve a thread of sanity while writing this book. You raised four great kids who are now adults; and one overgrown kid who refuses to grow up. Thank you for your word-smithing skills, managing figure files, and correcting my run-on sentences.
ABOUT THE AUTHOR
PAUL TURLEY is Principal Consultant for Intelligent Business, a Mentor with SolidQ, and a Microsoft Data Platform MVP. He consults, writes, speaks, teaches, and blogs about business intelligence and reporting solutions. He works with many organizations to model data, and visualize and deliver critical information to make informed business decisions using the Microsoft data platform and business analytics tools. He is a Director of the Oregon SQL PASS chapter and user group, and the author and lead author of 15 publications. He holds several certifications including MCSE for the Data Platform and BI. He posts and can be contacted through his blog at SqlServerBiBlog.com.
ABOUT THE TECHNICAL EDITOR
NIGEL PETER SAMMY is a Microsoft Data Platform Most Valuable Professional (MVP) with over 15 years of technical experience including 12 years of database and SQL Server experience. He currently works at SoftwareONE as a Senior Data Platform Engineer where his responsibilities include consulting, solution design and implementation, hands-on training, and pre-sales. Besides working at SoftwareONE, he is also a Lecturer at the School of Business and Computer Science (SBCS) where he teaches the Microsoft Certified Solutions Associate (MCSA): SQL Server certification as well as other BSc courses for the University of London and University of Greenwich.
Nigel previously worked at Microsoft as an Account Technology Strategist (ATS) where he was responsible for providing pre-sales technical/architectural support for over 200 agencies in both the commercial and public sectors. As an ATS he delivered technical presentations and proofs of concept using SQL Server, Azure, Power BI, and Office 365. Nigel was also a Data Platform Architect (DPA) at SolidQ, a global provider of advanced consulting, mentoring, and education solutions for the Microsoft Data, Business Intelligence, Collaboration, and Development platforms. Microsoft and SolidQ have given him at least five years' working experience with large international companies.
He has progressed through other roles in his career including Application Developer, Analyst, Database Administrator, Database Developer, Project Manager, Architect, Team Lead, and Manager. In 2010, Nigel co-founded the Trinidad and Tobago SQL Server User Group (TTSSUG), a volunteer, independent, non-profit organization providing a community for Microsoft SQL Server professionals, hobbyists, and enthusiasts. Nigel is a co-author of Microsoft's SQL Server 2012 Upgrade Technical Guide and a technical editor of Wrox's Professional Microsoft SQL Server 2012 Reporting Services book. For the last eight years he has been presenting on data platform topics for conferences locally and internationally. When he gets extra time, he blogs at www.nigelpsammy.com.
My endless appreciation and gratitude goes to the co-authors and contributors of the four previous editions of this book series over the past 13 years. As technical reviewer, Nigel Sammy worked tirelessly to test and research the 2016 product as it was readied for release, and to make sure we were current, complete, and accurate. This product continues to be a moving target, and Nigel went far above and beyond any reasonable expectation.
I appreciate my 2012 edition co-authors who helped refresh and update material for the new product version. Thanks go to Grant Paisley, Thiago Silva, and Robert Bruckner for your revisions and direction. Tom Dinse, thank you for your patience and persistence through this hardscrabble effort. Riccardo Muti and Chris Finlan from the product team, thanks for sewing the monster together and once again giving him life. Seriously, thanks for the direct product team access and on-going support for this set of marvelous tools.
FOREWORD
Riccardo Muti
Group Program Manager, SQL Server Reporting Services Microsoft
On a mild Seattle day in 2010, I headed to the Microsoft campus to start my new job working on the SQL Server Reporting Services product. Once there, I learned that my computer would take a few more days to arrive. What was I supposed to do for a few days without a computer? My manager handed me a book and said, “Read this.” That book was Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports by Paul Turley and Robert Bruckner. For those few days, I could do nothing but get to know my teammates and pore over that book. By the time my laptop arrived, I was primed to put everything I'd studied into practice. It's no exaggeration, then, to say that I've been learning Reporting Services from Paul's books since my first day on the Reporting Services team at Microsoft.
Long after I'd received my laptop, I kept that book on my desk, referring to it often as I worked to deepen my report design expertise. Whether I was trying to figure out the best way to design a multi-lingual report or to pass a multi-value parameter into a stored procedure, one of the many recipes in that book held the answer. Many people have turned investments in learning Reporting Services into rewarding careers, in no small part with the help of Paul's many books on the topic, which expertly guide readers on a journey from foundational knowledge to the most advanced techniques. Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports carries on that tradition. A must-read for novices and experts alike, it covers everything from setting up a report server to designing sophisticated reports to crafting enterprise solutions to optimizing for today's mobile devices.
Last year, after a few years working on other parts of Microsoft's Business Intelligence offering, I had the opportunity to rejoin the Reporting Services team and revitalize the product that hadn't been updated in a few years. A year of a team's hard work later, SQL Server 2016 Reporting Services, the product of an ambitious overhaul, offers a modern enterprise reporting platform, including a nifty mobile dashboard solution. What has powered the widespread adoption of Reporting Services goes beyond the product itself to the community that has flourished around it, thanks in large part to leaders such as Paul. In reading this book, you're participating in that community and learning from a master. If you'd asked me where to start learning Reporting Services, I would've handed you none other than this book. Enjoy!
FOREWORD
Christopher Finlan
Senior Program Manager SQL Server Reporting Services
“So, is Reporting Services dead?”
That question, and my non-answer, was usually the extent of my conversations with my Microsoft customers when it came to SQL Server Reporting Services. You see, prior to joining the product team, I was part of the pre-sales team at Microsoft. My entire job was to get customers excited about the latest tools and products we had in the Microsoft Business Intelligence Suite. This meant we were generally avoiding the topic of SQL Server Reporting Services, which looked dated and hadn't seen much added to it in the last few years. Instead, I was either talking about Power BI or about a little-known Microsoft partner called Datazen, which had these amazing mobile dashboards that looked beautiful on every device you consumed them on.
Fast forward to today, and much has changed in these last 12–18 months. Nowadays, customers can't get enough information about what's new in SQL Server Reporting Services. In fact, it's often the first thing customers want to talk about when it comes to Microsoft Business Intelligence. The SQL Server 2016 release saw the product transformed, bringing a modern look and feel along with all the mobile capabilities previously available in the standalone Datazen product. Combine this with the planned improvements already communicated on the Microsoft BI roadmap, and it's an incredibly exciting time to be a part of the Reporting Services product group. Just seeing our customer's excitement at what's been delivered and what's still to come is incredibly gratifying to all of us.
But this same excitement also brings with it an entirely new set of challenges—many of you reading this book may never have touched Reporting Services before for the reasons mentioned earlier. Now that you've seen what you have available as part of your SQL Server investment, you're looking to unlock it and don't know where to start. Or perhaps you've used SSRS in the past, but want to dive into creating mobile reports and KPIs for the first time. Regardless of why you are here now, be glad you are. There are few people better equipped to navigate you through what you can accomplish with Reporting Services than the author of this book, Paul Turley.
For over a decade, Paul's books on SQL Server Reporting Services have become “the SSRS bible” for thousands of SQL Server report developers every day. His writing style makes it easy for anyone to understand the wide-ranging topics he covers, from report design to server administration. There's even a copy of one of his books in our team room. I know this because I've picked it up and used it on more than one occasion. And I'm looking forward to adding this version to our collection, as you all have already.
Thanks to Paul and all of you for being a part of the Reporting Services community, and enjoy the book!