Course Description
This 4 day course is specifically designed for report developers using SQL Server 2008 R2 and SQL Server 2012 Reporting Services. It will provide report designers the skills necessary to design and implement various types of reports using both the Visual Studio-based Report Designer and Report Builder 3.0. This course also includes: data visualizations (i.e., sparklines, data bars, maps), MDX data access, data and report caching, subscriptions and report deployment to both native mode and Sharepoint integrated mode reporting services.
Agenda
Module 1 - Introduction to SQL Server Reporting Services
Lesson 1
- Overview of Reporting
- Reporting Scenarios
- Highlights for Reporting Services
Lesson 2
- Reporting Services Architecture
- Reporting Services Modes
- Native Mode Architecture
- Sharepoint Integrated Architecture (SQL Server 2008 R2)
- Sharepoint Integrated Architecture (SQL Server 2012)
- SQL Server Azure Reporting
Lesson 3
- Anatomy of a Report
- What is a Report?
- Report Definition Language
- Components of a Report
- Report Rendering Formats
Lesson 4
- Reporting Services Development Tools
- Reporting Roles and Authoring Tools
- SQL Server Data Tools Report Designer Interface
- Report Builder 3.0
Module 1 Lab
Exercise 1 - Create a Reporting Project with SQL Server Data Tools
Exercise 2 - Create a Matrix Report with Report Builder Wizard
Module 2 - Authoring Basic Reports
Lesson 1 - Tablix
- Tablix Data Regions
- What is a Table?
- Creating a Table
- What are Groups?
- Add Groups to a Tablix Data Region
- Displaying Aggregate Values
- Using Dynamic Visibility
- Enabling Interactive Drilldown Functionality
Lesson 2 - Formatting Report Pages
- Report Page Options
- Report Items
- Textbox Rotation
- Headers and Footers
- Including Images in a Report
- Configuring Page Breaks for Groups
Lesson 3 - Calculating Values
- Creating Custom Fields
- Aggregate Functions
- Common Aggregate Functions
- Aggregations of Aggregations
- Creating a Placeholder
- Collections
Lesson 4 - Enhancing Basic Reports
- Creating Document Maps
- Enabling Interactive Sorting
- Conditional Formatting
Module 2 Lab
Exercise 1 - Create a Basic Table Report
Exercise 2 - Formatting Report Pages
Exercise 3 - Adding Calculated Values
Exercise 4 - Providing User Control
Module 3 - Using Advanced Data Regions
Lesson 1 - Matrix Data Region
- Working with Matrices
- Tablix: Mixing Tables and Matrices
- Tablix Flexibility
Lesson 2 - Chart Data Region
- Components of a Chart
- Working with Charts
Lesson 3 - List Data Region
- Working with Lists
- Creating multi-column reports
Lesson 4 - Subreports
- Subreports
- Linking to a Subreport
Module 3 Lab
Exercise 1- Creating a Matrix report
Exercise 2 - Enhancing a Matrix Report
Exercise 3 - Creating a chart report
Exercise 4 - Using a Subreport
Exercise 5 - Creating Product Labels using a List data region
Module 4 - Visualizing Data
Lesson 1 - Data Visualizations
- Showing Key Values with Gauges
- Summarizing Data with Data Bars and Sparklines
- Creating Sparkline and Data Bars
- Aligning Sparklines and Data Bars
- Using Indicators
- Indicator Measurement Units
Lesson 2 - Displaying Geographical Data
- Using Maps to Display Geographic Data
- Overview of Spatial Data
- Maps and Map Layers
- Map Layer Types
- Map Layer Data
- What is a Bing Map?
Module 4 Lab
Using Indicators
Using Data Bars and Sparklines
Module 5 - Manipulating Data Sets
Lesson 1 - Data Sources and Data Sets
- Connecting to a Data Source
- Using Shared Data Items
- Creating Shared Data Sources
- Creating Shared Data Sets
Lesson 2 - Filter Reports By Using Parameters
- Overview of Parameters
- Using Query Parameters
- Add Parameters to a Report
- Configuring Parameters
- Specifying Available and Default Parameter Values
- Adding Parameters to a Stored Procedure
- Working with Parameter in an Expression
- Using Filters
Lesson 3 - Parameter Value Display
- Data-bound Parameters
- Dynamic Parameter Lists
- Creating a Multi-Valued Parameter List
Lesson 4 - Accessing an Analysis Services Cube
- Creating an Analysis Services Data Source
- Connection String properties
- Creating an MDX data set
- Adding parameters to an MDX data set
Lesson 5 - Combining data from multiple data sets in a data region
Module 5 Lab
Exercise 1 - Use Parameters to Restrict Query Results
Exercise 2 - Use Parameters to Filter Report Data
Exercise 3 - Creating Dynamic Parameter Lists
Exercise 4 - Using Parameters with a Stored Procedure
Module 6 Publish and Execute Reports
Lesson 1 - Report Publishing
- Publishing with Report Designer
- Reporting Services Project Properties
- Publishing with Report Builder
- Publishing to Native Mode with Report Manager
- Updating Data Sources
- Updating Reports
Lesson 2 - Report Execution
- Execution Process Flow
- Viewing a Report
- Execute Reports on Demand
- Linked Reports
Lesson 3 - Creating Cached Instances
- Optimizing Performance with Caching
- Configuring Data Source Credentials
- Executing Cached Instances
- Using Query Parameters with Cached Instances
- Using Filters with Cached Instances
- Configuring Cached Instances
Lesson 4 - Creating Snapshots and Report History
- Using Report Snapshots
- Executing Snapshot Reports
- Using Query Parameters with Snapshots
- Using Filters with Snapshots
- Configuring Snapshots
- Using Report History
- Configuring Report History
- Cached Instances vs. Report Snapshots
Module 6 Lab
Exercise 1 - Publishing Reports to a Native Mode Environment
Exercise 2 - Publishing Reports to Sharepoint Environment
Exercise 3 - Executing a Report on Demand
Exercise 4 - Configuring and Viewing a Cached Report
Exercise 5 - Configuring and Viewing a Snapshot
Module 7 - Supporting Self-Service Reporting
Lesson 1 - Why Self-Service Reporting
- Self-Service Reporting Scenarios
- Report Builder
Lesson 2 - Creating Report Parts
- What are Report Parts?
- Publishing Report Parts
Lesson 3 - Using Report Parts
- Accessing the Report Parts Gallery
- Building a Report from Report Builder
- Updating Report Parts
Module 7 Lab
Exercise 1 - Create a Report with Report Builder in Sharepoint
Exercise 2 - Create a Shared Data Source
Exercise 3 - Create a Shared Dataset
Exercise 4 - Publish the Report to the Report Part Catalog
Exercise 5 - Create a new report based on published Report Parts
Module 8 - Using Subscriptions and Data Alerts
Lesson 1 - What is a Subscription?
- Introduction to Subscriptions
- Subscription Scenarios
- Subscribing to a Report
- Creating a Data-Driven Subscription
Lesson 2 - What is a Data Alert?
- Creating a Data Alert
- Managing Data Alerts
Module 8 Lab
Exercise 1 - Implementing a Standard Subscription
Exercise 2 - Implementing a Data-Driven Subscription
Optional: Implementing a Data Alert
Module 9 Reporting Security
Lesson 1 - Report Server Security and Authentication
- Introduction to Report Server Security
- Managing Authentication
- Managing Secure Communication
Lesson 2 - Authorization
- Assigning Roles
- Managing Authorization
- Securing Items
- Working with System-Level Role Definition
- Securing the System
Module 9 Lab
Exercise 1 - Securing an Item in Native Mode
Exercise 2 - Securing an Item in Sharepoint Mode