Learn your way! Get started

SQL Server 2005: 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	9/15/2006
	Level	Advanced
	Runtime	9h 39m
	Closed captioning	N/A
	Transcript	N/A
	eBooks / courseware	Included
	Hands-on labs	Included
	Sample code	Included
	Exams	Included
	Platform	Internet Explorer Only

Enterprise Solutions

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

Course description

In this course, you will learn how to use SQL Server 2005 Reporting Services to create, execute, and manage reports. You will learn how to create tabular, matrix (cross-tab), and chart reports using Visual Studio 2005 and SQL Server 2005 Business Intelligence Studio. You will explore creating reports with groups, expressions, conditional formatting, and parameters. The reports you create in the course will employ shared data sources, interactive sorting, and drill-down capabilities. Explore the deployment and printing of reports and the export of reports to PDF, Excel, and other formats. You will gain a thorough understanding of Reporting Services security, report snapshots, subscriptions, and the use of custom assemblies. Discover how to integrate reports into your ASP.NET and WinForms applications using URL access and the Reporting Services Web Service API, without requiring users to use Internet Explorer. Finally, Learn how to empower your users to create ad-hoc reports using Report Builder and Report Model projects.

Prerequisites

Familiarity with databases and the SQL language. Knowledge of SQL Server, Visual Studio .NET/2005 helpful but not required. Knowledge of C# or VB helpful but also not required.

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

Introduction to RS (32:24)

Introduction (01:25)
Tour of Features (00:31)
Feature Overview (01:11)
Supported Report Types (01:29)
Supported Output Formats (01:15)
Data Sources (00:46)
Programming RS (01:20)
Deploying / Admin Features (01:09)
RS Components (01:34)
Report Server Component (00:45)
Repository Component (01:09)
Report Manager Component (00:42)
Report Mgr using a Browser (03:03)
Report Manager using SQL (01:28)
Report Designer Components (00:44)
Report Designer (06:21)
RS Configuration Tool (00:17)
Command-Line Utilities (00:49)
Examine Report Builder (00:40)
Report Builder (02:25)
Examine Model Designer (00:21)
Model Designer (02:35)
Summary (00:14)

Terminology (16:04)

Introduction (00:23)
Data Source / DataSet (01:52)
Data Region (00:33)
Table Region / Matrix Region (00:33)
List Region / Chart Region (00:29)
Mix and Match Regions (00:15)
Reporting Services Editions (01:39)
Report Wizard (10:03)
Summary (00:13)

Deconstructing RS (21:25)

Introduction (00:29)
Overview (00:22)
Project Files (00:33)
Ways to Create a Report (00:16)
Overview: Report Wizard (00:29)
MS Access Import Reports (05:41)
Access Feat. Not Imported (01:53)
New Report from Scratch (00:26)
Overview: Designer Views (00:28)
Designer Views: Data View (03:05)
Designer Views: Layout View (01:42)
Designer Views: Preview View (01:17)
Shared Data Sources (01:40)
Create a Shared Data Source (02:43)
Summary (00:14)

Module 2

Creating a Report (34:26)

Introduction (00:24)
Create Reports from Scratch (00:36)
Six Steps to a Basic Report (01:16)
Step 1: Creating the Report (00:42)
Step 2: Grab the Right Data (01:38)
Graphical Query Designer (03:48)
Generic Query Designer (04:47)
Using Stored Procedures (02:44)
Step 3: Data w/Layout View (00:30)
Adding a Table Region (00:44)
Adding Fields (01:39)
Other Report Item Types (01:13)
Adding Static Images (02:27)
Adding Bound Images (06:30)
Fix-up for OLE Bound Imgs. (05:15)
Summary (00:07)

Creating a Report (cont.) (21:47)

Introduction (00:34)
Adjusting Table Properties (02:47)
Add Page Headers & Footers (02:10)
Adjusting the Report Properties (02:56)
Add Rows to a Table Section (02:08)
Accessorizing Report (00:31)
Step 4: Previewing Reports (00:41)
Using the Preview Window (00:55)
Step 5: Deploy to Server (00:23)
Configuring for Deployment (01:10)
Deploying Reports (05:03)
Step 6: Call with Web Browser (02:07)
Summary (00:16)

Calc. Expressions / Collections (39:31)

Introduction (00:27)
Creating Expressions (01:34)
Expression Rules (00:33)
Using the Expression Editor (02:39)
Build Exp. by Typing Code (02:24)
Build Using List Items Panes (01:04)
Mathematical Calculations (00:36)
String Expressions (02:48)
Conditional Tests (04:08)
Null Handling (00:46)
Using Built-In Functions (00:13)
String / Date & Time Functions (01:25)
Aggregate / Conversion Func. (01:08)
Program Flow Functions (00:05)
RS-Specific Functions (00:36)
Using a Built-in Func. Demo (01:19)
Create / Use Custom Functions (00:23)
The Code Window (01:03)
Use a Func. in Code Window (04:41)
Ref. Code Window Functions (01:44)
Using the Global Collections (01:01)
Fields / Globals / User Collect. (01:43)
Global / User Collect. Demo (02:35)
Param. / ReportItems Collect. (00:27)
Param. / ReportItems Demo (03:45)
Summary (00:14)

Module 3

Formatting / Cond. Formatting (25:25)

Introduction (00:28)
Formatting Items (00:31)
Format using the Toolbars (03:47)
Format using the Prop. Win. (03:16)
Properties of Special Note (00:42)
Format w/Textbox Prop. Dialog (00:25)
General / Visibility / Font Tabs (02:18)
Format Tab (01:51)
Table Region Rows/Col. (03:12)
Merging and Splitting Cells (02:38)
Conditional Formatting (05:52)
Summary (00:20)

Creating Groups (20:51)

Introduction (00:27)
Overview (00:13)
Adding a Group to a Report (00:42)
Adding Fields to a Group (00:29)
Add Groups / Fields Demo (04:45)
Grouping / Sorting Properties (02:25)
Ordering Groups (00:33)
Nesting Groups (01:20)
Order / Nest / Insert Demo (08:17)
Editing Group Properties (01:19)
Summary (00:16)

Using Groups (31:39)

Introduction (00:27)
Calculate Totals & Percentage (02:06)
Counting Rows Demo (02:58)
Summing Sales Demo (05:41)
Scope (01:09)
Scope and RunningValue (00:14)
RunningValue Demo (04:04)
Calculate Percentages (01:12)
Calc. Percentages Demo (06:14)
Interactive Sorting (00:32)
Interactive Sorting Demo (02:54)
Drill-Down Reports (01:06)
Create Drill-Down Rep. Demo (02:43)
Summary (00:14)

Module 4

Creating Parameters (31:35)

Introduction (00:28)
Overview (00:24)
Parameters/Stored Procedures (00:33)
Report Parameters Dialog (00:21)
Param. on a Report Demo (06:18)
Display Parameter Values (00:52)
Add Parameter Value Demo (01:53)
Use Param. with Ad-Hoc SQL (00:36)
Param w/ Ad-Hoc SQL Demo (04:00)
Use Unbound Parameters (00:26)
Unbound Parameters Demo (03:33)
Create Dropdown List Param. (00:39)
Dropdown List Param. Demo (03:32)
Cascading Parameters (00:35)
Cascading Parameters Demo (07:07)
Summary (00:10)

Advanced Parameters (14:44)

Introduction (00:29)
Multivalued Parameters (00:24)
Multivalued Param. Report (00:38)
Reference Multivalued Param. (00:53)
M.P.R. Demo (04:30)
Debugging Parameter Issues (00:51)
Debug Missing Parameter (01:28)
Debug Data Type Mismatch (01:42)
Debug Hidden Parameter (01:44)
Debug Casc. Param. Report (01:51)
Summary (00:08)

Matrix Reports (18:04)

Introduction (00:32)
Creating a Matrix Report (01:02)
Steps to Create Matrix Report (00:34)
Create a Matrix Report Demo (02:57)
Accessorizing Matrix Reports (00:32)
Adding a Subtotal (00:25)
Removing a Subtotal (00:10)
Adding Row / Column Groups (00:51)
Subtotals/Row/Column Demo (06:08)
Adding Data Cells (00:21)
Using the Corner Cell (00:26)
Data Cells / Corner Cell Demo (03:47)
Summary (00:16)

Module 5

Chart Reports (17:28)

Introduction (00:24)
Overview (00:17)
Basic Steps to Create a Chart (00:48)
Create a Chart Demo (01:16)
Chart Anatomy (00:38)
Format a Chart (01:30)
Format a Chart Demo (03:21)
Add a Series (00:18)
Add a Series to Chart Demo (02:28)
Exploring Possibilities (01:51)
Several Chart Layouts Demo (03:28)
Charting Add-ons (00:45)
Summary (00:16)

Deploying / Exploring Projects (17:38)

Introduction (00:26)
Overview (00:16)
Planning for Deployment (01:18)
Setting the Active Config (00:15)
Configuring the Report Project (00:44)
Deploying a Project (00:36)
Configure/Deploy Demo (03:30)
Redeploying Shared Data Src. (00:30)
Redeploy SD Source Demo (01:23)
Exporting Reports (01:26)
Exporting Reports Demo (04:36)
Client-Side Printing (01:18)
Client-Side Printing Demo (01:00)
Summary (00:14)

Managing Reports (16:17)

Introduction (00:31)
Browser-Based Report Mgr. (00:37)
Display/Hide RM Details (00:23)
Managing Folders and Files (00:48)
Browser-Based RM Demo (03:09)
Creating a Linked Report (01:10)
Setup Linked Reports Demo (04:06)
Mng. Generic Site Settings (01:36)
Additional Report Manager (00:22)
SQL Server Based RM (01:20)
SQL Server Based RM Demo (01:51)
Summary (00:18)

Reporting Services Security (17:18)

Introduction (00:25)
Overview (00:29)
Securing Access to Reports (00:19)
Auth. Users at Home Level (00:12)
Using the Browser-Based RM (00:32)
Authorizing Users (01:02)
Manage Sec. w/BM Demo (01:58)
Using the SQL Server Snap-In (00:31)
Manage Sec. w/Snap-in Mgr. (01:28)
Auth. Users at Project Level (01:36)
Manage PLS Demo (04:36)
Managing RSR (00:37)
Add/Mng. New Roles Demo (03:08)
Summary (00:19)

Module 6

Virtual Dir. / DataSource Sec. (17:40)

Introduction (00:31)
Overview (00:45)
Configure Virtual Directory (01:22)
Requiring SSL (00:39)
Config ReportServer for SSL (01:49)
Data Source Security (00:20)
VS: Mng. Data Source Security (00:16)
Managing Credentials: Choices (01:24)
Data Source Credentials (01:29)
Local Storing of Credentials (00:31)
Storing of Credentials (00:17)
Overwriting Data Sources (01:09)
Overwriting a Data Source (02:10)
Managing Data Source Sec. (01:12)
Manage Data Source Sec. (03:19)
Summary (00:20)

Prog. Reporting Services (24:39)

Introduction (00:29)
Reporting Services APIs (01:28)
URL Access (00:16)
Executing Reports via URL (01:36)
Executing a Report w/ Param. (04:06)
Rendering to an Output Format (00:45)
Render Formats (03:36)
PDF Report w/ Cust. Margins (03:00)
Integrate Into a Web App (00:56)
Run a Simple Example (03:52)
Run a Parameterized Ex. (04:08)
Summary (00:21)

Report Viewer Control (21:52)

Introduction (00:39)
Overview (00:22)
Server vs. Client Reports (00:36)
Web RV w/Server Reports (01:22)
Web RV w/SR Demo (07:00)
Windows RV w/SR (00:54)
Windows RV w/SR Demo (04:30)
RV Control w/Client Reports (01:23)
Web RV w/Client Rep. Demo (04:44)
Summary (00:19)

Module 7

Web Services (16:47)

Introduction (00:37)
Overview (00:24)
Tale of Two Endpoints (01:15)
Use of Web Service API (00:22)
Report Server Web Service (00:24)
Steps to Add a Web Reference (00:48)
Add a Web Reference Demo (01:48)
ReportService2005 Classes (00:47)
ReportingService2k5 Methods (01:04)
Prog. ReportService2005 WS (00:34)
View Sample of Web Service (01:40)
CallReportServiceVB Code (04:51)
Run the Sample (01:32)
CallReportServiceCS Code (00:20)
Summary (00:16)

Custom Assemblies (18:02)

Introduction (00:30)
Overview (00:52)
Custom Assembly Part 1 (00:59)
Examine a CA Example (01:08)
Source Code for the Example (00:55)
Add a Reference (01:18)
Build / Test the Example (00:56)
Custom Assembly Part 2 (01:12)
Add Class Ref. for Report (01:19)
Add Reference to Method (00:50)
Custom Assembly Part 3 (01:23)
Copy the Assemblies (00:57)
Custom Assembly Part 4 (01:31)
Preview the Report (00:26)
Deploy the Report (00:42)
Execute the report (00:48)
Deploy to Production Server (00:22)
Security & Cust. Assemblies (01:20)
Summary (00:27)

Subscriptions (41:50)

Introduction (00:30)
Overview (00:22)
Parts of a Subscription (00:52)
Create a Standard Subscription (00:44)
Standard Sub. Demo (07:45)
Subscriptions and Parameters (00:16)
Demo: Subscribe to a PR (03:31)
Data-driven Subscriptions (00:47)
Create the Data-driven Query (00:54)
Create a Data-driven Sub. (01:55)
Data-driven Sub. Demo (09:48)
Email Data-driven Subscription (01:39)
Email Data-driven Sub. Demo (06:33)
Null Delivery Data-driven Sub. (01:40)
Null Data-driven Sub. Demo (04:13)
Summary (00:13)

Module 8

Report Caching and Snapshot (21:01)

Introduction (00:41)
Caching and Snapshots (00:31)
Overview: Report Caching (00:29)
Caching Choices (01:33)
Setup Caching Demo (02:24)
Setup Report-Specific Sched. (01:25)
How Caching Works (01:00)
Caching Param. Reports (00:50)
Caching PR Demo (01:20)
Cache Invalidation (00:29)
Overview: Report Snapshots (01:30)
Create a Snapshot (00:18)
Create a Snapshot Demo (02:22)
Snapshots and Parameters (00:22)
Snapshot with PR Demo (01:41)
Report History (01:34)
Enable / View Report History (02:03)
Summary (00:18)

Report Model (27:27)

Introduction (00:45)
Report Builder Architecture (00:28)
Using Report Builder (00:27)
How Report Builder Works (00:34)
Parts of a Report Model (00:59)
Report Model Project Part 1 (01:31)
Project / Data Source Demo (01:58)
Report Model Project Part 2 (00:19)
Data Source View Demo (01:55)
Report Model Project Part 3 (00:46)
Generate the RM Demo (02:57)
Report Model Objects (00:43)
Report Model Project Part 4 (01:22)
Refine Report Model Demo (04:37)
Add a Perspective Demo (02:06)
Report Model Project Part 5 (00:37)
Deploy Report Model Demo (00:39)
View Deployed Project Demo (00:38)
Securing Report Models (01:33)
Securing Report Models Demo (02:05)
Summary (00:18)

Report Builder (13:37)

Introduction (00:31)
Overview (00:47)
Install Report Builder (00:30)
Install Report Builder Demo (00:32)
Running Report Builder (00:21)
Create a Table Report (05:07)
Create a Matrix Report (01:56)
Create a Chart Report (03:21)
Summary (00:30)