Designing BI Report Solutions with SQL Server 2012 Reporting Services and SharePoint 2010


Apply effective design techniques and best practices to meet business reporting requirements. Learn about business intelligence solutions, dashboards and analytical reports deployed to an integrated SharePoint business portal environment.


Duration: 40 hours

Level: 300


  • Define, articulate and document business report requirements
  • Use Report Builder and SQL Server Data Tools to design reports and report solutions
  • Create data sources and datasets to retrieve data from business data stores
  • Use fundamental T-SQL and MDX queries to retrieve data
  • Filter data using a query or dataset filter definition
  • Design basic report styles including tables, lists, matrices and charts
  • Group, sort and summarize data using report data regions
  • Enable business user solutions by planning a self-service reporting solution for users to design their own reports, simply, using report parts and shared objects
  • Define report layout and formatting elements to present information
  • Build dynamic drill-down and drill-through reports using report actions
  • Use advanced report design techniques to present complex data
  • Use parameters and expressions to create a dynamic reporting experience
  • Design analytical reports based on semantic data models and OLAP cubes
  • Apply industry standard design patterns and best practices
  • Use shared data sources, shared datasets and report parts
  • Design and build BI dashboards using KPI indicators, sparklines and gauges


  • Use maps to visualize and analyse geographic and spatial data
  • Deploy and manage reports to test, and production environments
  • Manage report folders and document libraries
  • Integrate reports into SharePoint document libraries
  • Understand and articulate report management and security requirements
  • Define and manage a self-service reporting solution using Report Builder
  • Enable user data discovery and insights with PowerPivot, BI semantic tabular models and Power View
  • Use subscriptions, snapshots and cached report instances to define business report solutions


What does this course cover?

Developed in-house by SolidQ, the Reporting Services course will teach you to apply industry design patterns and best practices to create business reports and reporting solutions based on years of field experience with the product in a variety of industries and business scenarios. You will learn to avoid common pitfalls and to apply effective design techniques to meet business requirements. The Reporting Services course is focused on business intelligence solutions, dashboards and analytical reports deployed to an integrated SharePoint business portal environment.

SQL Server Reporting Services is the industry-standard reporting tool from Microsoft, available in the SQL Server product suite. It can be used to report on data from many different data sources and database platforms, for operational, business intelligence and analytical reporting. Many of the techniques and practices taught in the reporting Services course can be applied to a variety of reporting styles and environments with and without SharePoint integration.

Who is this course designed for?

  • Business users and IT professionals who need to design business reports to support operational or analytical reporting needs
  • Report designers
  • IT professionals and developers who want to design and manage reports that may be used in integrated applications and enterprise reporting solutions
  • Professionals with prior Reporting Services experience who wish to learn the new capabilities introduced in the new version

Pre-requisites: What do you need to know?

  • Proficiency in using Windows and business productivity software, common Windows dialogs and controls
  • Being able to use Windows Explorer to manage files and navigate a folder structure.
  • Proficiency in using Internet Explorer to navigate hyperlinks and Internet addresses
  • Proficiency in using Microsoft Windows, Word and Excel
  • Advanced skills with Visual Studio and SQL Server are helpful but not required
  • Knowledge of fundamental query design and data manipulation is not required

”Rubén provided us with very valuable information based on his own experience”

WHEN and WHERE is this course running?

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

Module 01: Report Solutions & Architecture
  • SQL Server Reporting Services (SSRS) architecture at a high level
  • Introduction to databases and semantic models
  • SSRS in SharePoint
Module 02: Basic Report Design
  • Report design tools overview
  • Report builder
    • Connecting to data sources
    • Basic report design structure
    • Using the report wizards

Lab 02A: Using the Matrix Wizard to Design a Basic Report

  • Basic report formatting

Lab 02B: Manually Designing a Matrix Report Using Report Builder

  • SQL Server Data Tools

Lab 02C: Creating a SQL Server Data Tools Report Project  

Module 03: Report Layout & Formatting
  • Essential components: Data sources, datasets, and data regions
  • Table design
  • Groups and headers
  • Formatting numeric measures
  • Aggregates and totals
Module 04: Designing Data Access
  • Shared and embedded data sources
  • Database objects
  • T-SQL query basics
  • Query grouping and aggregation

Lab 04A: Understanding Query Design Essentials

  • Filtering and parameters

Lab 04B: Using Parameters

  • Advanced parameterization
Module 05: Chart Report Design
  • Chart essentials
    • Series values and aggregation
    • Category and series groups
  • Chart design environment
  • Chart type criteria
  • Chart object anatomy

Lab 05: Creating Advanced Charts

Module 06: Groups and Totals
  • Report body, headers, and footers
  • Totals and subtotals

Lab 06A: Using Headers, Footers, and Totals

  • Aggregate and specialized functions
  • Aggregate scope
  • Nested report items

Lab 06B: Creating a Nested Gauge Report

  • Recursive group levels

Lab 06C: Reporting on Recursive Relationships  

Module 07: Expressions and Custom Logic
  • Using properties and expressions
  • Introducing the Expression Builder
  • Using expressions and parameters to customize report behavior
  • Introducing custom Visual Basic functions

Lab 07A: Using Basic Expressions

  • Using advanced report programming

Lab 07B: Using Custom Code in a Report

  • Developing and using a custom assembly

Lab 07C: Using a Custom Assembly

Module 08: Dynamic Reports and Actions
  • Dynamic visibility and drill-down reports

Lab 08A: Creating Drill-Down Reports

  • Document maps and bookmarks
  • Links and drill-through reports

Lab 08B: Creating Links and Drill-Through Reports

  • URL actions
  • Subreports

Lab 08C: Federating Data with a Subreport

  • Conditional rendering
  • Dynamic groups
  • Column visibility
Module 09: Semantic Models and Analytical Data Sources
  • Introduction to dimensional data modeling
  • Microsoft Business Intelligence and the BI Semantic Model
  • PowerPivot for Excel
  • PowerPivot for SharePoint
  • Tabular BI Semantic Models
  • Migrating PowerPivot to a tabular model
  • The SSDT tabular model designer

Lab 09: Using PowerPivot and Tabular Models for Reporting  

  • Getting started with Power View
  • Using tabular data sources in SSRS
Module 10: Multidimensional Reporting with SSAS and MDX
  • Basic MDX report design
  • Graphical MDX query designer
  • Manual query modification
  • Report design provisions
  • MDX essentials

Lab 10A: Creating a Report with a Manually Written MDX       

  • Dynamic parameter queries
  • Dynamic management views (DMVs)

Lab 10B: Adding Parameters and Dynamic Query Logic

Module 11: Map Report Design and Geospatial Reporting
  • Map report basics
  • Using the map wizard
  • Working with spatial data
  • Shape layers and point layers
  • Color scales, callouts, and point labels
  • Bing Maps layers
  • Geocoding and geospatial queries
  • Map design techniques and design patterns

Lab 11: Building a Multilayer Geographic Map Report

Module 12: BI Dashboard Design
  • Dashboard concepts
  • The art of dashboard design – examples, patterns, and expert recommendations
  • Using and reporting KPIs
  • Gauges and bullet graphs
  • Scorecards and indicator sets

Lab 12A: Designing a KPI Business Scorecard

  • Sparklines

Lab 12B: Building an Interactive Sparkline  

Module 13: Enabling Report Delivery and Self-Service Reporting
  • Reporting in the Cloud
  • Planning a self-service reporting solution
  • Report content planning
  • Organizing reports
  • Report ownership
  • Report execution options
  • Automated delivery options
    • Subscriptions, alerting, and delivery options

Lab 13A: Configuring the Environment

  • Enabling self-service

Lab 13B: User Reporting Experience

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.