Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports

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 Modifications 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
Configuration 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
Configuration 742
Memory Management 742
URL Reservations 743
E-mail Delivery 745
Rendering Extensions 747
My Reports 749
Summary 751
INDEX 753