Librería Portfolio

Optimize reporting and BI with Microsoft SQL Server 2016
Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports provides a comprehensive lesson in business intelligence (BI), operational reporting and Reporting Services architecture using a clear, concise tutorial approach. You´ll learn effective report solution design based upon many years of experience with successful report solutions. Improve your own reports with advanced, best-practice design, usability, query design, and filtering techniques. Expert guidance provides insight into common report types and explains where each could be made more efficient, while providing step-by step instruction on Microsoft SQL Server 2016. All changes to the 2016 release are covered in detail, including improvements to the Visual Studio Report Designer (SQL Server Data Tools) and Report Builder, Mobile Dashboard Designer, the new Report Portal Interface, HTML-5 Rendering, Power BI integration, Custom Parameters Pane, and more.

The Microsoft SQL Server 2016 release will include significant changes. New functionality, new capabilities, re-tooled processes, and changing support require a considerable update to existing knowledge. Whether you´re starting from scratch or simply upgrading, this book is an essential guide to report design and business intelligence solutions.

Understand BI fundamentals and Reporting Services architecture
Learn the ingredients to a successful report design
Get up to speed on Microsoft SQL Server 2016
Grasp the purpose behind common designs to optimize your reporting
Microsoft SQL Server Reporting Services makes reporting faster, easier, and more powerful than ever in web, desktop and portal solutions. Compatibility with an extensive variety of data sources makes it a go-to solution for organizations across the globe. The 2016 release brings some of the biggest changes in years, and the full depth and breadth of these changes can create a serious snag in your workflow. For a clear tutorial geared toward the working professional, Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports is the ideal guide for getting up to speed and producing successful reports.



Table of Contents

INTRODUCTION xxix
PART I: GETTING STARTED

CHAPTER 1: INTRODUCING REPORTING SERVICES 3

Who Uses Reporting Services? 4

Information Workers and Data Analysts 5

Information Consumers 6

Business Managers and Leaders 6

Software Developers 6

System Administrators 7

Dashboards, Reports, and Applications 7

Application Integration 7

Business Intelligence and Analytics Solutions 10

Mobile Reports and KPIs 11

Report Tool Choices 14

Simple Report Design 15

IT-Designed Reports 16

User-Designed Reports 16

Server-Based Reports 17

Report Data Sources 18

Enterprise Scale 19

Optimizing Performance 19

Performance 20

Summary 20

CHAPTER 2: WHAT'S NEW IN SQL SERVER 2016 REPORTING SERVICES? 23

Report Builder and Designer Enhancements 25

Modern Browser Rendering 26

Parameter Layout Control 26

Updated RDL Specifi cation 27

Mobile Reports 28

KPIs 30

Native Printing Control 31

PowerPoint Rendering 31

Integrated and Improved Web Portal 31

New Charts and Visual Enhancements 32

Standardized, Modern Browser Rendering 33

Power BI Dashboard Pinning 33

Summary 36

CHAPTER 3: REPORTING SERVICES INSTALLATION AND ARCHITECTURE 39

What's Changed in SQL Server 2016? 41

The Basic Installation 41

Installing Reporting Services 42

Installing the Reporting Services Samples, Exercises, and SQL Server Databases 56

The Enterprise Deployment 57

SQL Server Editions 58

Default and Named Instances 58

Topology 60

Modes 61

Installation Options 61

The Reporting Life Cycle 63

Authoring 63

Management 63

Delivery 64

Reporting Services Tools 64

Report Builder 64

Web Portal 64

SharePoint Libraries and Web Parts 64

Reporting Services Confi guration Manager 65

SQL Server Management Applications 65

Command-Line Utilities 65

HTML Viewer 66

Report Viewer Control 66

Reporting Services Web Service 67

Reporting Services Windows Service 68

HTTP.SYS and the HTTP Listener 69

The Security Sublayer 69

Web Portal and the Web Service 70

Core Processing 71

Service Management 71

WMI and the RPC Interface 72

Reporting Services Processors and Extensions 73

The Report Processor 74

Data Processing Extensions 75

Report Items 76

Rendering Extensions 77

The Scheduling and Delivery Processor 80

Delivery Extensions 80

Reporting Services Application Databases 80

ReportServer 80

ReportServerTempDB 82

Summary 82

PART II: BASIC REPORT DESIGN

CHAPTER 4: REPORT LAYOUT AND FORMATTING 87

Using Report Design Tools 88

Understanding Report Data Building Blocks 89

Data Sources 89

Datasets 90

Data Regions 90

Report Items 93

Samples and Exercises 93

Preparing the Report Data 96

Designing the Report Layout 100

Reviewing the Report 104

Setting Formatting Properties 105

Validating Report Design and Grouping Data 108

Summary 112

CHAPTER 5: DATA ACCESS AND QUERY BASICS 113

Database Essentials 114

Relational Database Concepts 114

What's a Sequel? 114

Data Source Management 115

Embedded and Shared Data Sources 115

Datasets and Fields 119

Embedded and Shared Datasets 120

Exercises 120

Authoring a Query with SQL Server Management Studio 120

Add the Query to the Report Dataset 124

Design the Report Body 128

Enhance the Parameter 131

Using Multiple Parameter Values 134

Summary 138

CHAPTER 6: GROUPING AND TOTALS 139

SQL Server Data Tools 140

Getting Started 140

Getting Started with Sample Reports Projects 144

Report Groups 150

Adding Totals to a Table or Matrix Report 153

Expression Basics 154

Introducing Aggregate Functions and Totals 155

Sorting 155

Exercise 158

Design the Dataset Query 158

Design and Lay Out a Table Report 160

Add Summary Totals and Drill-Down 163

Aggregate Detail Row Summaries 167

Create Parameter List 168

Summary 171

PART III: ADVANCED AND ANALYTIC REPORTING

CHAPTER 7: ADVANCED REPORT DESIGN 175

Pagination and Flow Control 176

Headers and Footers 178

Tablix Headers and Detail Cells 182

Designing the Page Headers 182

Composite Reports and Embedded Content 187

Unlocking the Textbox 187

Padding and Indenting 188

Embedded Formatting 189

Designing Master/Detail Reports 195

Repeating Data Regions: Table, Matrix, and List 196

Groups and Dataset Scope 200

More Aggregate Functions and Totals 200

Designing Subreports 203

Federating Data with a Subreport 205

Navigating Reports 208

Creating a Document Map 209

Exercises 210

Exercise 1: Create a Report Template 210

Exercise 2: Create a Report from the Template with Dynamic Expressions 215

Summ