Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports

1041 Pages·2017·31.97 MB·English

by  Paul Turley

Table of Contents
Cover
Title Page
Introduction
Who This Book Is For
What This Book Covers
What You Need to Use This Book
Conventions
Sample Reports and Projects
Errata
P2P.WROX.COM
Part I: Getting Started
Chapter 1: Introducing Reporting Services
Who Uses Reporting Services?
Dashboards, Reports, and Applications
Report Tool Choices
Optimizing Performance
Summary
Chapter 2: What's New in SQL Server 2016 Reporting Services?
Report Builder and Designer Enhancements
Modern Browser Rendering
Parameter Layout Control
Updated RDL Specification
Mobile Reports
KPIs
Native Printing Control
PowerPoint Rendering
Integrated and Improved Web Portal
New Charts and Visual Enhancements
Standardized, Modern Browser Rendering
Power BI Dashboard Pinning
Summary
Chapter 3: Reporting Services Installation and Architecture
What's Changed in SQL Server 2016?
The Basic Installation
The Enterprise Deployment
The Reporting Life Cycle
Reporting Services Tools
Reporting Services Windows Service
Reporting Services Processors and Extensions
Reporting Services Application Databases
Summary
Part II: Basic Report Design
Chapter 4: Report Layout and Formatting
Using Report Design Tools
Understanding Report Data Building Blocks
Samples and Exercises
Summary
Chapter 5: Data Access and Query Basics
Database Essentials
Data Source Management
Datasets and Fields
Summary
Chapter 6: Grouping and Totals
SQL Server Data Tools
Report Groups
Exercise
Summary
Part III: Advanced and Analytic Reporting
Chapter 7: Advanced Report Design
Pagination and Flow Control
Headers and Footers
Composite Reports and Embedded Content
Unlocking the Textbox
Designing Master/Detail Reports
Designing Subreports
Navigating Reports
Exercises
Summary
Chapter 8: Graphical Report Design
Visual Design Principles
Chart Types
Exercises
Summary
Chapter 9: Advanced Queries and Parameters
T-SQL Queries and Parameters
MDX Queries and Parameters
Summary
Chapter 10: Reporting With Analysis Services
Analysis Services for Reporting
Using Reporting Services with Analysis Services Data
Working with Multidimensional Expression Language
Adding Nonadditive Measures
Mdx Properties and Cube Formatting
Drill-Through Reports
Best Practices and Provisions
Summary
Chapter 11: SSAS Reporting Advanced Techniques
Building A Dynamic Cube Browser with SSRS
Cube Dynamic Rows
Cube Dynamic Rows Expanded
Cube Restricting Rows
Cube Metadata
Cube Browser
Summary
Chapter 12: Expressions and Actions
Basic Expressions Recap
Using the Expression Builder
Calculated Fields
Conditional Expressions
The IIF() Function
Using Custom Code
Reporting on Recursive Relationships
Actions and Report Navigation
Summary
Part IV: Solution Patterns
Chapter 13: Report Projects and Consolidation
SSDT Solutions and Projects
Version Control
Synchronizing Content
Managing Server Content
Report Builder and Self-Service Reporting Strategies
Report Builder and Semantic Model History
Planning A Self-Service Reporting Environment
User Report Migration Strategies
Summary
Chapter 14: Report Solutions, Patterns, and Recipes
Super Reports
Report Recipes: Building on Basic Skills
Summary
Part V: Reporting Services Custom Programming
Chapter 15: Integrating Reports into Custom Applications
URL Access
Programmatic Rendering
Using the Reportviewer Control
Summary
Chapter 16: Extending Reporting Services
Extension Through Interfaces
Creating a Custom Data Processing Extension
Summary
Part VI: Mobile Report Solutions
Chapter 17: Introducing Reporting Services Mobile Reports
The Mobile Report Experience and Business Case
Connection and Dataset Design Basics
Introducing Mobile Report Publisher
Visual Control Categories
Summary
Chapter 18: Implementing a Mobile Report with Design-First Development
Design-First Mobile Report Development Exercise
Summary
Chapter 19: Mobile Report Design Patterns
Key Performance Indicators
The Thing About KPIs
Creating a Time-Series Mobile Report
Summary
Chapter 20: Advanced Mobile Report Solutions
Designing a Chart Data Grid Mobile Report
Exercise: Adding a Drill-Through Mobile Report
Exercise: Adding a Drill-Through Paginated Report
Summary
Part VII: Administering Reporting Services
Chapter 21: Content Management
Using Web Portal
Content Management Activities
Site and Content Security
Site Branding
Content Management Automation
Summary
Chapter 22: Server Administration
Security
Backup and Recovery
Monitoring
Configuration
Summary
End User License Agreement
List of Illustrations
Chapter 1: Introducing Reporting Services
Figure 1.1 Using navigation links.
Figure 1.2 Web portal.
Figure 1.3 Web portal on iPhone.
Figure 1.4 Mobile report on iPad.
Chapter 2: What's New in SQL Server 2016 Reporting Services?
Figure 2.1 Evolution of Reporting Services.
Figure 2.2 Report Builder's new look.
Figure 2.3 Report Designer grid.
Figure 2.4 RDL file snippet
Figure 2.5 Mobile report on tablet.
Figure 2.6 Mobile report on phone.
Figure 2.7 KPI from SSDT Report Designer dataset.
Figure 2.8 Content menu in the web portal.
Figure 2.9 Two new chart types.
Figure 2.10 Reporting Services Configuration Manager.
Figure 2.11 Power BI pinning added to report toolbar.
Figure 2.12 “Pinnable” items highlighted in web portal.
Figure 2.13 Power BI dashboard and update frequency.
Figure 2.14 Pinned report visuals appearing on the dashboard alongside the
Power BI visuals.
Chapter 3: Reporting Services Installation and Architecture
Figure 3.1 SQL Server setup DVD image mounted as a logical drive.
Figure 3.2 SQL Server Installation Center.
Figure 3.3 Installation page of the SQL Server Installation Center.
Figure 3.4 Setup Support Rules page.
Figure 3.5 System Configuration Check Report.
Figure 3.6 Product Key page.
Figure 3.7 License Terms page.
Figure 3.8 Install Setup Files page.
Figure 3.9 Selecting a SQL Server Feature Installation.
Figure 3.10 Selecting products and features to install.
Figure 3.11 Instance Configuration page.
Figure 3.12 Server Configuration page.
Figure 3.13 Server Configuration tab of the Database Engine Configuration
page.
Figure 3.14 Reporting Services Configuration page.
Figure 3.15 Ready to Install page.
Figure 3.16 Installation Progress page.
Figure 3.17 Web portal.
Figure 3.18 Navigating directly to the report server.
Figure 3.19 The two Reporting Services instances.
Figure 3.20 A named instance and a default instance open in browser windows.
Figure 3.21 SSRS services and dependencies.
Figure 3.22 SSRS core processing.
Chapter 4: Report Layout and Formatting
Figure 4.1 Report Builder with different areas of the designer.
Figure 4.2 Basic components of reports.
Figure 4.3 Report Builder Insert ribbon.
Figure 4.4 Simple multi-level table report.
Figure 4.5 Matrix report.
Figure 4.6 List in design view.
Figure 4.7 List report preview.
Figure 4.8 Creating a new folder.
Figure 4.9 A new empty folder.
Figure 4.10 Getting Started page.
Figure 4.11 Selecting Add Data Source in the Report Data pane.
Figure 4.12 Data Source Properties dialog.
Figure 4.13 Connection Properties dialog.
Figure 4.14 Selecting Add Dataset.
Figure 4.15 Query Designer dialog.
Figure 4.16 List of fields added to Report Data pane.
Figure 4.17 Selecting the Insert Table option.
Figure 4.18 Placing the table.
Figure 4.19 Selecting the ProductCategory field.
Figure 4.20 Inserting columns.
Figure 4.21 Previewing the report using the Run icon.
Figure 4.22 Selecting the background color.
Figure 4.23 Formatting the column for currency.
Figure 4.24 Previewing the report.
Figure 4.25 Revealing the total number of pages.
Figure 4.26 Selecting Group Properties.
Figure 4.27 Selecting the [ProductName] field.
Figure 4.28 Selecting the [OrderQuantity] field.
Figure 4.29 Jumping to the last page of the report.
Chapter 5: Data Access and Query Basics
Figure 5.1 Selecting columns in Query Designer.
Figure 5.2 Using the Auto Detect feature.
Figure 5.3 Adding a report parameter.
Figure 5.4 Generating a T-SQL query.
Figure 5.5 “Connect to Server” dialog.
Figure 5.6 Right-clicking the WroxSSRS2016 database.
Figure 5.7 Selecting the SalesSummaryCountry dataset.
Figure 5.8 Pasting the query copied from SSMS.
Figure 5.9 Entering the year for the Parameter Value.
Figure 5.10 Dataset Properties with query.
Figure 5.11 “Parameter value” referring to a report parameter.
Figure 5.12 Four fields belonging to the dataset.
Figure 5.13 Selecting the Insert Matrix option.
Figure 5.14 Dropping the matrix into the report body.
Figure 5.15 Selecting the [SUM(SalesAmountSum)] data cell.
Figure 5.16 Entering 2013 for the Year parameter.
Figure 5.17 Country field values repeated across columns.
Figure 5.18 Report Parameter Properties dialog.
Figure 5.19 Entering report parameters.
Figure 5.20 Providing a default value for the parameter.
Figure 5.21 Report with the new parameter value.
Figure 5.22 Report Parameter Properties dialog.
Figure 5.23 Changing the WHERE clause of the query.
Figure 5.24 Checking the values 2012 and 2013.
Chapter 6: Grouping and Totals
Figure 6.1 Choosing Reporting Services from the New Project dialog.
Figure 6.2 Window panes in Report Designer.
Figure 6.3 A report in the Design window.
Figure 6.4 Tables added to Query Designer.
Figure 6.5 Column names modified.
Figure 6.6 Using Report Designer properties and features.
Figure 6.7 the Group Properties dialog.
Figure 6.8 ProductCategory field as a group expression.
Figure 6.9 Managing page breaks.
Figure 6.10 Grouped table data region with totals.
Figure 6.11 Design of sample report.
Figure 6.12 Previewing the report.
Figure 6.13 Sorting page of the Group Properties dialog.
Figure 6.14 Displaying by one field and sorting by another.
Figure 6.15 Selecting the vSalesSummaryYearProduct view.
Figure 6.16 Selecting the vSalesSummaryYearProduct view.
Figure 6.17 Filter text added to Query Designer.
Figure 6.18 Report preview showing one row per product.
Figure 6.19 Selecting the Order Quantity column.
Figure 6.20 Selecting the background color icon.
Figure 6.21 Previewing the report.