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