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 xxixPART I: GETTING STARTEDCHAPTER 1: INTRODUCING REPORTING SERVICES 3Who Uses Reporting Services? 4Information Workers and Data Analysts 5Information Consumers 6Business Managers and Leaders 6Software Developers 6System Administrators 7Dashboards, Reports, and Applications 7Application Integration 7Business Intelligence and Analytics Solutions 10Mobile Reports and KPIs 11Report Tool Choices 14Simple Report Design 15IT-Designed Reports 16User-Designed Reports 16Server-Based Reports 17Report Data Sources 18Enterprise Scale 19Optimizing Performance 19Performance 20Summary 20CHAPTER 2: WHAT’S NEW IN SQL SERVER 2016 REPORTING SERVICES? 23Report Builder and Designer Enhancements 25Modern Browser Rendering 26Parameter Layout Control 26Updated RDL Specifi cation 27Mobile Reports 28KPIs 30Native Printing Control 31PowerPoint Rendering 31Integrated and Improved Web Portal 31New Charts and Visual Enhancements 32Standardized, Modern Browser Rendering 33Power BI Dashboard Pinning 33Summary 36CHAPTER 3: REPORTING SERVICES INSTALLATION AND ARCHITECTURE 39What’s Changed in SQL Server 2016? 41The Basic Installation 41Installing Reporting Services 42Installing the Reporting Services Samples, Exercises, and SQL Server Databases 56The Enterprise Deployment 57SQL Server Editions 58Default and Named Instances 58Topology 60Modes 61Installation Options 61The Reporting Life Cycle 63Authoring 63Management 63Delivery 64Reporting Services Tools 64Report Builder 64Web Portal 64SharePoint Libraries and Web Parts 64Reporting Services Confi guration Manager 65SQL Server Management Applications 65Command-Line Utilities 65HTML Viewer 66Report Viewer Control 66Reporting Services Web Service 67Reporting Services Windows Service 68HTTP.SYS and the HTTP Listener 69The Security Sublayer 69Web Portal and the Web Service 70Core Processing 71Service Management 71WMI and the RPC Interface 72Reporting Services Processors and Extensions 73The Report Processor 74Data Processing Extensions 75Report Items 76Rendering Extensions 77The Scheduling and Delivery Processor 80Delivery Extensions 80Reporting Services Application Databases 80ReportServer 80ReportServerTempDB 82Summary 82PART II: BASIC REPORT DESIGNCHAPTER 4: REPORT LAYOUT AND FORMATTING 87Using Report Design Tools 88Understanding Report Data Building Blocks 89Data Sources 89Datasets 90Data Regions 90Report Items 93Samples and Exercises 93Preparing the Report Data 96Designing the Report Layout 100Reviewing the Report 104Setting Formatting Properties 105Validating Report Design and Grouping Data 108Summary 112CHAPTER 5: DATA ACCESS AND QUERY BASICS 113Database Essentials 114Relational Database Concepts 114What’s a Sequel? 114Data Source Management 115Embedded and Shared Data Sources 115Datasets and Fields 119Embedded and Shared Datasets 120Exercises 120Authoring a Query with SQL Server Management Studio 120Add the Query to the Report Dataset 124Design the Report Body 128Enhance the Parameter 131Using Multiple Parameter Values 134Summary 138CHAPTER 6: GROUPING AND TOTALS 139SQL Server Data Tools 140Getting Started 140Getting Started with Sample Reports Projects 144Report Groups 150Adding Totals to a Table or Matrix Report 153Expression Basics 154Introducing Aggregate Functions and Totals 155Sorting 155Exercise 158Design the Dataset Query 158Design and Lay Out a Table Report 160Add Summary Totals and Drill-Down 163Aggregate Detail Row Summaries 167Create Parameter List 168Summary 171PART III: ADVANCED AND ANALYTIC REPORTINGCHAPTER 7: ADVANCED REPORT DESIGN 175Pagination and Flow Control 176Headers and Footers 178Tablix Headers and Detail Cells 182Designing the Page Headers 182Composite Reports and Embedded Content 187Unlocking the Textbox 187Padding and Indenting 188Embedded Formatting 189Designing Master/Detail Reports 195Repeating Data Regions: Table, Matrix, and List 196Groups and Dataset Scope 200More Aggregate Functions and Totals 200Designing Subreports 203Federating Data with a Subreport 205Navigating Reports 208Creating a Document Map 209Exercises 210Exercise 1: Create a Report Template 210Exercise 2: Create a Report from the Template with Dynamic Expressions 215Summary 219CHAPTER 8: GRAPHICAL REPORT DESIGN 221Visual Design Principles 222Keep Charts Simple 222Properties, Oh My! 223The Fashion of Visualization 223Visual Storytelling 224Perspective and Skewing 224Chart Types 225Chart Type Summary 225Column and Stacked Charts 228Area and Line Charts 229Pie and Doughnut Charts 229Bubble and Stock Charts 233New Chart Types 233The Anatomy of a Chart 235Multiple Series, Axes, and Areas 237Exercises 240Exercise 1: Creating and Styling a Simple Chart 240Exercise 2: Creating a Multi-series Chart 245Useful Properties and Settings 248Summary 249CHAPTER 9: ADVANCED QUERIES AND PARAMETERS 251T-SQL Queries and Parameters 252Parameter Lists and Multi-select 252Cascading Parameters 257Arranging Parameters in the Parameter Bar 259Managing Long Parameter Lists 259All Value Selection 261Handling Conditional Logic 264MDX Queries and Parameters 266Single-Valued Parameter 270Multi-Valued Parameter 270Date Value Ranges 271Summary 275CHAPTER 10: REPORTING WITH ANALYSIS SERVICES 277Analysis Services for Reporting 278Using Reporting Services with Analysis Services Data 279Working with Multidimensional Expression Language 280MDX: Simple or Complex? 280Building Queries with the MDX Query Designer 281Modifying an MDX Query 293Adding Nonadditive Measures 302When to Use the Aggregate Function 304MDX Properties and Cube Formatting 305Drill-Through Reports 307Parameter Safety Precautions 308Best Practices and Provisions 308Summary 309CHAPTER 11: SSAS REPORTING ADVANCED TECHNIQUES 311Building a Dynamic Cube Browser with SSRS 312Cube Dynamic Rows 312Cube Dynamic Rows Anatomy 313Cube Dynamic Rows Summary 322Cube Dynamic Rows Expanded 324MDX Query Modifi cations 324Design Surface Modifi cations 325Cube Restricting Rows 326Designing the Report 326Cube Metadata 332Designing the Report 332Adding Other Cube Metadata 336Cube Browser 342Anatomy of the Reports 342Behind the Scenes 346Final Thoughts 362Summary 364CHAPTER 12: EXPRESSIONS AND ACTIONS 365Basic Expressions Recap 365Using the Expression Builder 367Calculated Fields 369Conditional Expressions 371The IIF() Function 372Using Custom Code 375Using Custom Code in a Report 376Links and Drill-Through Reports 378Reporting on Recursive Relationships 381Actions and Report Navigation 385Summary 392PART IV: SOLUTION PATTERNSCHAPTER 13: REPORT PROJECTS AND CONSOLIDATION 397SSDT Solutions and Projects 398Project Structure and Development Phases 399Shared Datasets and Data Sources 401Key Success Factors 402Report Specifi cations 403Report Template 406Version Control 407Setting Up Version Control 408Getting the Latest Version 408Viewing a Report’s History 409Restoring a Previous Version of a Report 409Setting Check-out and Check-in Policies 409Applying Labels 409Synchronizing Content 409Deploying an Individual Report 410Deploying a Suite of Reports 410Checking for Build Errors 410Excluding a Report from a Deployment 410Managing Server Content 410Checking the Deployment Location 411Managing Content in Native Mode 412Managing Content in SharePoint 413Report Builder and Self-Service Reporting Strategies 414Report Builder and Semantic Model History 415Planning a Self-Service Reporting Environment 416You Need a Plan 416Design Approaches and Usage Scenarios 416Defi ne Ownership 417Data Governance 418Data Source Access and Security 419User Education 419Data Source and Query Options 421User Report Migration Strategies 425Review 425Consolidate 426Design 426Test 426Maintain 426Summary 427CHAPTER 14: REPORT SOLUTIONS, PATTERNS, AND RECIPES 429Super Reports 430Working with the Strengths and Limitations of the Reporting Services Architecture 431Seeking the Excel Export Holy Grail 431Report Recipes: Building on Basic Skills 435Dashboard Solution Data Sources and Datasets 436KPI Scorecard 437Gauges 441Interactive Sparkline and Chart 443Thumbnail Map with Drill-Through Navigation 450Summary 456PART V: REPORTING SERVICES CUSTOM PROGRAMMINGCHAPTER 15: INTEGRATING REPORTS INTO CUSTOM APPLICATIONS 461URL Access 462URL Syntax 463Accessing Reporting Services Objects 463Reporting Services URL Parameters 469Passing Report Information Through the URL 474Programmatic Rendering 477Common Scenarios 478Rendering Through Windows 479Rendering to the Web 502Using the ReportViewer Control 509Embedding a Server-Side Report in a Windows Application 512Summary 519CHAPTER 16: EXTENDING REPORTING SERVICES 521Extension Through Interfaces 524What Is an Interface? 524Interface Language Differences 524A Detailed Look at Data Processing Extensions 527Creating a Custom Data Processing Extension 529The Scenario 530Creating and Setting Up the Project 530Creating the DataSetConnection Object 533Creating the DataSetParameter Class 542Implementing IDataParameter 543Creating the DataSetParameterCollection Class 545Creating the DataSetCommand Class 547Creating the DataSetDataReader Object 562Installing the DataSetDataProcessing Extension 566Testing DataSetDataExtension 569Summary 572PART VI: MOBILE REPORT SOLUTIONSCHAPTER 17: INTRODUCING REPORTING SERVICES MOBILE REPORTS 575The Mobile Report Experience and Business Case 576Report Drill-Through Navigation 579When to Use Mobile Reports 579Connection and Dataset Design Basics 581Introducing Mobile Report Publisher 581Layout View 582Data View 582Dashboard Settings 583Preview 583Visual Control Categories 584Navigators 585Summary 592CHAPTER 18: IMPLEMENTING A MOBILE REPORT WITH DESIGN-FIRST DEVELOPMENT 593Design-First Mobile Report Development Exercise 593Add Visual Controls 598Preview the Mobile Report 601Add Data to the Report 602Apply Mobile Layouts and Color Styling 613Test the Completed Mobile Report from the Server 616Summary 620CHAPTER 19: MOBILE REPORT DESIGN PATTERNS 623Key Performance Indicators 623The Thing About KPIs 630You Need Goals 630Time-Series Calculations and Time Grain 631Creating a Time-Series Mobile Report 632Lay Out the Report Using Design-First Report Development 633Add Data and Set Control Data Properties 635Set Color Palette and Mobile Device Layouts 642Server Access and Live Mobile Connectivity 647Summary 650CHAPTER 20: ADVANCED MOBILE REPORT SOLUTIONS 653Designing a Chart Data Grid Mobile Report 653Exercise: Chart Data Grid 654Exercise: Adding a Drill-Through Mobile Report 662Exercise: Adding a Drill-Through Paginated Report 666Getting Serious with Maps 671Summary 676PART VII: ADMINISTERING REPORTING SERVICESCHAPTER 21: CONTENT MANAGEMENT 679Using Web Portal 680Content Management Activities 683Folders 684Shared Data Sources 685Reports 688Report Resources 694Shared Schedules 695Site and Content Security 696Site Security 697Item-Level Security 697Site Branding 707Content Management Automation 710The RS Utility 710Reporting Services Scripts 713Summary 714CHAPTER 22: SERVER ADMINISTRATION 715Security 716Account Management 717System-Level Roles 721Surface Area Management 723Backup and Recovery 724Application Databases 725Encryption Keys 727Confi guration Files 730Other Items 730Monitoring 731Setup Logs 731Windows Application Event Logs 731Trace Logs 732Execution Logs 735Performance Counters 736Server Management Reports 741Confi guration 742Memory Management 742URL Reservations 743E-mail Delivery 745Rendering Extensions 747My Reports 749Summary 751INDEX 753