Cover	1
	Title Page	5
	Copyright	6
	About the Author	9
	About the Technical Editor	9
	Credits	11
	Acknowledgments	13
	Contents	15
	Foreword	27
	Foreword	29
	Introduction	31
	Who This Book is For	31
	What This Book Covers	31
	Part I: Getting Started	31
	Part II: Basic Report Design	32
	Part III: Advanced and Analytic Reporting	32
	Part IV: Solution Patterns	33
	Part V: Reporting Services Custom Programming	33
	Part VI: Mobile Report Solutions	34
	Part VII: Administering Reporting Services	34
	What You Need to Use This Book	35
	Conventions	35
	Sample Reports and Projects	36
	Errata	36
	P2P.WROX.COM	37
	Part I: Getting Started	39
	Chapter 1: Introducing Reporting Services	41
	Who Uses Reporting Services?	42
	Information Workers and Data Analysts	43
	Information Consumers	44
	Business Managers and Leaders	44
	Software Developers	44
	System Administrators	45
	Dashboards, Reports, and Applications	45
	Application Integration	45
	Business Intelligence and Analytics Solutions	48
	Mobile Reports and KPIs	49
	Report Tool Choices	52
	Simple Report Design	53
	IT-Designed Reports	54
	User-Designed Reports	54
	Server-Based Reports	55
	Report Data Sources	56
	Enterprise Scale	57
	Optimizing Performance	57
	Performance	58
	Summary	58
	Chapter 2: What’s New in SQL Server 2016 Reporting Services?	61
	Report Builder and Designer Enhancements	63
	Modern Browser Rendering	64
	Parameter Layout Control	64
	Updated RDL Specification	65
	Mobile Reports	66
	KPIs	68
	Native Printing Control	69
	PowerPoint Rendering	69
	Integrated and Improved Web Portal	69
	New Charts and Visual Enhancements	70
	Standardized, Modern Browser Rendering	71
	Power BI Dashboard Pinning	71
	Summary	74
	Chapter 3: Reporting Services Installation and Architecture	77
	What’s Changed in SQL Server 2016?	79
	The Basic Installation	79
	Installing Reporting Services	80
	Installing the Reporting Services Samples, Exercises, and SQL Server Databases	94
	The Enterprise Deployment	95
	SQL Server Editions	96
	Default and Named Instances	96
	Topology	98
	Modes	99
	Installation Options	99
	The Reporting Life Cycle	101
	Authoring	101
	Management	101
	Delivery	102
	Reporting Services Tools	102
	Report Builder	102
	Web Portal	102
	SharePoint Libraries and Web Parts	102
	Reporting Services Configuration Manager	103
	SQL Server Management Applications	103
	Command-Line Utilities	103
	HTML Viewer	104
	Report Viewer Control	104
	Reporting Services Web Service	105
	Reporting Services Windows Service	106
	HTTP.SYS and the HTTP Listener	107
	The Security Sublayer	107
	Web Portal and the Web Service	108
	Core Processing	109
	Service Management	109
	WMI and the RPC Interface	110
	Reporting Services Processors and Extensions	111
	The Report Processor	112
	Data Processing Extensions	113
	Report Items	114
	Rendering Extensions	115
	The Scheduling and Delivery Processor	118
	Delivery Extensions	118
	Reporting Services Application Databases	118
	ReportServer	118
	ReportServerTempDB	120
	Summary	120
	Part II: Basic Report Design	123
	Chapter 4: Report Layout and Formatting	125
	Using Report Design Tools	126
	Understanding Report Data Building Blocks	127
	Data Sources	127
	Datasets	128
	Data Regions	128
	Report Items	131
	Samples and Exercises	131
	Preparing the Report Data	134
	Designing the Report Layout	138
	Reviewing the Report	142
	Setting Formatting Properties	143
	Validating Report Design and Grouping Data	146
	Summary	150
	Chapter 5: Data Access and Query Basics	151
	Database Essentials	152
	Relational Database Concepts	152
	What’s a Sequel?	152
	Data Source Management	153
	Embedded and Shared Data Sources	153
	Datasets and Fields	157
	Embedded and Shared Datasets	158
	Exercises	158
	Authoring a Query with SQL Server Management Studio	158
	Add the Query to the Report Dataset	162
	Design the Report Body	166
	Enhance the Parameter	169
	Using Multiple Parameter Values	172
	Summary	176
	Chapter 6: Grouping and Totals	177
	SQL Server Data Tools	178
	Getting Started	178
	Getting Started with Sample Reports Projects	182
	Report Groups	188
	Adding Totals to a Table or Matrix Report	191
	Expression Basics	192
	Introducing Aggregate Functions and Totals	193
	Sorting	193
	Exercise	196
	Design the Dataset Query	196
	Design and Lay Out a Table Report	198
	Add Summary Totals and Drill-Down	201
	Aggregate Detail Row Summaries	205
	Create Parameter List	206
	Summary	209
	Part III: Advanced and Analytic Reporting	211
	Chapter 7: Advanced Report Design	213
	Pagination and Flow Control	214
	Headers and Footers	216
	Tablix Headers and Detail Cells	220
	Designing the Page Headers	220
	Composite Reports and Embedded Content	225
	Unlocking the Textbox	225
	Padding and Indenting	226
	Embedded Formatting	227
	Designing Master/Detail Reports	233
	Repeating Data Regions: Table, Matrix, and List	234
	Groups and Dataset Scope	238
	More Aggregate Functions and Totals	238
	Designing Subreports	241
	Federating Data with a Subreport	243
	Navigating Reports	246
	Creating a Document Map	247
	Exercises	248
	Exercise 1: Create a Report Template	248
	Exercise 2: Create a Report from the Template with Dynamic Expressions	253
	Summary	257
	Chapter 8: Graphical Report Design	259
	Visual Design Principles	260
	Keep Charts Simple	260
	Properties, Oh My!	261
	The Fashion of Visualization	261
	Visual Storytelling	262
	Perspective and Skewing	262
	Chart Types	263
	Chart Type Summary	263
	Column and Stacked Charts	266
	Area and Line Charts	267
	Pie and Doughnut Charts	267
	Bubble and Stock Charts	271
	New Chart Types	271
	The Anatomy of a Chart	273
	Multiple Series, Axes, and Areas	275
	Exercises	278
	Exercise 1: Creating and Styling a Simple Chart	278
	Exercise 2: Creating a Multi-series Chart	283
	Useful Properties and Settings	286
	Summary	287
	Chapter 9: Advanced Queries and Parameters	289
	T-SQL Queries and Parameters	290
	Parameter Lists and Multi-select	290
	Cascading Parameters	295
	Arranging Parameters in the Parameter Bar	297
	Managing Long Parameter Lists	297
	All Value Selection	299
	Handling Conditional Logic	302
	MDX Queries and Parameters	304
	Single-Valued Parameter	308
	Multi-Valued Parameter	308
	Date Value Ranges	309
	Summary	313
	Chapter 10: Reporting with Analysis Services	315
	Analysis Services for Reporting	316
	Using Reporting Services with Analysis Services Data	317
	Working with Multidimensional Expression Language	318
	MDX: Simple or Complex?	318
	Building Queries with the MDX Query Designer	319
	Modifying an MDX Query	331
	Adding Nonadditive Measures	340
	When to Use the Aggregate Function	342
	MDX Properties and Cube Formatting	343
	Drill-Through Reports	345
	Parameter Safety Precautions	346
	Best Practices and Provisions	346
	Summary	347
	Chapter 11: SSAS Reporting Advanced Techniques	349
	Building a Dynamic Cube Browser with SSRS	350
	Cube Dynamic Rows	350
	Cube Dynamic Rows Anatomy	351
	Cube Dynamic Rows Summary	360
	Cube Dynamic Rows Expanded	362
	MDX Query Modifications	362
	Design Surface Modifications	363
	Cube Restricting Rows	364
	Designing the Report	364
	Cube Metadata	370
	Designing the Report	370
	Adding Other Cube Metadata	374
	Cube Browser	380
	Anatomy of the Reports	380
	Behind the Scenes	384
	Final Thoughts	400
	Summary	402
	Chapter 12: Expressions and Actions	403
	Basic Expressions Recap	403
	Using the Expression Builder	405
	Calculated Fields	407
	Conditional Expressions	409
	The IIF() Function	410
	Using Custom Code	413
	Using Custom Code in a Report	414
	Links and Drill-Through Reports	416
	Reporting on Recursive Relationships	419
	Actions and Report Navigation	423
	Summary	430
	Part IV: Solution Patterns	433
	Chapter 13: Report Projects and Consolidation	435
	SSDT Solutions and Projects	436
	Project Structure and Development Phases	437
	Shared Datasets and Data Sources	439
	Key Success Factors	440
	Report Specifications	441
	Report Template	444
	Version Control	445
	Setting Up Version Control	446
	Getting the Latest Version	446
	Viewing a Report’s History	447
	Restoring a Previous Version of a Report	447
	Setting Check-out and Check-in Policies	447
	Applying Labels	447
	Synchronizing Content	447
	Deploying an Individual Report	448
	Deploying a Suite of Reports	448
	Checking for Build Errors	448
	Excluding a Report from a Deployment	448
	Managing Server Content	448
	Checking the Deployment Location	449
	Managing Content in Native Mode	450
	Managing Content in SharePoint	451
	Report Builder and Self-Service Reporting Strategies	452
	Report Builder and Semantic Model History	453
	Planning a Self-Service Reporting Environment	454
	You Need a Plan	454
	Design Approaches and Usage Scenarios	454
	Define Ownership	455
	Data Governance	456
	Data Source Access and Security	457
	User Education	457
	Data Source and Query Options	459
	User Report Migration Strategies	463
	Review	463
	Consolidate	464
	Design	464
	Test	464
	Maintain	464
	Summary	465
	Chapter 14: Report Solutions, Patterns, and Recipes	467
	Super Reports	468
	Working with the Strengths and Limitations of the Reporting Services Architecture	469
	Seeking the Excel Export Holy Grail	469
	Report Recipes: Building on Basic Skills	473
	Dashboard Solution Data Sources and Datasets	474
	KPI Scorecard	475
	Gauges	479
	Interactive Sparkline and Chart	481
	Thumbnail Map with Drill-Through Navigation	488
	Summary	494
	Part V: Reporting Services Custom Programming	497
	Chapter 15: Integra Ting Reports Into Custom Applications	499
	URL Access	500
	URL Syntax	501
	Accessing Reporting Services Objects	501
	Reporting Services URL Parameters	507
	Passing Report Information Through the URL	512
	Programmatic Rendering	515
	Common Scenarios	516
	Rendering Through Windows	517
	Rendering to the Web	540
	Using the ReportViewer Control	547
	Embedding a Server-Side Report in a Windows Application	550
	Summary	557
	Chapter 16: Extending Reporting Services	559
	Extension Through Interfaces	562
	What Is an Interface?	562
	Interface Language Differences	562
	A Detailed Look at Data Processing Extensions	565
	Creating a Custom Data Processing Extension	567
	The Scenario	568
	Creating and Setting Up the Project	568
	Creating the DataSetConnection Object	571
	Creating the DataSetParameter Class	580
	Implementing IDataParameter	581
	Creating the DataSetParameterCollection Class	583
	Creating the DataSetCommand Class	585
	Creating the DataSetDataReader Object	600
	Installing the DataSetDataProcessing Extension	604
	Testing DataSetDataExtension	607
	Summary	610
	Part VI: Mobile Report Solutions	611
	Chapter 17: Introducing Reporting Services Mobile Reports	613
	The Mobile Report Experience and Business Case	614
	Report Drill-Through Navigation	617
	When to Use Mobile Reports	617
	Connection and Dataset Design Basics	619
	Introducing Mobile Report Publisher	619
	Layout View	620
	Data View	620
	Dashboard Settings	621
	Preview	621
	Visual Control Categories	622
	Navigators	623
	Summary	630
	Chapter 18: Implementing a Mobile Report with Design-First Development	631
	Design-First Mobile Report Development Exercise	631
	Add Visual Controls	636
	Preview the Mobile Report	639
	Add Data to the Report	640
	Apply Mobile Layouts and Color Styling	651
	Test the Completed Mobile Report from the Server	654
	Summary	658
	Chapter 19: Mobile Report Design Patterns	661
	Key Performance Indicators	661
	The Thing About KPIs	668
	You Need Goals	668
	Time-Series Calculations and Time Grain	669
	Creating a Time-series Mobile Report	670
	Lay Out the Report Using Design-First Report Development	671
	Add Data and Set Control Data Properties	673
	Set Color Palette and Mobile Device Layouts	680
	Server Access and Live Mobile Connectivity	685
	Summary	688
	Chapter 20: Advanced Mobile Report Solutions	691
	Designing a Chart Data Grid Mobile Report	691
	Exercise: Chart Data Grid	692
	Exercise: Adding a Drill-through Mobile Report	700
	Exercise: Adding a DRILL-THROUGH Paginated Report	704
	Getting Serious with Maps	709
	Summary	714
	Part VII: Administering Reporting Services	715
	Chapter 21: Content Management	717
	Using Web Portal	718
	Content Management Activities	721
	Folders	722
	Shared Data Sources	723
	Reports	726
	Report Resources	732
	Shared Schedules	733
	Site and Content Security	734
	Site Security	735
	Item-Level Security	735
	Site Branding	745
	Content Management Automation	748
	The RS Utility	748
	Reporting Services Scripts	751
	Summary	752
	Chapter 22: Server Administration	753
	Security	754
	Account Management	755
	System-Level Roles	759
	Surface Area Management	761
	Backup and Recovery	762
	Application Databases	763
	Encryption Keys	765
	Configuration Files	768
	Other Items	768
	Monitoring	769
	Setup Logs	769
	Windows Application Event Logs	769
	Trace Logs	770
	Execution Logs	773
	Performance Counters	774
	Server Management Reports	779
	Configuration	780
	Memory Management	780
	URL Reservations	781
	E-mail Delivery	783
	Rendering Extensions	785
	My Reports	787
	Summary	789
	Index	791
	EULA	818