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
1. Who Uses Reporting Services?
2. Dashboards, Reports, and Applications
3. Report Tool Choices
4. Optimizing Performance
5. Summary
Chapter 2: What's New in SQL Server 2016 Reporting Services?
1. Report Builder and Designer Enhancements
2. Modern Browser Rendering
3. Parameter Layout Control
4. Updated RDL Specification
5. Mobile Reports
6. KPIs
7. Native Printing Control
8. PowerPoint Rendering
9. Integrated and Improved Web Portal
10. New Charts and Visual Enhancements
11. Standardized, Modern Browser Rendering
12. Power BI Dashboard Pinning
13. Summary
Chapter 3: Reporting Services Installation and Architecture
1. What's Changed in SQL Server 2016?
2. The Basic Installation
3. The Enterprise Deployment
4. The Reporting Life Cycle
5. Reporting Services Tools
6. Reporting Services Windows Service
7. Reporting Services Processors and Extensions
8. Reporting Services Application Databases
9. Summary

Part II: Basic Report Design

Chapter 4: Report Layout and Formatting
1. Using Report Design Tools
2. Understanding Report Data Building Blocks
3. Samples and Exercises
4. Summary
Chapter 5: Data Access and Query Basics
1. Database Essentials
2. Data Source Management
3. Datasets and Fields
4. Summary
Chapter 6: Grouping and Totals
1. SQL Server Data Tools
2. Report Groups
3. Exercise
4. Summary

Part III: Advanced and Analytic Reporting

Chapter 7: Advanced Report Design
1. Pagination and Flow Control
2. Headers and Footers
3. Composite Reports and Embedded Content
4. Unlocking the Textbox
5. Designing Master/Detail Reports
6. Designing Subreports
7. Navigating Reports
8. Exercises
9. Summary
Chapter 8: Graphical Report Design
1. Visual Design Principles
2. Chart Types
3. Exercises
4. Summary
Chapter 9: Advanced Queries and Parameters
1. T-SQL Queries and Parameters
2. MDX Queries and Parameters
3. Summary
Chapter 10: Reporting With Analysis Services
1. Analysis Services for Reporting
2. Using Reporting Services with Analysis Services Data
3. Working with Multidimensional Expression Language
4. Adding Nonadditive Measures
5. Mdx Properties and Cube Formatting
6. Drill-Through Reports
7. Best Practices and Provisions
8. Summary
Chapter 11: SSAS Reporting Advanced Techniques
1. Building A Dynamic Cube Browser with SSRS
2. Cube Dynamic Rows
3. Cube Dynamic Rows Expanded
4. Cube Restricting Rows
5. Cube Metadata
6. Cube Browser
7. Summary
Chapter 12: Expressions and Actions
1. Basic Expressions Recap
2. Using the Expression Builder
3. Calculated Fields
4. Conditional Expressions
5. The IIF() Function
6. Using Custom Code
7. Reporting on Recursive Relationships
8. Actions and Report Navigation
9. Summary

Part IV: Solution Patterns

Chapter 13: Report Projects and Consolidation
1. SSDT Solutions and Projects
2. Version Control
3. Synchronizing Content
4. Managing Server Content
5. Report Builder and Self-Service Reporting Strategies
6. Report Builder and Semantic Model History
7. Planning A Self-Service Reporting Environment
8. User Report Migration Strategies
9. Summary
Chapter 14: Report Solutions, Patterns, and Recipes
1. Super Reports
2. Report Recipes: Building on Basic Skills
3. Summary

Part V: Reporting Services Custom Programming

Chapter 15: Integrating Reports into Custom Applications
1. URL Access
2. Programmatic Rendering
3. Using the Reportviewer Control
4. Summary
Chapter 16: Extending Reporting Services
1. Extension Through Interfaces
2. Creating a Custom Data Processing Extension
3. Summary

Part VI: Mobile Report Solutions

Chapter 17: Introducing Reporting Services Mobile Reports
1. The Mobile Report Experience and Business Case
2. Connection and Dataset Design Basics
3. Introducing Mobile Report Publisher
4. Visual Control Categories
5. Summary
Chapter 18: Implementing a Mobile Report with Design-First Development
1. Design-First Mobile Report Development Exercise
2. Summary
Chapter 19: Mobile Report Design Patterns
1. Key Performance Indicators
2. The Thing About KPIs
3. Creating a Time-Series Mobile Report
4. Summary
Chapter 20: Advanced Mobile Report Solutions
1. Designing a Chart Data Grid Mobile Report
2. Exercise: Adding a Drill-Through Mobile Report
3. Exercise: Adding a Drill-Through Paginated Report
4. Summary

Part VII: Administering Reporting Services

Chapter 21: Content Management
1. Using Web Portal
2. Content Management Activities
3. Site and Content Security
4. Site Branding
5. Content Management Automation
6. Summary
Chapter 22: Server Administration
1. Security
2. Backup and Recovery
3. Monitoring
4. Configuration
5. 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.
Figure 6.22 Clicking the down-arrow icon.
Figure 6.23 Previewing the report.
Figure 6.24 Totals for the last category and the overall total.
Figure 6.25 Expression editor window.
Figure 6.26 Changing text to Grand Total.
Figure 6.27 Category name and category total.
Figure 6.28 Selecting ProductSubcategory.
Figure 6.29 Revealing three products in the Helmets subcategory.
Figure 6.30 Adding a query script to return a distinct list.
Figure 6.31 Choosing “Available Values” and query options.
Figure 6.32 Modifying the expression.
Figure 6.33 Entire report in default view.

Chapter 7: Advanced Report Design

Figure 7.1 Report properties in the designer.
Figure 7.2 Report Properties, Page Setup page.
Figure 7.3 Properties window, Size properties group.
Figure 7.4 Headers and footers in the designer for a typical table report.
Figure 7.5 Rendered table report with headers and footers.
Figure 7.6 Page header shown on the second page.
Figure 7.7 Page Header Properties dialog.
Figure 7.8 Row group header boundary.
Figure 7.9 Assembling the page footer.
Figure 7.10 Assembling the page header.
Figure 7.11 Assembling the report header.
Figure 7.12 Tablix Properties dialog.
Figure 7.13 Setting up repeating page header in Advanced Mode.
Figure 7.14 Textbox Padding properties.
Figure 7.15 Text with embedded formatting.
Figure 7.16 Placeholder Properties dialog.
Figure 7.17 Previewed report with data-bound embedded formatting.
Figure 7.18 Report header content contained in a rectangle.
Figure 7.19 Page header content contained in a rectangle.
Figure 7.20 Setting a page break in the rectangle properties.
Figure 7.21 Long parameter text in page header.
Figure 7.22 Setting the PageBreak Disabled property with an expression.
Figure 7.23 Report rendered to Excel.
Figure 7.24 Multi-level table report with drill-down and repeating chart.
Figure 7.25 Matrix report with repeating chart.
Figure 7.26 List report with free-form layout and embedded chart.
Figure 7.27 Combination report with list, embedded table, and chart.
Figure 7.28 Different aggregate functions used in totals.
Figure 7.29 Using a scoped aggregation.
Figure 7.30 Putting the calculations together in a meaningful solution.
Figure 7.31 Adding a subreport to the main report.
Figure 7.32 Modify the dataset parameter using an expression.
Figure 7.33 Product Details report with subreport in designer.
Figure 7.34 Product Details report in preview.
Figure 7.35 Multi-group table report with document map.
Figure 7.36 Report Properties dialog.
Figure 7.37 Page Header Properties dialog.
Figure 7.38 Adding an embedded image.
Figure 7.39 Completed template report.
Figure 7.40 Select an item from the Add New Item dialog.
Figure 7.41 The Query Designer.
Figure 7.42 Report Parameter Properties.
Figure 7.43 Add a Figure caption here.
Figure 7.44 Matrix Design view and BackgroundColor property expresssion.
Figure 7.45 Preview of finished report.

Chapter 8: Graphical Report Design

Figure 8.1 Two renditions of the same chart.
Figure 8.2 Stacked column chart.
Figure 8.3 Area chart.
Figure 8.4 Pie chart for Bike product categories.
Figure 8.5 Doughnut chart versus bar chart.
Figure 8.6 Top 200 Product Sales report.
Figure 8.7 Bubble chart.
Figure 8.8 Series Properties page for bubble chart.
Figure 8.9 Tree Map chart.
Figure 8.10 Sunburst chart.
Figure 8.11 Chart properties and settings.
Figure 8.12 Hierarchy of chart objects.
Figure 8.13 Adding a new chart area.
Figure 8.14 Series Properties option.
Figure 8.15 Selecting a chart area.
Figure 8.16 Finished report with line chart and axis moved.
Figure 8.17 Chart Area Properties dialog.
Figure 8.18 Changing the Category to Currency.
Figure 8.19 Changing the Interval to 1.
Figure 8.20 Viewing the tooltip.
Figure 8.21 Previewing the report.
Figure 8.22 Changing the “Vertical axis” to Secondary.
Figure 8.23 Previewing the revised report.

Chapter 9: Advanced Queries and Parameters

Figure 9.1 Parameters page of Dataset Properties dialog.
Figure 9.2 Report Parameter Properties page of the Report Data window.
Figure 9.3 Specifying the “Value field” and “Label field” properties.
Figure 9.4 Clarifying the context in the printed report.
Figure 9.5 Finished report with the parameter list in the heading.
Figure 9.6 Filtering product subcategories.
Figure 9.7 Available Values settings.
Figure 9.8 Using the right-click menu to add and remove columns and rows.
Figure 9.9 Products in parameter list.
Figure 9.10 Using “(All Countries)” to provide users with an option.
Figure 9.11 Using a numeric key rather than text.
Figure 9.12 Designer parameter bar grid arrangement.
Figure 9.13 Preview with month of July displayed.
Figure 9.14 Matrix report showing results of MDX query.
Figure 9.15 Mapping to the “Value field” and “Label field”.
Figure 9.16 Using a full-qualified unique name reference for each key value.
Figure 9.17 Copying values.
Figure 9.18 Naming two query parameters.
Figure 9.19 Dataset Properties page.

Chapter 10: Reporting with Analysis Services

Figure 10.1 Adding a new data source.
Figure 10.2 Shared Data Source Properties dialog.
Figure 10.3 Connection Properties dialog.
Figure 10.4 Changing the name of the data source.
Figure 10.5 MDX Query Designer.
Figure 10.6 Analysis Services metadata objects.
Figure 10.7 Query Designer filled in.
Figure 10.8 Defining the Filter Expression.
Figure 10.9 Default slicer.
Figure 10.10 Table report.
Figure 10.11 Indicator Properties dialog.
Figure 10.12 Switching to the text query view.
Figure 10.13 Query as it appears in Query window.
Figure 10.14 Testing the query.
Figure 10.15 Three new calculated members in the dataset fields collection.
Figure 10.16 Completed report.
Figure 10.17 Basic matrix report.
Figure 10.18 Report in preview.
Figure 10.19 Report with changes.
Figure 10.20 Miscalculated report and fixed report.
Figure 10.21 Modifying field properties.
Figure 10.22 Action expression settings.

Chapter 11: SSAS Reporting Advanced Techniques

Figure 11.1 Behavior of final report.
Figure 11.2 Cube Dynamic Rows Report Data.
Figure 11.3 DataSet1 in Query Designer.
Figure 11.4 Query Parameters dialog.
Figure 11.5 Results of query.
Figure 11.6 Expression group on [Row Key].
Figure 11.7 Seeing how rows are sorted.
Figure 11.8 Changing the text alignment and padding properties.
Figure 11.9 Setting the BackgroundColor property.
Figure 11.10 Current cell properties.
Figure 11.11 Testing the changing of the report.
Figure 11.12 Changing action.
Figure 11.13 Viewing the changed report.
Figure 11.14 Viewing each hierarchy level in a new column.
Figure 11.15 Creating a new table/tablix.
Figure 11.16 Completed report.
Figure 11.17 Creating a new dataset.
Figure 11.18 Setting the “Value field” and “Label field” values.
Figure 11.19 Default value of pRowCount parameter.
Figure 11.20 Results of execution.
Figure 11.21 Previewing the report.
Figure 11.22 Adding pRowCount.
Figure 11.23 Interacting with a report parameter.
Figure 11.24 Finished report.
Figure 11.25 Dataset properties.
Figure 11.26 Cube metadata.
Figure 11.27 Dataset Properties dialog showing filter condition.
Figure 11.28 Text Box Properties dialog.
Figure 11.29 Clicking a cube to display an associated MeasureGroup.
Figure 11.30 Complete report data.
Figure 11.31 Design surface.
Figure 11.32 Preview.
Figure 11.33 Design surface.
Figure 11.34 Preview.
Figure 11.35 Sales report.
Figure 11.36 Profit and loss report.
Figure 11.37 Key navigation paths.
Figure 11.38 List of parameters and default values.
Figure 11.39 Metadata and measure value.
Figure 11.40 Main tablix.
Figure 11.41 Adding an ascending or descending control.
Figure 11.42 Parameter values.
Figure 11.43 Swapping of row and column parameters.
Figure 11.44 Action.
Figure 11.45 Selecting a measure.
Figure 11.46 Design surface and Action tab.
Figure 11.47 Preview of result.
Figure 11.48 Action parameters.
Figure 11.49 Selecting the Date parameter.
Figure 11.50 Formatted footer.
Figure 11.51 Angry Koala Cube Surfer report.
Figure 11.52 Report with a lag of 12.

Chapter 12: Expressions and Actions

Figure 12.1 Expression window.
Figure 12.2 Textbox used to calculate profit margin.
Figure 12.3 Selecting the dataset you want to use.
Figure 12.4 Dataset properties dialog.
Figure 12.5 Field reference in a textbox.
Figure 12.6 Report results.
Figure 12.7 Finished report.
Figure 12.8 Product Details report.
Figure 12.9 Action page in the Text Box Properties dialog.
Figure 12.10 Row Groups pane.
Figure 12.11 Group Properties dialog.
Figure 12.12 Setting the “Recursive parent” property.
Figure 12.13 Correct design environment.
Figure 12.14 Finished report.
Figure 12.15 Two example reports.
Figure 12.16 Table with a drill-through action on the first column.
Figure 12.17 Report Data window.
Figure 12.18 Text Box Properties dialog.
Figure 12.19 Clicking a column of interest on the column chart.
Figure 12.20 Report action properties.
Figure 12.21 Report in Design view.
Figure 12.22 Parameter reset back to the default state.

Chapter 13: Report Projects and Consolidation

Figure 13.1 New Project dialog.
Figure 13.2 Shared datasets in SSDT.
Figure 13.3 Requirements document template.
Figure 13.4 Sketch of a dashboard report solution.
Figure 13.5 Property Pages for sample server.
Figure 13.6 Feature differences and similarities between the Report Builder and SSDT design tools.
Figure 13.7 Tasks and roles that are best suited for each design tool.
Figure 13.8 Report part folder.
Figure 13.9 Options dialog.
Figure 13.10 The Publish Report Parts option.

Chapter 14: Report Solutions, Patterns, and Recipes

Figure 14.1 Interdependencies between basic report design elements.
Figure 14.2 Final dashboard.
Figure 14.3 Final scorecard report.
Figure 14.4 MDX Query Designer.
Figure 14.5 Completed scorecard table in report designer.
Figure 14.6 Score table with AVG expressions.
Figure 14.7 Add indicator to table.
Figure 14.8 Select Indicator Type dialog.
Figure 14.9 Select Gauge Type dialog.
Figure 14.10 Sparkline table and chart in the report designer.
Figure 14.11 MDX query designer.
Figure 14.12 Parameter properties.
Figure 14.13 Select Sparkline Type dialog.
Figure 14.14 Chart Data window.
Figure 14.15 Category group expression.
Figure 14.16 Chart Properties filter expressions.
Figure 14.17 Text Box Properties Action page.
Figure 14.18 Sparkline table and chart in preview.
Figure 14.19 Choosing a map from the Map Gallery.
Figure 14.20 Map Layers window.
Figure 14.21 MapBindingFieldPair Editor dialog.

Chapter 15: Integrating Reports into Custom Applications

Figure 15.1 Web Portal Favorites page.
Figure 15.2 Report server page.
Figure 15.3 Data Sources folder.
Figure 15.4 Data source contents.
Figure 15.5 Sales Dashboard report.
Figure 15.6 Internet Sales KPI Dashboard.
Figure 15.7 Dashboard report with parameters.
Figure 15.8 Custom rendering application interface.
Figure 15.9 Add Service Reference dialog.
Figure 15.10 Service Reference Settings dialog.
Figure 15.11 Add Web Reference dialog containing WSDL.
Figure 15.12 Reporting Service Rendering application report list.
Figure 15.13 Reporting Service Rendering application.
Figure 15.14 Confirmation message box.
Figure 15.15 New Project dialog.
Figure 15.16 Data Source Configuration Wizard.
Figure 15.17 ReportViewer control properties.
Figure 15.18 ReportViewerTasks smart tag panel.
Figure 15.19 Sample report with map and table.
Figure 15.20 WroxReportViewer form running.
Figure 15.21 Report in viewer application.

Chapter 16: Extending Reporting Services

Figure 16.1 Reporting Services interfaces in Visual Studio.
Figure 16.2 ADO.NET object model.
Figure 16.3 Reference added to ReportingServices.Interfaces.
Figure 16.4 Application page and assembly information.
Figure 16.5 Interfaces in Visual Studio Class Designer.
Figure 16.6 Data Source Properties with DATASET type selected.
Figure 16.9 Dataset results.
Figure 16.7 Credentials page.
Figure 16.8 Query command in Dataset Properties.

Chapter 17: Introducing Reporting Services Mobile Reports

Figure 17.1 A running mobile report in full-screen mode on an iPad.
Figure 17.2 A simple mobile report displayed in a web browser.
Figure 17.3 Report viewed in the phone app.
Figure 17.4 Pages on Mobile Report Publisher.
Figure 17.5 Mobile Report Publisher Layout page.
Figure 17.6 Navigator controls.
Figure 17.7 The dataset shown in the Data page.
Figure 17.8 Grid and Selection list controls.
Figure 17.9 Gauge type controls.
Figure 17.10 Chart controls.
Figure 17.11 Same data using different chart types.
Figure 17.12 Report with Tree map.
Figure 17.13 Map controls.
Figure 17.14 Map selection list.

Chapter 18: Implementing a Mobile Report with Design-First Development

Figure 18.1 Web Portal with compact menu items.
Figure 18.2 Web Portal after selecting “Mobile Report.”
Figure 18.3 Mobile Report Publisher—Settings page.
Figure 18.4 “Save mobile report as” destination options.
Figure 18.5 Report server and location properties.
Figure 18.6 Report Mobile Publisher—Layout page.
Figure 18.7 Time navigator Time level presets.
Figure 18.8 Setting properties for the Time navigator.
Figure 18.9 Category Sales chart added.
Figure 18.10 Sales Summary by Country and Category report with selected Country.
Figure 18.11 Data page with simulated datasets.
Figure 18.12 Visual controls on the report.
Figure 18.13 Datasets added in the Data page.
Figure 18.14 Selecting a shared data source.
Figure 18.15 Creating a shared dataset.
Figure 18.16 Shared datasets viewed in Web Portal.
Figure 18.17 Add data options.
Figure 18.18 Select the report server.
Figure 18.19 New tables added to report datasets.
Figure 18.20 Time navigator background chart properties.
Figure 18.21 Filter options in the properties for the Select Country control.
Figure 18.22 Number gauge data filter properties and aggregating function selection.
Figure 18.23 Category Sales chart Data properties.
Figure 18.24 Color palette selection.
Figure 18.25 Choosing a report layout.
Figure 18.26 Report shown in phone layout.
Figure 18.27 Report optimized for the phone layout.
Figure 18.28 Phone layout preview.
Figure 18.29 Sales Summary by Country and Category report in Web Portal.
Figure 18.30 Power BI Mobile app on tablet.
Figure 18.31 Connecting to server.
Figure 18.32 Report viewed in the mobile app.

Chapter 19: Mobile Report Design Patterns

Figure 19.1 Elements of a KPI.
Figure 19.2 Completed design page for the US Bike Sales - 2013 KPI.
Figure 19.3 Parameters for Actual and Target Sales dataset.
Figure 19.4 Field selection for Actual and Target Sales dataset.
Figure 19.5 Parameter values for Sales By Month for Year and Country dataset.
Figure 19.6 Field selection for Sales By Month for Year and Country dataset.
Figure 19.7 KPIs in the Web Portal.
Figure 19.8 New Mobile Report from Web Portal.
Figure 19.9 Mobile Report Publisher message.
Figure 19.10 Report layout example for control positioning.
Figure 19.11 Time navigator Visual properties in the Layout page.
Figure 19.12 Add data page.
Figure 19.13 Add data from server page.
Figure 19.14 Report settings page.
Figure 19.15 Save mobile report as page.
Figure 19.16 Time navigator Data properties panel.
Figure 19.17 Select Country Selection list Data properties.
Figure 19.18 Bike Sales Number gauge Data properties.
Figure 19.19 Bike Daily Sales chart Data properties.
Figure 19.20 Bike Daily Sales chart Visual properties.
Figure 19.21 Map Selection list.
Figure 19.22 Selecting shape and data files for custom maps.
Figure 19.23 Sales by Country map Data properties.
Figure 19.24 Functionally complete mobile report.
Figure 19.25 Report after color palette selection.
Figure 19.26 Using the Select Country Selection list.
Figure 19.27 Completed main desktop layout of the mobile report, testing map interactions.
Figure 19.28 Testing chart interactions.
Figure 19.29 Empty phone layout.
Figure 19.30 Completed phone layout.
Figure 19.31 Testing the phone layout in preview.
Figure 19.32 Command prompt with IPCONFigure results.
Figure 19.33 Power BI mobile app on an iPhone.
Figure 19.34 Steps to navigate to report server content on phone app.
Figure 19.35 Steps to navigate mobile report in phone app.

Chapter 20: Advanced Mobile Report Solutions

Figure 20.1 Report shell showing control placement.
Figure 20.2 Report datasets.
Figure 20.3 Selection list “Years” Data properties.
Figure 20.4 Selection list “Categories” Data properties.
Figure 20.5 Data grid columns and Chart properties.
Figure 20.6 Completed Data properties for “Subcategory Sales Monthly Trend” Chart data grid control.
Figure 20.7 Chart data grid control Layout properties.
Figure 20.8 Report preview.
Figure 20.9 Data properties for “Subcategory Sales Monthly trend” Chart data grid.
Figure 20.10 Phone preview.
Figure 20.11 Shared dataset parameter properties.
Figure 20.12 Completed report ready for deployment.
Figure 20.13 Chart data grid Visual properties.
Figure 20.14 Report parameters page.
Figure 20.15 Drillthrough reports.
Figure 20.16 Simple Order Details report.
Figure 20.17 Chart data grid drillthrough property.
Figure 20.18 Drillthrough URL options.
Figure 20.19 Mobile and paginated report drillthrough.
Figure 20.20 Example reports with all available maps.
Figure 20.21 Example world countries map

Chapter 21: Content Management

Figure 21.1 Web portal Favorites view.
Figure 21.2 Web portal Browse view.
Figure 21.3 View and Visibility menu.
Figure 21.4 Data source properties page.
Figure 21.5 Report properties pages in web portal.
Figure 21.6 New Linked Report page.
Figure 21.7 Schedules page in Site Settings.
Figure 21.8 Schedule Properties in SSMS.
Figure 21.9 Security page in Site Settings.
Figure 21.10 Assigning roles to a user or group.
Figure 21.11 Role permission mapping in SSMS.
Figure 21.12 Confirm breaking security inheritance.
Figure 21.13 Edit Security page.
Figure 21.14 Branding package file contents.
Figure 21.15 Branding page.
Figure 21.16 Brand package color example report.
Figure 21.17 Some example brand elements.

Chapter 22: Server Administration

Figure 22.1 Configuration Manager Service Account page.
Figure 22.2 Report Server Database Configuration Wizard.
Figure 22.3 Configuration Execution Account page.
Figure 22.4 SSMS Report Server Roles.
Figure 22.5 Configuration Manager Database page.
Figure 22.6 Configuration Manager Encryption Keys page.
Figure 22.7 Server Properties Logging page.
Figure 22.8 Web Service URL and the ReportServer web page.
Figure 22.9 Web Portal URL page and the web portal
Figure 22.10 Server Properties dialog.

List of Tables

Chapter 1: Introducing Reporting Services

Table 1.1 Report Designer and Visualization Options

Chapter 3: Reporting Services Installation and Architecture

Table 3.1 Command-Line Utilities
Table 3.2 Web Service Endpoints
Table 3.4 SSRS configuration files
Table 3.5 Included SSRS rendering extensions
Table 3.6 ReportServer database tables
Table 3.7 ReportServerTempDB database tables

Chapter 6: Grouping and Totals

Table 6.1 Expression Placeholders

Chapter 7: Advanced Report Design

Table 7.1 HTML tags supported with embedded formatting
Table 7.2 Aggregate functions supported in report expressions
Table 7.3 Special-use dataset row functions
Table 7.4 Matrix cell field mapping

Chapter 8: Graphical Report Design

Table 8.1 Chart Type Categories

Chapter 12: Expressions and Actions

Table 12.1 Visual Basic Functions for Report Expressions

Chapter 14: Report Solutions, Patterns, and Recipes

Table 14.1 Common limitations and design alternatives

Chapter 15: Integrating Reports into Custom Applications

Table 15.1 SharePoint “rv” URL Parameters
Table 15.2 Web service “rs” URL Parameters
Table 15.3 Command Parameter Values
Table 15.4 Rendering Format Parameters
Table 15.5 Report Execution Web Service Parameters

Chapter 16: Extending Reporting Services

Table 16.1 Data Processing Extension Objects
Table 16.2 Data Processing Extensions Required Interfaces
Table 16.3 Data Processing Extensions Optional Interfaces

Chapter 20: Advanced Mobile Report Solutions

Table 20.1 Data Grid Chart Properties
Table 20.2 MapShapes Table Summary

Chapter 21: Content Management

Table 21.1 Home Folder Setting Options
Table 21.2 Application Downloads
Table 21.3 Email Subscription Delivery Options
Table 21.4 Fixed Role Permissions
Table 21.5 Task-Level Permissions
Table 21.6 User Item-Level Roles
Table 21.7 RS Utility Parameter Switches

Chapter 22: Server Administration

Table 22.1 Service and Account Types
Table 22.2 Databases and Roles
Table 22.3 System Roles and Task Permissions
Table 22.4 Configuration Files
Table 22.5 Log Configuration Settings
Table 22.6 Trace Log Fields
Table 22.7 Reporting Services Performance Counters
Table 22.8 Reporting Services Windows Service Counters
Table 22.9 Recommended Windows System Counters

Microsoft^® SQL Server^® 2016 Reporting Services and Mobile Reports

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Published simultaneously in Canada

ISBN: 978-1-119-25835-3

ISBN: 978-1-119-25838-4 (ebk)

ISBN: 978-1-119-25836-0 (ebk)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2016954844

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.

ABOUT THE TECHNICAL EDITOR

NIGEL PETER SAMMY is a Microsoft Data Platform Most Valuable Professional (MVP) with over 15 years of technical experience including 12 years of database and SQL Server experience. He currently works at SoftwareONE as a Senior Data Platform Engineer where his responsibilities include consulting, solution design and implementation, hands-on training, and pre-sales. Besides working at SoftwareONE, he is also a Lecturer at the School of Business and Computer Science (SBCS) where he teaches the Microsoft Certified Solutions Associate (MCSA): SQL Server certification as well as other BSc courses for the University of London and University of Greenwich.

Nigel previously worked at Microsoft as an Account Technology Strategist (ATS) where he was responsible for providing pre-sales technical/architectural support for over 200 agencies in both the commercial and public sectors. As an ATS he delivered technical presentations and proofs of concept using SQL Server, Azure, Power BI, and Office 365. Nigel was also a Data Platform Architect (DPA) at SolidQ, a global provider of advanced consulting, mentoring, and education solutions for the Microsoft Data, Business Intelligence, Collaboration, and Development platforms. Microsoft and SolidQ have given him at least five years' working experience with large international companies.

He has progressed through other roles in his career including Application Developer, Analyst, Database Administrator, Database Developer, Project Manager, Architect, Team Lead, and Manager. In 2010, Nigel co-founded the Trinidad and Tobago SQL Server User Group (TTSSUG), a volunteer, independent, non-profit organization providing a community for Microsoft SQL Server professionals, hobbyists, and enthusiasts. Nigel is a co-author of Microsoft's SQL Server 2012 Upgrade Technical Guide and a technical editor of Wrox's Professional Microsoft SQL Server 2012 Reporting Services book. For the last eight years he has been presenting on data platform topics for conferences locally and internationally. When he gets extra time, he blogs at www.nigelpsammy.com.

ACKNOWLEDGMENTS

My endless appreciation and gratitude goes to the co-authors and contributors of the four previous editions of this book series over the past 13 years. As technical reviewer, Nigel Sammy worked tirelessly to test and research the 2016 product as it was readied for release, and to make sure we were current, complete, and accurate. This product continues to be a moving target, and Nigel went far above and beyond any reasonable expectation.

I appreciate my 2012 edition co-authors who helped refresh and update material for the new product version. Thanks go to Grant Paisley, Thiago Silva, and Robert Bruckner for your revisions and direction. Tom Dinse, thank you for your patience and persistence through this hardscrabble effort. Riccardo Muti and Chris Finlan from the product team, thanks for sewing the monster together and once again giving him life. Seriously, thanks for the direct product team access and on-going support for this set of marvelous tools.

FOREWORD

Riccardo Muti

Group Program Manager, SQL Server Reporting Services
Microsoft

On a mild Seattle day in 2010, I headed to the Microsoft campus to start my new job working on the SQL Server Reporting Services product. Once there, I learned that my computer would take a few more days to arrive. What was I supposed to do for a few days without a computer? My manager handed me a book and said, “Read this.” That book was Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports by Paul Turley and Robert Bruckner. For those few days, I could do nothing but get to know my teammates and pore over that book. By the time my laptop arrived, I was primed to put everything I'd studied into practice. It's no exaggeration, then, to say that I've been learning Reporting Services from Paul's books since my first day on the Reporting Services team at Microsoft.

Long after I'd received my laptop, I kept that book on my desk, referring to it often as I worked to deepen my report design expertise. Whether I was trying to figure out the best way to design a multi-lingual report or to pass a multi-value parameter into a stored procedure, one of the many recipes in that book held the answer. Many people have turned investments in learning Reporting Services into rewarding careers, in no small part with the help of Paul's many books on the topic, which expertly guide readers on a journey from foundational knowledge to the most advanced techniques. Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports carries on that tradition. A must-read for novices and experts alike, it covers everything from setting up a report server to designing sophisticated reports to crafting enterprise solutions to optimizing for today's mobile devices.

Last year, after a few years working on other parts of Microsoft's Business Intelligence offering, I had the opportunity to rejoin the Reporting Services team and revitalize the product that hadn't been updated in a few years. A year of a team's hard work later, SQL Server 2016 Reporting Services, the product of an ambitious overhaul, offers a modern enterprise reporting platform, including a nifty mobile dashboard solution. What has powered the widespread adoption of Reporting Services goes beyond the product itself to the community that has flourished around it, thanks in large part to leaders such as Paul. In reading this book, you're participating in that community and learning from a master. If you'd asked me where to start learning Reporting Services, I would've handed you none other than this book. Enjoy!

FOREWORD

Christopher Finlan

Senior Program Manager
SQL Server Reporting Services

“So, is Reporting Services dead?”

That question, and my non-answer, was usually the extent of my conversations with my Microsoft customers when it came to SQL Server Reporting Services. You see, prior to joining the product team, I was part of the pre-sales team at Microsoft. My entire job was to get customers excited about the latest tools and products we had in the Microsoft Business Intelligence Suite. This meant we were generally avoiding the topic of SQL Server Reporting Services, which looked dated and hadn't seen much added to it in the last few years. Instead, I was either talking about Power BI or about a little-known Microsoft partner called Datazen, which had these amazing mobile dashboards that looked beautiful on every device you consumed them on.

Fast forward to today, and much has changed in these last 12–18 months. Nowadays, customers can't get enough information about what's new in SQL Server Reporting Services. In fact, it's often the first thing customers want to talk about when it comes to Microsoft Business Intelligence. The SQL Server 2016 release saw the product transformed, bringing a modern look and feel along with all the mobile capabilities previously available in the standalone Datazen product. Combine this with the planned improvements already communicated on the Microsoft BI roadmap, and it's an incredibly exciting time to be a part of the Reporting Services product group. Just seeing our customer's excitement at what's been delivered and what's still to come is incredibly gratifying to all of us.

But this same excitement also brings with it an entirely new set of challenges—many of you reading this book may never have touched Reporting Services before for the reasons mentioned earlier. Now that you've seen what you have available as part of your SQL Server investment, you're looking to unlock it and don't know where to start. Or perhaps you've used SSRS in the past, but want to dive into creating mobile reports and KPIs for the first time. Regardless of why you are here now, be glad you are. There are few people better equipped to navigate you through what you can accomplish with Reporting Services than the author of this book, Paul Turley.

For over a decade, Paul's books on SQL Server Reporting Services have become “the SSRS bible” for thousands of SQL Server report developers every day. His writing style makes it easy for anyone to understand the wide-ranging topics he covers, from report design to server administration. There's even a copy of one of his books in our team room. I know this because I've picked it up and used it on more than one occasion. And I'm looking forward to adding this version to our collection, as you all have already.

Thanks to Paul and all of you for being a part of the Reporting Services community, and enjoy the book!

PROFESSIONAL
Microsoft^® SQL Server^® 2016
Reporting Services and
Mobile Reports

ABOUT THE AUTHOR

ABOUT THE TECHNICAL EDITOR

CREDITS

ACKNOWLEDGMENTS

FOREWORD

FOREWORD