INTRODUCTION

FOURTEEN YEARS! I had to say that out loud just to make sure it was right. yes; fourteen years. That is how long it has been since I began using Reporting Services to create reports and reporting solutions.

Consulting clients, conference attendees, and students often ask which of all the BI or reporting tools they should use for their business reporting needs. I have used several other Microsoft products including SQL Server, Analysis Services, Integration Services, SharePoint, Access, Excel, and Power BI, but Reporting Services is the tool I keep coming back to because it does so much.

My peers and I have been tracking this product through every version since it was released in 2003; since that time, we have produced six Wrox Press books on Reporting Services. I have worked closely with the Microsoft product team leadership, and the product developers who continue to innovate and move it forward. I have learned to use SSRS correctly, and, on occasion, incorrectly; benefiting from some tough lessons about what it can and can't do along the way. My goal is to share this experience with you, in addition to the best practices we have developed over the years.

WHO THIS BOOK IS FOR

This book is written to meet the needs of a broad audience, and includes specific solutions for report designers, developers, administrators, and business professionals. My goal for this book is to be a comprehensive guide and valuable reference. It is written for the novice report designer as well as the expert interested in learning to use advanced functionality.

WHAT THIS BOOK COVERS

This book is divided into seven parts.

Part I: Getting Started

This part covers what Reporting Services is and how it is used. The three chapters in this part of the book will help you understand the capabilities of Reporting Services and the reporting platform. You will get to know the server platform and the report design tools used to create KPIs, paginated reports, and mobile reports. You will learn what's new in SQL Server 2016 Reporting Services.

Chapter 1 covers Reporting Services use cases, using and creating dashboards, creating reports, and building integrated applications. We discuss how to choose the right reporting tool based on the business need, as well as optimizing report performance.

Chapter 2 is all about what is new in SSRS 2016. You learn about report designer enhancements, modern browser rendering, and parameter layout management. We introduce mobile reports and KPIs, new printing and rendering options, the new report web portal, and Power BI dashboard pinning and integration.

In Chapter 3 you learn about how to install Reporting Services and understand the server architecture. We discuss what has changed in the SQL Server 2016 architecture, and how to install and set up a report server. You will understand how to build an enterprise report server deployment, and how to use tools to manage the reporting lifecycle and leverage Reporting Services extensions.

Part II: Basic Report Design

These chapters include hands-on exercises that step through the process of building reports, queries, and the solutions that are discussed in each chapter. Finished copies of all the reports and exercises are provided for your reference. These chapters lead you through the building blocks that are fundamental to all report designs. You learn the mechanics behind data regions, groups, report items, page breaks, tables, matrices, and charts.

Chapter 4 covers report layout and formatting. You learn to use datasets, data regions, and other report data building blocks. You'll design report layouts using tables and matrices, and set grouping and formatting properties using expressions.

Chapter 5 teaches database query essentials. You learn to understand relational database principles and concepts and data source management, and you build simple and complex datasets using the query design tools. We will perform query authoring using the Report Builder query designer, SSDT report designer, and SQL Server Management Studio. You will become proficient using single- and multi-select parameters in queries.

Chapter 6 introduces SQL Server Data Tools for Visual Studio. You build more advanced reports in your chapter exercises using the graphical query designer and hand-written queries with parameters and complex query logic. You will understand query groups in table joins and the report dataflow, and understand report groups and expressions used for complex grouping, sorting, and visibility.

Part III: Advanced and Analytic Reporting

These chapters deal with advanced and more complex reporting scenarios. You build on your grouping and expression skills, incorporating more advanced queries with parameters, expressions, and programming logic.

Chapter 7 is about advanced report design. You will manage pagination and report page headers and footers. You will use conditional logic for text formatting and layout properties, embedded HTML text and styling, master/detail reports, subreports, and document maps.

In Chapter 8, you learn about graphical report design principles and standards. We review both standard and advanced chart types and design approaches, and we dive deep into more complex charting features, creating multi-series and multiple area charts. Also, you learn to use KPI indicators, sparklines, and data bars.

Chapter 9 is all about advanced queries and parameters. You learn more about T-SQL queries and parameters, and MDX queries and parameters.

In Chapter 10 you use SQL Server Analysis Services as a data source for reports working with Multidimensional Expressions (MDX). You learn to build queries with the MDX query designer and learn to handwrite MDX with parameters.

Chapter 11 is a complex example of a reporting solution that leverages the power of the MDX language and Analysis Services. In this cube browser solution, we use reports to enumerate and prompt the user for parameter selections and then dynamically navigate an entire cube structure. This example showcases some very useful, complex report navigation and design techniques.

In Chapter 12 you learn about interactions and report navigation. We revisit the expressions used to implement conditional logic. You learn to use common functions such as IIF and SWITCH in decision-based expressions and custom code, and you learn reporting techniques using recursive relationships and actions to navigate between reports.

Part IV: Solution Patterns

If you use Visual Studio with integrated version control and work with teams to build solutions, this part of the book is for you. You learn to manage report projects alongside other report and solution developers using formal project methodologies.

Chapter 13 is about report projects and report consolidation. You learn to apply SSDT solution patterns, understand how to work with report specifications and requirements, and work within project development phases. You will create report templates and manage reports within projects and solutions. You will also learn to plan self-service reporting solutions and how to support nontechnical report designers who use Report Builder to create their own reports within a managed environment.

In Chapter 14 you learn about report solutions, patterns, and recipes. You will combine multiple report components into super reports and business dashboards. You will design a KPI scorecard, an interactive sparkline report with a drill-through chart, and a map report with drill-through navigation.

Part V: Reporting Services Custom Programming

In this part, you learn how to integrate Reporting Services into custom applications and to use reports outside of the web portal environment using URL access and web service calls.

Chapter 15 is about integrating reports into custom applications. You'll use URL access and web services to render reports, build a custom Windows form or web form application to enter parameters, and render reports in your own custom interface. You will see how to create a custom-made input interface for Reporting Services reports.

In Chapter 16 you learn to extend Reporting Services and leverage extensibility options. We begin by discussing the reasons for extending SQL Server Reporting Services and creating custom extensions. Often, these options are complex and specific to the business needs outside of standard reporting scenarios. You will learn how each type of Reporting Services extension can be used to provide custom rendering, security, data access, and delivery of reports.

Part VI: Mobile Report Solutions

This section of the book introduces new mobile reporting capabilities introduced in SQL Server 2016. You learn to use Mobile Report Publisher and the new mobile reporting platform to deliver reports specifically designed for tablets, smart phones, and other mobile devices. We start with basic mobile report design approaches and techniques. You will learn to use each of the visual controls, navigators and selectors, report navigation, and styling options.

Chapter 17 introduces Reporting Services Mobile Reports. You'll learn to use the Mobile Report Publisher to consume shared datasets and deliver interactive information for a mobile device. You'll also learn the basic building blocks and how the components within each...