Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports

About the author Paul Turley is an independent BI consultant and trainer, owner of Intelligent Business LLC and Mentor for SolidQ. He is a Microsoft Data Platform MVP and Microsoft Certified Trainer with multiple industry certifications, and teaches classes on SQL Server technologies to companies around the world. Paul has authored several books and courses on database and BI technologies. He blogs at SqlServerBiBlog.com. Visit us at wrox.com where you have access to free code samples, Programmer to Programmer forums, and discussions on the latest happenings in the industry from around the world.

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

Summary 219

CHAPTER 8: GRAPHICAL REPORT DESIGN 221

Visual Design Principles 222

Keep Charts Simple 222

Properties, Oh My! 223

The Fashion of Visualization 223

Visual Storytelling 224

Perspective and Skewing 224

Chart Types 225

Chart Type Summary 225

Column and Stacked Charts 228

Area and Line Charts 229

Pie and Doughnut Charts 229

Bubble and Stock Charts 233

New Chart Types 233

The Anatomy of a Chart 235

Multiple Series, Axes, and Areas 237

Exercises 240

Exercise 1: Creating and Styling a Simple Chart 240

Exercise 2: Creating a Multi-series Chart 245

Useful Properties and Settings 248

Summary 249

CHAPTER 9: ADVANCED QUERIES AND PARAMETERS 251

T-SQL Queries and Parameters 252

Parameter Lists and Multi-select 252

Cascading Parameters 257

Arranging Parameters in the Parameter Bar 259

Managing Long Parameter Lists 259

All Value Selection 261

Handling Conditional Logic 264

MDX Queries and Parameters 266

Single-Valued Parameter 270

Multi-Valued Parameter 270

Date Value Ranges 271

Summary 275

CHAPTER 10: REPORTING WITH ANALYSIS SERVICES 277

Analysis Services for Reporting 278

Using Reporting Services with Analysis Services Data 279

Working with Multidimensional Expression Language 280

MDX: Simple or Complex? 280

Building Queries with the MDX Query Designer 281

Modifying an MDX Query 293

Adding Nonadditive Measures 302

When to Use the Aggregate Function 304

MDX Properties and Cube Formatting 305

Drill-Through Reports 307

Parameter Safety Precautions 308

Best Practices and Provisions 308

Summary 309

CHAPTER 11: SSAS REPORTING ADVANCED TECHNIQUES 311

Building a Dynamic Cube Browser with SSRS 312

Cube Dynamic Rows 312

Cube Dynamic Rows Anatomy 313

Cube Dynamic Rows Summary 322

Cube Dynamic Rows Expanded 324

MDX Query Modifi cations 324

Design Surface Modifi cations 325

Cube Restricting Rows 326

Designing the Report 326

Cube Metadata 332

Designing the Report 332

Adding Other Cube Metadata 336

Cube Browser 342

Anatomy of the Reports 342

Behind the Scenes 346

Final Thoughts 362

Summary 364

CHAPTER 12: EXPRESSIONS AND ACTIONS 365

Basic Expressions Recap 365

Using the Expression Builder 367

Calculated Fields 369

Conditional Expressions 371

The IIF() Function 372

Using Custom Code 375

Using Custom Code in a Report 376

Links and Drill-Through Reports 378

Reporting on Recursive Relationships 381

Actions and Report Navigation 385

Summary 392

PART IV: SOLUTION PATTERNS

CHAPTER 13: REPORT PROJECTS AND CONSOLIDATION 397

SSDT Solutions and Projects 398

Project Structure and Development Phases 399

Shared Datasets and Data Sources 401

Key Success Factors 402

Report Specifi cations 403

Report Template 406

Version Control 407

Setting Up Version Control 408

Getting the Latest Version 408

Viewing a Report’s History 409

Restoring a Previous Version of a Report 409

Setting Check-out and Check-in Policies 409

Applying Labels 409

Synchronizing Content 409

Deploying an Individual Report 410

Deploying a Suite of Reports 410

Checking for Build Errors 410

Excluding a Report from a Deployment 410

Managing Server Content 410

Checking the Deployment Location 411

Managing Content in Native Mode 412

Managing Content in SharePoint 413

Report Builder and Self-Service Reporting Strategies 414

Report Builder and Semantic Model History 415

Planning a Self-Service Reporting Environment 416

You Need a Plan 416

Design Approaches and Usage Scenarios 416

Defi ne Ownership 417

Data Governance 418

Data Source Access and Security 419

User Education 419

Data Source and Query Options 421

User Report Migration Strategies 425

Review 425

Consolidate 426

Design 426

Test 426

Maintain 426

Summary 427

CHAPTER 14: REPORT SOLUTIONS, PATTERNS, AND RECIPES 429

Super Reports 430

Working with the Strengths and Limitations of the Reporting Services Architecture 431

Seeking the Excel Export Holy Grail 431

Report Recipes: Building on Basic Skills 435

Dashboard Solution Data Sources and Datasets 436

KPI Scorecard 437

Gauges 441

Interactive Sparkline and Chart 443

Thumbnail Map with Drill-Through Navigation 450

Summary 456

PART V: REPORTING SERVICES CUSTOM PROGRAMMING

CHAPTER 15: INTEGRATING REPORTS INTO CUSTOM APPLICATIONS 461

URL Access 462

URL Syntax 463

Accessing Reporting Services Objects 463

Reporting Services URL Parameters 469

Passing Report Information Through the URL 474

Programmatic Rendering 477

Common Scenarios 478

Rendering Through Windows 479

Rendering to the Web 502

Using the ReportViewer Control 509

Embedding a Server-Side Report in a Windows Application 512

Summary 519

CHAPTER 16: EXTENDING REPORTING SERVICES 521

Extension Through Interfaces 524

What Is an Interface? 524

Interface Language Differences 524

A Detailed Look at Data Processing Extensions 527

Creating a Custom Data Processing Extension 529

The Scenario 530

Creating and Setting Up the Project 530

Creating the DataSetConnection Object 533

Creating the DataSetParameter Class 542

Implementing IDataParameter 543

Creating the DataSetParameterCollection Class 545

Creating the DataSetCommand Class 547

Creating the DataSetDataReader Object 562

Installing the DataSetDataProcessing Extension 566

Testing DataSetDataExtension 569

Summary 572

PART VI: MOBILE REPORT SOLUTIONS

CHAPTER 17: INTRODUCING REPORTING SERVICES MOBILE REPORTS 575

The Mobile Report Experience and Business Case 576

Report Drill-Through Navigation 579

When to Use Mobile Reports 579

Connection and Dataset Design Basics 581

Introducing Mobile Report Publisher 581

Layout View 582

Data View 582

Dashboard Settings 583

Preview 583

Visual Control Categories 584

Navigators 585

Summary 592

CHAPTER 18: IMPLEMENTING A MOBILE REPORT WITH DESIGN-FIRST DEVELOPMENT 593

Design-First Mobile Report Development Exercise 593

Add Visual Controls 598

Preview the Mobile Report 601

Add Data to the Report 602

Apply Mobile Layouts and Color Styling 613

Test the Completed Mobile Report from the Server 616

Summary 620

CHAPTER 19: MOBILE REPORT DESIGN PATTERNS 623

Key Performance Indicators 623

The Thing About KPIs 630

You Need Goals 630

Time-Series Calculations and Time Grain 631

Creating a Time-Series Mobile Report 632

Lay Out the Report Using Design-First Report Development 633

Add Data and Set Control Data Properties 635

Set Color Palette and Mobile Device Layouts 642

Server Access and Live Mobile Connectivity 647

Summary 650

CHAPTER 20: ADVANCED MOBILE REPORT SOLUTIONS 653

Designing a Chart Data Grid Mobile Report 653

Exercise: Chart Data Grid 654

Exercise: Adding a Drill-Through Mobile Report 662

Exercise: Adding a Drill-Through Paginated Report 666

Getting Serious with Maps 671

Summary 676

PART VII: ADMINISTERING REPORTING SERVICES

CHAPTER 21: CONTENT MANAGEMENT 679

Using Web Portal 680

Content Management Activities 683

Folders 684

Shared Data Sources 685

Reports 688

Report Resources 694

Shared Schedules 695

Site and Content Security 696

Site Security 697

Item-Level Security 697

Site Branding 707

Content Management Automation 710

The RS Utility 710

Reporting Services Scripts 713

Summary 714

CHAPTER 22: SERVER ADMINISTRATION 715

Security 716

Account Management 717

System-Level Roles 721

Surface Area Management 723

Backup and Recovery 724

Application Databases 725

Encryption Keys 727

Confi guration Files 730

Other Items 730

Monitoring 731

Setup Logs 731

Windows Application Event Logs 731

Trace Logs 732

Execution Logs 735

Performance Counters 736

Server Management Reports 741

Confi guration 742

Memory Management 742

URL Reservations 743

E-mail Delivery 745

Rendering Extensions 747

My Reports 749

Summary 751

INDEX 753