SQL Server 2014 / 2012 / 2008 // BI779

SQL Server 2012 Reporting Services (SSRS) for Report Developers

This 4-day course is for designed for report developers using Microsoft SQL Server 2008 R2 and SQL 2012 Reporting Services (SSRS). Learn how to build reports in Report Builder and Report Designer

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. Self-service reporting capabilities are covered as well. This course helps people prepare for Microsoft Exam 70-466.

AUDIENCE

This course is intended for report developers who need to implement reporting solutions by using Microsoft SQL Server 2008 R2 and SQL Server 2012 Reporting Service

PREREQUISITES

Before attending this course, students must have:

  • Exposure to creating reports in Microsoft Access or other third-party reporting products, such as Crystal Reports.
  • Conceptual understanding of the push and pull distribution/subscription paradigm.
  • Experience navigating the Microsoft Windows Server environment.

In addition, it is recommended, but not required that students have completed course:
SQL100: Introduction to Transact-SQL

WHAT YOU WILL LEARN

After completing this course, students will be able to:

  • Describe SQL Server Reporting Services and its components.
  • Create a Reporting Services report.
  • Enhance a Reporting Services report.
  • Create and manipulate data sets.
  • Incorporate multi-dimensional data in a report
  • Configure report publishing to native and Sharepoint integrated mode report servers
  • Implement subscriptions and data alerts for reports.
  • Create and use report parts in a self-service reporting scenario
  • Understand and Implement reporting security

COURSE OUTLINE

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

  • Lookup Functions

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