Learn your way! Get started

SQL Server 2008: Reporting Services

with expert Paul Litwin

Course at a glance

Included in these subscriptions:

Dev & IT Pro Video
Dev & IT Pro Power Pack

	Release date	11/18/2009
	Level	Advanced
	Runtime	14h 33m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included

Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More

Course description

In this course, you will learn how to use SQL Server 2008 Reporting Services. Explore its basic architecture and gain a basic understanding of the components of SSRS 2008. You will then explore tabular and list reports that employ datasets and data regions. As you progress you will learn to add custom expressions, custom functions and format your reports using Visual Studio 2008 and SQL Server 2008 Business Intelligence Studio. The reports you create in the course will employ shared data sources, interactive sorting, and drill-down capabilities. You will then understand how Reporting Services parameters work and create parameters in ad-hoc SQL. Next you will be building Matrix reports and charts then explore the use of Reporting Services gauges.

Prerequisites

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

Learning Paths

This course will help you prepare for the following certification and exam:
MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Meet the expert

Paul Litwin is a developer specializing in ASP, ASP.NET, Visual Basic, C#, SQL Server, and related technologies. He is an experienced trainer, has written articles for MSDN Magazine and PC World, and is the author of several books including ASP.NET for Developers (SAMS) and Access 2002 Enterprise Developer’s Handbook (SYBEX). Paul is a Microsoft MVP, the chair of the Microsoft ASP.NET Connections conference, and a member of the INETA Speakers Bureau.

Course outline

Module 1

Tour of Features (27:14)

Introduction (02:56)
Tour of Features (00:24)
Feature Overview (01:19)
Supported Report Types (00:06)
Tabular Reports (00:12)
Matrix Reports (00:11)
Charts and Graphs (00:06)
Additional Report Types (00:25)
Supported Output Formats (00:28)
Data Sources (00:38)
Program Reporting Services (00:56)
Deployment - Admin Features (00:47)
What"s New in SSRS 08 Part 1 (03:06)
Demo:New Features Part 1 (01:14)
What"s New in SSRS 08 Part 2 (02:13)
Demo:New Features Part 2 (05:22)
What"s New in SSRS 08 Part 3 (01:30)
Demo:New Features Part 3 (03:45)
Summary (01:29)

SSRS Architechure (20:47)

Introduction (00:57)
SSRS Architechture (01:22)
Report Server (00:50)
Report Server Database (00:31)
Report Designer (00:58)
Report Manager (01:31)
Demo:Report Manager (03:08)
SSRS Configuration Tool (01:02)
SSRS Command-Line Utilities (00:48)
Demo:Configuration Tool (02:51)
Report Builder (00:24)
Model Designer (01:09)
Demo:Model Designer (01:04)
SSRS and SharePoint (00:32)
Why Use SharePoint Integrated (01:37)
Not Supported in Integrated (00:28)
Alternative to Integrated (00:36)
Integrated vs. Native (00:15)
Summary (00:36)

Reporting Server Projects (46:07)

Introduction (00:48)
Report Server Projects (00:35)
Creating Data Sources (01:10)
Why Use Shared Data Source (01:13)
Demo: Shared Data Source (03:44)
Ways to Create a Report (00:37)
Using the Report Wizard (00:36)
Demo:Report Wizard (09:47)
Importing Access Reports (01:40)
Demo:Importing Access Reports (02:59)
Access Features Not Imported (01:20)
Creating a New Blank Report (00:32)
Demo:Creating a Blank Report (07:41)
Using Visual Studio Designer (01:27)
Data Regions (02:39)
Mixing and Matching Regions (00:30)
Report Execution Options (01:20)
Output Options (00:30)
Demo: Execute a Report (06:00)
Summary (00:51)

Module 2

Creating Tabular Report (18:17)

Introduction (00:40)
What is a Tabular Report? (00:39)
Creating Tabular Report (00:48)
Demo: Creating Tabular Report (07:16)
Datasets and Data Regions (00:30)
Adding Dataset to a Report (00:38)
Adding Data Region to a Report (01:13)
Associating Region to Dataset (00:37)
Demo:Associating Region to Set (04:48)
Summary (01:04)

Adding Controls (30:46)

Introduction (01:00)
Adding Fields to a Data Region (01:58)
Other Report Item Types (01:22)
Static Images to Reports (01:16)
Demo: Static Images (03:42)
Bound Images to Reports (00:57)
Fixup for Bound Images (01:38)
Demo:Fixup for Bound Images (03:59)
Is it a Table or a Tablix? (00:36)
Adjusting Tablix Properties (00:26)
Repeating Table Headers (01:18)
Demo:Tablix Properties (02:54)
Adding Page Headers+Footers (00:30)
Adjusting Report Properties (01:07)
Add Rows+Columns to Tablix (00:27)
Merging and Splitting Cells (00:26)
Demo:Add, Adjusting, Merge (06:43)
Summary (00:18)

Creating List Report (38:01)

Introduction (00:48)
Creating a List Report (00:59)
Demo: List Data Region (06:44)
Working with Rich Text (00:33)
Text Box Controls (00:36)
Enable Rich text Support (00:39)
Working with Placeholders (00:21)
Formatting Rich Text (01:04)
Demo: Rich Text in SSRS (10:30)
Using HTML Rich Text (01:08)
Enabling HTML Rich Text (00:44)
Creating Rich Text HTML (00:45)
Formatting with HTML Tags (00:57)
Demo: Rich Text HTML (11:42)
Summary (00:24)

Module 3

Creating Expressions (37:25)

Introduction (00:43)
Creating Expressions (00:42)
Where to Place Expressions (01:15)
Expression Rules (00:15)
Using the Expression Editor (00:32)
Expression Editor Five Panes (02:46)
Building Code Expressions (00:26)
Expressions Using Item Panes (01:08)
Demo: Code Expressions (04:38)
Mathematical Calculations (00:40)
Demo: Math Expression (02:22)
String Expressions (00:50)
Conditional Tests (00:51)
Demo: Conditional Tests (05:06)
Null Handling (00:42)
Other Built-In Functions (02:15)
SSRS Specific Functions (01:00)
Demo:Nulls and Functions (05:17)
Creating Custom Functions (00:19)
The Code Window (01:08)
Referencing Code (00:16)
Demo:Custom Function (03:34)
Summary (00:29)

Global Collections (13:32)

Introduction (00:34)
Using Global Collections (01:10)
Fields Collection (00:21)
Globals Collection (00:20)
User Collection (00:20)
Demo: Globals and User (03:01)
Parameters Collection (00:18)
Report Items Collection (00:32)
Variables Collection (01:39)
Demo:Variables Collection (04:29)
Summary (00:41)

Formatting (36:34)

Introduction (00:46)
Formatting Items (00:33)
Formatting Using Toolbars (01:11)
Demo:Formatting Toolbars (03:42)
Formatting with Properties (05:35)
Formatting with Dialog Box (02:08)
Using Custom Format Strings (00:15)
Format Tablix Rows/Columns (00:46)
Demo:Format with Dialog Box (04:05)
Demo:Custom Format String (01:31)
Demo:Format Rows/Columns (01:37)
Conditional Formatting (02:13)
Demo:Conditional Formatting (06:38)
Conditional and Rich Text (00:31)
Demo:Conditional Rich Text (04:07)
Summary (00:48)

Module 4

Creating Groups (22:52)

Introduction (00:36)
Creating Groups (00:29)
The Grouping Pane (00:55)
The Details Group (00:35)
Adding a Row Group (00:46)
Demo:Row Group (05:29)
Add Adjacent Row Group (01:14)
Demo:Adjacent Row Group (05:37)
Editing Group Properties (00:20)
Add Group Headers+Footers (01:02)
Demo:Headers and Footers (05:29)
Summary (00:18)

Calculating Totals (33:07)

Introduction (00:39)
Aggregate Functions (01:15)
Demo:Aggregate Functions (09:48)
Scope (00:41)
Scope Example (00:23)
Scope RunningValue/RowNum (00:15)
Demo:Scope RunningValue (10:37)
Calculating Percentages (01:33)
Demo:Calculating Percentages (07:18)
Summary (00:33)

Interactive Sorting (13:33)

Introduction (00:44)
Interactive Sorting (01:06)
Sorting and Groups (00:43)
Demo:Interactive Sorting (04:34)
Creating Drill-Down Reports (01:08)
Group Properties Dialog (00:27)
Demo:Drill-Down Report (04:33)
Summary (00:15)

Module 5

Creating Parameters (32:58)

Introduction (00:44)
Creating Report Parameters (00:28)
Parameters and Procedures (00:30)
Demo:Stored Procedures (09:13)
Report vs. Dataset Parameters (00:22)
Report Parameters (00:53)
Dataset Parameters (01:33)
Reordering Report Parameters (01:14)
Parameter Values on Report (00:59)
Demo:Parameters (06:38)
Parameters with Ad-Hoc SQL (00:17)
Using Unbound Parameters (00:28)
Demo:Ad-Hoc SQL Parameters (04:41)
Demo:Unbound Parameters (04:36)
Summary (00:14)

Drop Down List Parameters (19:38)

Introduction (00:29)
Dropdown List Parameters (00:37)
Displaying Parameters (00:52)
Demo:Dropdown List (08:14)
Cascading Parameters (00:42)
Demo:Cascading Parameters (08:22)
Summary (00:19)

Parameter Sorting (26:43)

Introduction (00:45)
Parameterized Sorting (01:13)
Demo:Parameterized Sorting (08:33)
Show Columns using Parameters (00:30)
Demo:Show Columns (06:33)
Multivalued Parameters (00:35)
Report Multivalued Parameter (00:43)
Referring Multivalued Parameter (00:44)
Demo:Multivalued Parameter (04:23)
Debugging Parameter Issues (00:36)
Debugging Other Parameters (01:24)
Summary (00:39)

Module 6

Creating Matrix Reports (31:09)

Introduction (00:48)
Creating a Matrix Report (00:48)
Steps to Create Matrix Report (00:41)
To Group or Not to Group (00:48)
Demo:Matrix Report (04:25)
Accessorizing Matrix Reports (00:13)
Available Summary Statistics (01:30)
Adding Row Group (00:45)
Demo: Adding Row Group (01:58)
Adding Column Group (00:40)
Deleting Row or Column Group (00:50)
Demo:Column Group (05:24)
Working with Totals (00:43)
Removing a Total (00:24)
Demo:Row and Column Totals (02:26)
Adding Data Cells (00:51)
Demo:Adding Data Cells (01:25)
Data Cell Above or Below (00:34)
Demo:Data Cell Above/Below (01:52)
Adding Data Cell Labels (00:54)
Demo:Adding Data Cell Labels (02:34)
Summary (00:24)

SSRS 2008 Matrix Features (16:43)

Introduction (00:38)
Tablix Data Region (00:48)
Adding Dynamic Columns (00:38)
Demo:Dynamic Columns (03:36)
Adding Static Columns (00:37)
Demo:Adding Static Columns (04:04)
Adding Percentage to Matrix (01:17)
Demo:Adding Percentage (04:40)
Summary (00:20)

Creating A Chart (37:14)

Introduction (00:27)
Creating a Chart (00:23)
Basic Steps to Create a Chart (01:24)
Demo: Creating a Chart (04:32)
SQL 2008 Chart Changes (00:33)
Chart Anatomy (01:03)
Formatting a Chart (00:33)
Demo:Formatting a Chart (06:19)
Adding a Series to a Chart (00:36)
Demo:Adding a Series Field (04:48)
Chart Talk (01:17)
Chart Types (01:31)
Demo:Chart Types (11:59)
Charting Tips (01:27)
Summary (00:14)

Module 7

Adding Gauges (16:41)

Introduction (00:32)
Gauge Data Region (00:14)
Adding Gauges to a Report (01:18)
Demo:Adding a Gauge (04:09)
Gauge Anatomy (02:43)
Demo:2 Gauges (04:39)
Add Gauge to Existing Report (00:28)
Demo:Gauge to Existing Report (02:19)
Summary (00:14)

Deploy Reports (17:17)

Introduction (00:29)
Planning for Deployment (00:43)
Setting Active Configuration (00:21)
Creating Custom Configurations (00:24)
Demo:Active+Custom (01:56)
Configuring Report Project (02:10)
Deploying RS Project (00:25)
Redeploying Shared Data (00:41)
Demo:Deploying Project (05:10)
Exporting Reports + Printing (00:21)
Exporting Reports (00:36)
Demo:Exporting Reports (01:25)
Client-Side Printing (00:33)
Client-Side Printing Setup (00:17)
More Client-Side Printing (00:17)
Demo:Client-Side Printing (01:01)
Summary (00:18)

Report Manager (18:33)

Introduction (00:31)
Managing Reports (00:38)
Displaying or Hiding Details (00:19)
Navigating Report Manager (00:33)
Running Reports (00:21)
Demo:Report Manager (02:08)
Managing Folders and Files (01:05)
Reports and Data Sources (00:23)
Generic Site Settings (00:20)
Demo:Folders, Files + Settings (04:26)
Creating Linked Reports (01:09)
Creating a Linked Report (01:08)
Demo:Creating Linked Report (05:07)
Summary (00:19)

Module 8

Reporting Services Security (15:00)

Introduction (00:37)
Reporting Services Security (00:29)
Securing Access to Reports (00:38)
Authorizing At Home Level (00:30)
Authorizing Users (00:49)
Demo:Authorizing Users (02:33)
Authorizing at Project Level (01:10)
Demo:Project Level (01:50)
Managing RS Roles (00:27)
Demo:Managing RS Roles (02:04)
RS and Basic Autherntication (00:20)
Configuring Autherntication (00:53)
Demo:Config Autherntication (02:09)
Summary (00:24)

SSL (18:12)

Introduction (00:55)
RS and SSL (00:30)
Securing to Report Server (00:52)
Demo: Setup SSL (01:52)
Securing to Report Manger (01:06)
Demo: Securing Report Manager (01:17)
Requiring SSL (01:29)
Demo: Requiring SSL (02:46)
Data Source Security (00:12)
Data Security from VS (00:18)
Shared Data Properties (00:09)
Four Choices for Credentials (01:42)
Demo:Security Shared Data (04:40)
Summary (00:17)

Data Source Security (14:41)

Introduction (00:28)
Local Storing Credentials (00:44)
Storing Credentials in RS (00:36)
Demo:Storing Credentials (01:36)
Overwriting Data Sources (00:34)
Demo:Overwriting Data (01:32)
SQL Permissions Needed (01:09)
Demo:Users Run Reports (04:44)
Managing Data Security RM (00:29)
Demo:Security from RM (01:59)
Summary (00:45)

Module 9

Reporting Services APIs (33:22)

Introduction (00:47)
Reporting Services APIs (01:35)
URL Access (00:22)
Report with No Parameters (00:24)
Report with Parameters (00:55)
Demo:Report with URL Access (04:03)
Report to Output Format (00:39)
Render Formats (02:16)
Demo:Render to Formats (05:37)
Rendering PDF Custom Margins (00:37)
Customizing Data Rendering (01:29)
Demo:Rendering to CSV (04:01)
Integrating Report to Web App (00:44)
Demo:Report to Web App (09:22)
Summary (00:23)

Report Viewer Controls (35:13)

Introduction (00:39)
Report Viewer Controls (00:35)
Server vs. Local reports (00:33)
Web Report Viewer Control (00:14)
Steps to Use Web Report Viewer (01:01)
Demo:ASP with Report Viewer (09:08)
Windows Report Viewer Control (00:37)
Demo:Windows Report Viewer (04:07)
Viewer with Local Reports (02:20)
Creating a Local Report (01:39)
Demo:Creating Local Report (13:50)
Summary (00:25)

Module 10

Web Service (25:09)

Introduction (00:41)
RS Web Services (00:30)
A Tale of Two Endpoints (01:35)
Why Use the Web Service (00:43)
Wny Not Use Web Service API (00:20)
Using RS Web Service (00:36)
Adding Web Reference (00:56)
Demo:Add Web Reference (02:05)
ReportService2005 Classes (01:30)
ReportService2005 Methods (01:29)
Programming RS2005 (00:43)
CallReportServiceVB.aspx (01:15)
Drilling into the Code (00:41)
Demo:CallReportServiceVB (11:35)
Summary (00:24)

Web Service Subscription (31:36)

Introduction (00:36)
Creating Subscriptions (00:34)
Drilling into the Code (00:58)
Demo:Report Subscriptions (14:29)
More Drilling into the Code (01:39)
Demo:More Report Subscriptions (12:53)
Summary (00:23)

Custom Assemblies (20:19)

Introduction (00:31)
Custom Assemblies (00:59)
Custom Assembly Example (00:52)
Creating Custom Assembly (00:33)
Demo:Custom Assembly (04:35)
Creating Custom Assembly-2 (01:14)
Demo:Creating Continued (03:05)
Creating Custom Assembly-3 (02:02)
Demo:Running Assembly (04:33)
Security + Custom Assemblies (01:28)
Summary (00:24)

Module 11

Report Caching (32:24)

Introduction (00:37)
Warning! (00:11)
Report Caching (01:47)
Demo:Report Caching (02:37)
Creating Expiration Schedules (00:54)
Creating Shared Schedule (00:28)
Demo:Schedules (04:29)
How Caching Works (01:01)
Parameterized Reports (01:25)
Cache Invalidation (00:59)
Demo:Parameters (02:26)
Report Snapshots (02:37)
Creating a Snapshot (00:42)
Demo:Creating a Snapshot (02:26)
Snapshots and Parameters (00:50)
Demo:Snapshot with Parameter (02:41)
Report History (00:22)
Configuring Report History (00:34)
Snapshot Retention (00:38)
Viewing Report History (00:17)
Demo:Create History (04:00)
Summary (00:13)

Subscriptions (32:25)

Introduction (00:27)
Creating Subscriptions (00:52)
Parts of Subscription (00:40)
Creating Standard Subscription (00:33)
Demo:Creating Subscription (07:36)
Subscriptions and Parameters (00:14)
Maintaining Subscriptions (00:30)
Demo:Subscriptions (01:30)
Data-driven Subscriptions (01:01)
Data-driven Query (00:31)
File Share Data-driven (00:26)
Demo:File Share Data-driven (07:47)
Email Data-driven Subscription (00:42)
Demo:Email Data-driven (04:10)
Null Delivery Data-driven (01:04)
Demo:Null Delivery (03:51)
Summary (00:23)

Module 12

Two Report Builders (25:42)

Introduction (00:39)
Two Report Builders (00:27)
Report Builder 1.0 (00:45)
Report Builder 2.0 (01:02)
Which Report Builder to use? (00:36)
Launching Report Builder 2.0 (00:43)
Report Manager Link (00:29)
Update Report Builder Link (00:18)
Demo:Report Builder 1.0 + 2.0 (02:09)
Report Builder Data Sources (00:54)
Login, Shared Data, Folder (02:17)
Creating Report Builder Folder (00:32)
Demo:Creating Info (04:03)
Getting Started RB 2.0 (00:14)
New Table or Matrix Wizard (00:18)
Demo:New Table or Matrix (03:39)
Using New Chart Wizard (00:15)
Demo:Chart Wizard (05:41)
Summary (00:34)

Creating Reports (33:49)

Introduction (00:36)
Constructing a Report (00:43)
Tour Around Report Builder (01:14)
Report Builder Button (00:26)
Report Design Surface (00:16)
Ribbon Bar (00:57)
Report Data Pane (00:32)
Properties Window (00:16)
Grouping Pane (00:18)
Creating a Tabular Report (00:18)
Demo:Tabular Report (06:24)
Adding a Group to a Report (01:05)
Demo:Adding a Group (01:54)
Creating Matrix Reports (00:44)
Demo:Create Matrix Report (05:19)
Adding Totals to a Report (00:48)
Demo:Adding Totals to Groups (02:52)
Creating Charts (00:35)
Demo:Create Chart (07:56)
Summary (00:28)