SQL Server Reporting Services

Course Overview

Introduction

• Prerequisites
• Installing the Practice Files
  • Software Requirements
  • Microsoft Sample Databases
  • Installation
• About the Author

Introducing SQL Sever Reporting Services

• Tour of Features
  • Feature Overview
  • Supported Report Types
  • Supported Output Formats
  • Data Sources
  • Programming Reporting Services
  • Deployment and Administration Features
• What's New in SQL Server 2008 Reporting Services
• Reporting Services Architecture
  • Report Server
  • Report Server Database
  • Report Designer
  • Report Manager
  • Reporting Services Configuration Tool
  • Report Server Command-Line Utilities
  • Report Builder
  • Report Models
• Reporting Services and SharePoint
  • Why Use SharePoint Integrated Mode?
• Report Server Projects
  • Creating a Report Server Project
  • Report Server Project Files
• Creating Data Sources
  • Creating a Shared Data Source
• Creating Reports
  • Using the Report Wizard
  • Importing Microsoft Access Reports
  • Creating a New Blank Report
  • Using the Visual Studio Report Designer
  • The Report Dataset
  • The Report Design
• Running Reports
  • Report Execution Options
  • Output Options

Lab 1: Introducing SQL Server Reporting Services

• Lab 1 Overview
• Create a Report Using the Report Wizard

Creating Tabular and List Reports

• Creating Your First Tabular Report
• Working with Datasets and Data Regions
  • Adding a Dataset to a Report
  • Adding a Data Region to a Report
  • Associating a Data Region with a Dataset
• Adding Controls to a Report
  • Adding Fields to a Data Region
  • Working with Other Report Item Types
• Accessorizing Reports
  • Is It a Table or a Tablix
  • Adjusting Tablix Properties
  • Adding Page Headers and Footers to the Report
  • Adjusting Report Properties
  • Adding Rows and Columns to a Tablix Region
  • Merging and Splitting Cells
• Creating a List Report
• Working with Rich Text
  • Rich Text

Lab 2: Creating Reports

• Lab 2 Overview
• Create a Shared Data Source
• Create a Report from Scratch
• Create a Mail Merge Report with Rich Text

Calculations and Formatting

• Creating Expressions
  • Expression Rules
  • Using the Expression Editor
  • Mathematical Calculations
  • String Expressions
  • Conditional Tests
  • Null Handling
  • Using Other Built-In Functions
  • Creating and Using Custom Functions
• Using the Global Collections
  • Fields Collection
  • Globals Collection
  • User Collection
  • Parameters Collection
  • ReportItems Collection
  • Variables Collection
• Formatting Items
  • Formatting Text Boxes Using the Toolbars
  • Formatting Text Boxes Using the Properties Window
  • Formatting Text Boxes Using the Text Box Properties Dialog Box
• Conditional Formatting
  • Conditional Formatting and Rich Text

Lab 3: Calculations and Formatting

• Lab 3 Overview
• Reigning in a Long Text Field
• Calculating the Price of Books
• Highlighting Large Book Sales

Grouping and Sorting

• Creating Groups
  • The Grouping Pane
  • The Details Group
  • Adding a Row Group
  • Adding an Adjacent Row Group
  • Editing Group Properties
  • Adding Group Headers and Footers
• Calculating Totals and Percentages
  • An Example: Counting Rows in rptCustomersGrouped
  • An Example: Summing Sales in rptEmployeeSales
  • Scope
  • Calculating Percentages
• Interactive Sorting
  • An Example: rptCustomersIntSort
  • An Example: rptCustomersIntSort2
• Creating Drill-Down Reports
  • A Drill-Down Example

Lab 4: Grouping and Sorting

• Lab 4 Overview
• Create a Report with Nested Groups
• Create a Report with Percentages
• Create a Drill-Down Report

Working with Parameters

• Creating Report Parameters
  • Parameters and Stored Procedures
• Understanding Report and Dataset Parameters
  • Report Parameters
  • Dataset Parameters
  • Reordering Report Parameters
  • Displaying Parameter Values on the Report
  • Using Parameters with Ad-Hoc SQL
  • Using Unbound Parameters
• Creating Drop-Down List Parameters
  • Displaying the Parameter Value and Label for a Drop-Down List Parameter
  • Cascading Parameters
• Parameterized Sorting
• Multivalued Parameters
  • Creating a Report Employing a Multivalued Parameter
  • Referencing the Multivalued Parameter Values in a Heading
• Debugging Parameter Issues

Lab 5: Working with Parameters

• Lab 5 Overview
• Create a Parameterized Report
• Create a Drop-Down List Parameter
• Work with Multivalued Parameters

Creating Matrix Reports and Charts

• Creating a Matrix Report
  • Basic Steps to Create a Matrix Report
• Accessorizing Your Matrix Reports
  • Available Summary Statistics
  • Working with Row and Column Groups
  • Working with Totals
  • Adding Data Cells
• SQL Server 2008 Matrix Features
  • Adding Adjacent Dynamic Columns to a Matrix
  • Adding Adjacent Static Columns to a Matrix
  • Adding a Percentage to a Matrix Report
• Creating a Chart
  • Basic Steps to Create a Chart
  • SQL Server 2008 Chart Changes
  • Chart Anatomy
  • Formatting a Chart
  • Adding a Series to a Chart
• Exploring the Charting Possibilities
  • Chart Talk
  • Chart Types
  • Column Charts
  • Line Charts
  • Shape Charts
  • Bar Charts
  • Area Charts
  • Range Charts
  • Scatter Charts
  • Polar Charts
  • Charting Tips
• Adding Gauges to a Report
  • Basic Steps to Creating a Gauge Report
  • Gauge Anatomy
  • Adding a Gauge to an Existing Report

Lab 6: Creating Matrix Reports and Charts

• Lab 6 Overview
• Create a Matrix Report with Subtotals
• Create a Stacked Column Chart
• Create a Pie Chart with Point Labels
• Add a Gauge to an Existing Matrix Report

Managing Reporting Services

• Deploying Reporting Services Projects
  • Planning for Deployment
  • Setting the Active Configuration
  • Creating a Custom Configuration
  • Configuring the Report Project
  • Deploying a Reporting Services Project
  • Deploying Individual Reports and Data Sources
  • Redeploying Shared Data Sources
• Exporting Reports and Printing
  • Exporting Reports
  • Client-Side Printing
• Report Manager
  • Displaying or Hiding Report Manager Details
  • Navigating Around Report Manager
  • Running Reports
  • Managing Folders and Files
  • Managing Reports and Data Sources
  • Managing Generic Site Settings
  • Other Things You Can Do with Report Manager
• Creating Linked Reports

Lab 7: Managing Reporting Services

• Lab 7 Overview
• Deploy a Report to the Local Reporting Services Server
• Export a report to PDF and Excel Formats
• Create Linked Reports

Security

• Reporting Services Security
• Securing Access to Reports
  • Authorizing Users at the Home Level
  • Authorizing Users at Project Level
  • Managing Reporting Services Roles
• Reporting Services and Basic Authentication
• Reporting Services and Secure Sockets Layer (SSL)
  • Enabling SSL
  • Requiring SSL
• Data Source Security
  • Managing Data Source Security from Visual Studio
  • Four Choices for Managing Credentials
  • Which SQL Server Permissions Are Needed for a Report?
  • Managing Data Source Security from Report Manager

Lab 8: Security

• Lab 8 Overview
• Set Up Report Server Security Using Report Manager
• Managing Data Source Security

Programming Reporting Services

• The Reporting Services APIs
• URL Access
  • Executing Reports via URLs
  • Integrating a Report into a Web Application Using URL Access
• Using the Report Viewer Controls
  • Server vs. Local Reports
  • Using the Web Report Viewer Control with Server Reports
  • Using the Windows Report Viewer Control with Server Reports
• Using the Report Viewer Control with Local Reports
  • Using the Web Report Viewer Control with Local Reports
  • Using the Windows Report Viewer Control with Local Reports

Lab 9: Programming Reporting Services

• Lab 9 Overview
• Integrate a Reporting Services into a Web App Using URL Access
• Employ the Server Report Viewer Control in a Web App
• Employ the Local Report Viewer Control in a Web App

Advanced Reporting Services Programming

• Using the Reporting Services Web Service
  • A Tale of Two Endpoints
  • Why Use the Web Service?
  • Why Not Use The Web Service API?
  • Using the Report Server Web Service
  • An Example: The CallReportServiceVB.aspx and CallReportServiceCS.aspx Pages
  • A Second Example: Programmatically Creating Subscriptions
• Working with Custom Assemblies
  • What's Wrong with the Code Window
  • Creating a Custom Assembly
  • A Custom Assembly Example
  • Security and Custom Assemblies

Lab 10: Advanced Reporting Services Programming

• Lab 10 Overview
• Call the Reporting Services Web Service
• Create and Deploy a Custom Assembly

Snapshots and Subscriptions

• Report Caching and Snapshots
  • Setting Up the NWReportUser Login and User Account
  • Setting the NWSub Credentials
  • Report Caching
  • Report Snapshots
  • Report History
• Creating Subscriptions
  • Parts of a Subscription
  • Creating a Standard Subscription
• Creating Data-Driven Subscriptions
  • Creating the Data-Driven Query
  • A Data-Driven Example

Lab 11: Snapshots and Subscriptions

• Lab 11 Overview
  • Important Lab Setup
• Enable Caching for a Report
• Create a Snapshot with History
• Create a File-Share Standard Subscription

Ad-Hoc Reporting with Report Builder 2.0

• A Tale of Two Report Builders
  • Report Builder 1.0
  • Report Builder 2.0
  • Which Report Builder Version to Use?
• Report Builder 2.0 Setup and Configuration
  • Launching Report Builder 2.0
  • Report Manager Link
  • Report Builder Data Sources
  • Creating the Login, Shared Data Sources, and Folder Used in the Chapter Samples
• Getting Started with Report Builder 2.0
  • Using the new Table or Matrix Wizard
  • Using the New Chart Wizard
  • Is That All There Is?
• Creating Reports with Report Builder
  • Constructing a Report
  • Report Builder Tour
  • Creating a Tabular Report
  • Adding a Group to a Report
  • Creating Matrix Reports
  • Adding Totals to a Report
  • Creating Charts

Lab 12: Ad-Hoc Reporting with Report Builder 2.0

• Lab 12 Overview
• Create a Matrix Report Using the Wizard
• Create a Tabular Report
• Create a Pyramid Chart

Appendix A: Creating Report Model Projects

• Creating Report Model Projects
  • Basic Steps to Creating a Report Model
  • Creating a Project
  • Adding a Data Source to a Report Model Project
  • Creating Data Source Views
  • Generating Report Models Using the Report Model Wizard
  • The Generated Model
  • Refining and Enhancing the Report Models
• Deploying Report Model Projects
  • Deploying Report Models
  • Creating Reports on Report Models

Prerequisites

This course assumes no prior knowledge of SQL Server Reporting Services. This course does assume prior knowledge of SQL Server and the use of SQL Server Management Studio for development, Visual Studio using Visual Basic and administrative tasks. The user should also be able to write and run expressions, script files and diagnose problems that occur.

Software/Setup For this Class

SQL Server 2012 or higher.