Microsoft SQL Server 2012 Reporting Services 4/E

Home >
Browse >
Microsoft SQL Server 2012 Reporting Services 4/E

Microsoft SQL Server 2012 Reporting Services 4/E

Authors: Brian Larson

Published: March 2012

eISBN: 9780071760485 0071760482 | ISBN: 9780071760478

Reporting Services

About the Author

About the Technical Editors

Contents at a Glance

Contents

Foreword

Acknowledgments

Introduction

The Galactic Database and Other Supporting Materials

Part I: Getting Started

Chapter 1: Let’s Start at the Very Beginning

Sharing Business Intelligence

The Need to Share

The Production Manager

The Vice President of Sales

The Chief Executive Officer

Possible Solutions

An HTML Solution

A PDF Solution

A Third-Party Reporting Environment

Microsoft Reporting Services

No Programming Required

A Server with a View

Plays Well with Printers

Special Delivery

The Price Is Right

Reporting Services to the Rescue

Report Authoring Architecture

Report Structure

Data Definition

Report Layout

Report Definition Language

Report Designer

Design Surface

Report Data Window

Toolbox

Properties Window

Grouping Pane

Report Builder

Report-Serving Architecture

Report Server

Report Catalog

Report Processor

Data Providers

Renderers

Request Handler

Report Delivery

Report Manager Website

SharePoint

Subscription Delivery

Web Service Interface

Diving In

Chapter 2: Putting the Pieces in Place: Installing Reporting Services

Preparing for the Installation

The Parts of the Whole

The Windows Service

Administration Utilities

SQL Server 2005/2008/2008 R2/2012

SQL Server Agent

The Report Server and Report Server Temp DB Databases

Sample Reports and the AdventureWorks Database

SQL Server Data Tools/Visual Studio/Report Builder

Documentation

Editions of Reporting Services

The Express Edition

The Standard Edition

The Business Intelligence and Enterprise Editions

The Developer Edition

Types of Reporting Services Installations

The Full Installation

The Server Installation

The Report Author Installation

The Distributed Installation

The Scale-Out Installation

SharePoint Integrated Mode Installation

Installation Requirements

Hardware Requirements

Software Requirements

Other Installation Considerations

Distributed Installation and Scale-Out Installation Considerations

SharePoint Integrated Mode Considerations

Database Server Considerations

E-mail (SMTP) Server

Encrypting Reporting Services Information

Login Accounts

Running the SQL Server Installation Program

The Installation Process

The SQL Server 2012 Installation

Preliminaries

The Feature Selection Page

Instance Configuration Page

Server Configuration Page

The Reporting Services Configuration Page

Completing the SQL Server Installation Wizard

The Reporting Services Configuration Manager

Server Status Page

Service Account Page

Web Service URL Page

Report Server Database Page

Report Manager URL Page

E-mail Settings Page

Execution Account Page

Encryption Keys Page

Scale-out Deployment Page

Menu Bar

Completing a Reporting Services SharePoint Integrated Mode Installation

Creating a Reporting Services Service Application

Making the Service Application Part of the Default Application Proxy Group

Starting the Reporting Services Service Application

Common Installation Issues

Administrative Rights

Server Components Not Shown on the Feature Selection Screen

Installation Error 2755

Reporting Services and IIS on the Same Server

The Repair Utility and Installation Log File

Spending Some Time in Basic Training

Part II: Report Authoring

Chapter 3: DB 101: Database Basics

Database Structure

Getting Organized

Tables, Rows, and Columns

Primary Key

Normalization

Relations

Cardinality of Relations

Retrieving Data

Inner Joins

Outer Joins

Joining Multiple Tables

Self-Joins

Sorting

Galactic Delivery Services

Company Background

Package Tracking

Personnel

Accounting

Transport Maintenance

Querying Data

The SELECT Query

The FROM Clause

The FIELD LIST

The JOIN Clause

The WHERE Clause

The ORDER BY Clause

Constant and Calculated Fields

The GROUP BY Clause

The HAVING Clause

On to the Reports

Chapter 4: A Visit to Emerald City: The Report Wizard

Obtaining the Galactic Database

Getting Started with the Authoring Environment

Creating a Project Using the Report Designer

Project Templates

Launching the Report Builder

Your First Report

The Customer List Report

Customer List Report, Task 1: Begin a New Project in SSDT or Visual Studio, or Preparing to Create a New Report in the Report Builder

Customer List Report, Task 2: Create a Data Source

Customer List Report, Task 3: Create a Dataset

Customer List Report, Task 4: Choose the Report Layout

An Interactive Table Report

The Customer-Invoice Report

Customer-Invoice Report, Task 1: Prepare to Create a Second Report

Customer-Invoice Report, Task 2: Create a New Report, Select the Shared Data Source, and Create a Dataset

Customer-Invoice Report, Task 3: Choose the Report Layout

Creating Matrix Reports

The Invoice-Batch Number Report

Invoice-Batch Number Report, Task 1: Create a New Report, Select the Shared Data Source, and Create a Dataset

Invoice-Batch Number Report, Task 2: Choose the Report Layout

Report Parameters

The Parameterized Invoice-Batch Number Report

Parameterized Invoice-Batch Number Report, Task 1: Reopen the Invoice-Batch Number Report, and Add Parameters to the Query in the Original Dataset

Parameterized Invoice-Batch Number Report, Task 2: Create a Second Dataset Containing a List of Cities

Parameterized Invoice-Batch Number Report, Task 3: Customize the Report Parameters

Flying Solo

Chapter 5: Removing the Training Wheels: Building Basic Reports

Riding Down Familiar Roads

The Transport List Report

Transport List Report, Task 1: Preparation and Creation of a New Report

Transport List Report, Task 2: Create a Dataset

Transport List Report, Task 3: Place a Table Item on the Report and Populate It

Transport List Report, Task 4: Add Table Grouping and Other Report Formatting

The Tablix and Data Regions

The Repair Count By Type Report

Repair Count By Type Report, Task 1: Create a New Report, Select the Shared Data Source, and Create a Dataset

Repair Count By Type Report, Task 2: Place a Matrix Item on the Report and Populate It

Repair Count By Type Report, Task 3: Add Column Grouping and Other Report Formatting

New Territory

The Transport Information Sheet

Transport Information Sheet, Task 1: Create a New Report, Select the Shared Data Source, and Create the TransportSNs Dataset

Transport Information Sheet, Task 2: Create the TransportDetail Dataset

Transport Information Sheet, Task 3: Place a List Item on the Report and Populate It

Getting Graphical

Chapter 6: Graphic Expression: Using Charts, Images, and Gauges

Chart Your Course

The Deliveries Versus Lost Packages Chart

Deliveries versus Lost Packages Chart, Task 1: Create a New Report and Two Datasets

Deliveries versus Lost Packages Chart, Task 2: Place a Chart Item on the Report and Populate It

Deliveries versus Lost Packages Chart, Task 3: Explore Alternate Ways to Present the Deliveries and Lost Packages Together

The Fuel Price Chart

Fuel Price Chart, Task 1: Create a New Report and Two Datasets

Fuel Price Chart, Task 2: Place a Chart Item on the Report and Populate It

Fuel Price Chart, Task 3: Refine the Chart

The Fuel Price Chart, Version 2

Fuel Price Chart, Version 2, Task 1: Create a New Dataset for the Second Drop-down List and Revise the FuelPrices Dataset to Allow for Fuel Type Selection

The Business Type Distribution Chart

Business Type Distribution Chart, Task 1: Create a New Report and a Dataset

Business Type Distribution Chart, Task 2: Place a Chart Item on the Report and Populate It

The Days in Maintenance Chart

Gauging the Results

The Digital Dashboard

Digital Dashboard, Task 1: Create a New Report along with a Dataset and Present the Data on a Gauge

Digital Dashboard, Task 2: Refine the Appearance of the Gauge

Digital Dashboard, Task 3: Modify the Dataset and Add a Second Gauge

Image Is Everything

Conference Nametags

Conference Nametags, Task 1: Place the GDS.gif File in the Appropriate Location

Conference Nametags, Task 2: Create a New Report, Create a Dataset, and Place the Report Items on the Report

Conference Place Cards

Conference Place Cards, Task 1: Create a New Report, Create a Dataset, and Place the Report Items on the Report

The Rate Sheet Report

Rate Sheet Report, Task 1: Create a New Report, Create a Dataset, and Place the Report Items on the Report Layout

Rate Sheet Report, Task 2: Refine the Report Layout

From Graphical to Geo-graphical

Chapter 7: Geography Lesson: Using Maps and Spatial Data Types

Colors and Bubbles: Representing Quantities on Maps

The Earth U.S. Deliveries Map

Earth U.S. Deliveries Map, Task 1: Create a New Report and a New Dataset

Earth U.S. Deliveries Map, Task 2: Place a Map Item on the Report and Populate It

SQL Server Spatial Data Types

Spatial Functions

Spatial Data Types

Putting Polygons to Use

The Deliveries per Planet Map

Deliveries per Planet Map, Task 1: Create a New Report and Two New Datasets

Deliveries per Planet Map, Task 2: Place a Map Item on the Report and Populate It

The Employee Homes Map

Employee Homes Map, Task 1: Create the Map Report

Building Higher

Chapter 8: Kicking It Up a Notch: Intermediate Reporting

Never Having to Say “I’m Sorry”

The Report Template

Report Template, Task 1: Create a Template Report with a Page Header

Report Template, Task 2: Create the Page Footer on the Template Report

Report Template, Task 3: Copy the Template to the Appropriate Location

Putting the Report Template to Use

The Employee Time Report

Employee Time Report, Task 1: Create a New Report and a New Dataset

Employee Time Report, Task 2: Populate the Report Layout

Employee Time Report, Task 3: Add Drilldown Capability

Employee Time Report, Task 4: Add Totaling

Data Caching During Preview

The Employee List Report

Employee List Report, Task 1: Create a New Report and a Dataset

Employee List Report, Task 3: Add Interactive Sorting and a Floating Header

The Employee Mailing Labels Report

Employee Mailing Labels Report, Task 1: Create the Mailing Label Content

Employee Mailing Labels Report, Task 2: Add the Report Header and Multiple Columns

The Overtime Report

Overtime Report, Task 1: Create a New Report and Three Datasets

Overtime Report, Task 2: Create the Report Layout

Overtime Report, Task 3: Add Data Bars to the Report

The Revised Employee Time Report

Revised Employee Time Report, Task 1: Copy and Rename the Existing Report

Revised Employee Time Report, Task 2: Modify the Dataset and the Layout

The Revised Employee List Report

Revised Employee List Report, Task 1: Copy and Rename the Existing Report, Add Datasets, and Utilize the Lookup Functions

Under the Hood

Viewing the RDL

XML Structure

Editing the RDL

Advance, Never Retreat

Chapter 9: Beyond Wow: Advanced Reporting

Speaking in Code

The Delivery Status Report

Delivery Status Report, Task 1: Create a New Report and Two Datasets

Delivery Status Report, Task 2: Set Up the Report Parameters and Place the Titles on the Report Layout

Delivery Status Report, Task 3: Add a Tablix to the Report

Delivery Status Report, Task 4: Add the Expressions

The Lost Delivery Report

Lost Delivery Report, Task 1: Create a New Report, Create a Dataset, and Add a Tablix to the Report

Lost Delivery Report, Task 2: Add a Calculated Column to the Tablix

Lost Delivery Report, Task 3: Add an Indicator and Totals to the Tablix

The Customer List Report—Revisited

Customer List Report—Revisited, Task 1: Copy the Report from Chapter04 for Use in Chapter09

Customer List Report—Revisited, Task 2: Add a Report Parameter and Modify the Dataset to Use the Report Parameter

The Delivery Trend Report

Delivery Trend Report, Task 1: Create a New Report, Create a Dataset, and Create a Report Layout

Payroll Checks

Payroll Checks, Task 1: Create a New Report, Create Two Datasets, Add a Tablix to the Report Layout, and Populate It

Payroll Checks, Task 2: Add a Second Tablix to the Report Layout and Populate It

Payroll Checks, Task 3: Configure the Report Parameter and Add Embedded Code to the Report

The Weather Report

Weather Report, Task 1: Copy the .NET Assembly into the Appropriate Location, Create a New Report, and Create a Reference to the Assembly

Weather Report, Task 2: Create a Dataset, Add a Tablix to the Report Layout, and Populate It

The Delivery Analysis Report

Delivery Analysis Report, Task 1: Copy the .NET Assembly into the Appropriate Location, Create a New Report, Create a Reference to the Assembly, and Create a Dataset Using the MDX Query Designer

Delivery Analysis Report, Task 2: Add a Tablix to the Report Layout, Populate It, and Localize the Report Strings

Reports Within Reports

The Employee Evaluation Report

Employee Evaluation Report, Task 1: Create a New Report, Create a Dataset, Add a Tablix to the Report Layout, and Populate It

Employee Evaluation Report, Task 2: Create a New Report, Create a Dataset, and Populate the Report Layout

Employee Evaluation Report, Task 3: Add a Rectangle

Employee Evaluation Report, Task 4: Add Rich Formatting

The Invoice Report

Invoice Report, Task 1: Create a New Report, Create a Dataset, and Copy the Layout from the DeliveryStatus Report

Invoice Report, Task 2: Create a New Report, Create a Dataset, and Populate the Report Layout

Invoice Report, Task 3: Improve PDF Export

Interacting with Reports

The Invoice Front-End Report

Invoice Front-End Report, Task 1: Create a New Report, Create a Dataset, and Populate the Report Layout

Invoice Front-End Report, Task 2: Add the Navigation

Invoice Front-End Report, Task 3: Improve Excel Export

The Transport Monitor Report

Transport Monitor Report, Task 1: Create a New Report, Create a Dataset, Populate the Report Layout, and Set Report Properties

What’s Next

Part III: Report Serving

Chapter 10: How Did We Ever Manage Without You? The Report Manager

Folders

The Report Manager

Adding a New Folder Using the Report Manager

Moving Reports and Supporting Files to the Report Server

Deploying Reports Using the Report Designer

Deploying Reports in the Chapter07 Project Using the Report Designer

Working Through the Web Service

Creating Folders While Deploying

Deploying a Single Report

Deploying Shared Data Sources

Additional Properties in the Property Pages Dialog Box

Uploading Reports Using Report Manager

Uploading Reports in the Chapter06 Project Using the Report Manager

Creating a Shared Data Source in the Report Manager

Hiding an Item

Connect Using Options

Uploading Other Items Using Report Manager

Uploading External Report Images

Uploading Supporting Materials

Uploading Reports Using .NET Assemblies

Copying the .NET Assembly to the Report Server

Code Access Security

Modifying the Report Server’s Security Configuration

Uploading the Report

Try the Deploy One More Time

A Look at Localization

Modifying Reports from the Report Server

Downloading a Report Definition

Editing the Report Definition

Uploading the Modified Report Definition

Managing Items in Folders

Moving Items Between Folders

Moving a Single Item

Moving Multiple Items

Deleting a Folder

Deleting a Folder Using the Check Boxes and Toolbar

Renaming a Folder

Seek and Ye Shall Find: Search and Find Functions

Searching for a Report

Finding Text Within a Report

Report Output Formats

Exporting Reports in Other Formats

Presentation Rendering Formats

Data Exchange Rendering Formats

The XML Export Format

The Atom Data Feed

Printing from Report Manager

Printing Options

Managing Reports on the Report Server

Security

Integration with Windows Security

Local Administrator Privileges

Tasks and Rights

Roles

Linked Reports

Creating a Linked Report

Deploying the Report to a Common Folder

Creating Linked Reports

Managing Report Parameters in Report Manager

Reporting Services in SharePoint Integrated Mode

Creating a Document Library

Deploying a Report to a Document Library

Managing Reports

Security Roles

Data Alerts

The Data Alert Architecture

Requirements for Creating a Data Alert on a Report

Creating a Data Alert

Delivering the Goods

Chapter 11: Delivering the Goods: Report Delivery

The Joy of Sharing

Shared Datasets

Creating a Shared Dataset from Scratch

Creating a Shared Dataset from an Existing Dataset

Modifying Shared Datasets

Using a Shared Dataset

Report Parts

Saving Report Parts on the Report Server

Using Report Parts in a Report

Updating Report Parts

Using Shared Datasets and Report Parts Wisely

Caching In

Report Caching

Cached Report Expiration

Cached Reports and Data Source Credentials

Caching and Report Formats

Enabling Report Caching

Enabling Report Caching for the Weather Report

Viewing the Report from the Report Cache

Cache Expiration on a Schedule

Report Cache and Deploying

Report Caching and Report Parameters

Report Caching and Security

Report Snapshots

Enabling Report Snapshots

Manually Creating a Report Snapshot

Creating Report Snapshots on a Schedule

Report Snapshots and Security

Cache Refresh Options

Cache Refresh Plans

Creating a Cache Refresh Plan

Report History

Enabling Report History

Manually Creating a Report History Snapshot

Report History Snapshots and Report Parameters

Additional Methods for Creating Report History Snapshots

Report History Snapshots and Security

Managing Report History Snapshots

Setting Limits on the Number of Report History Snapshots

Manually Deleting Report History Snapshots

Disabling Report History Snapshot Creation

Updating Report Definitions and Report History Snapshots

Subscriptions

Standard Subscriptions

Creating a Standard E-mail Subscription with an Embedded Report

Creating a Standard E-mail Subscription with a Report Link

Standard Subscriptions and Report Snapshots

Multiple Subscriptions for One Report

Embedded Report Versus Attached Report

Standard Subscriptions and Security

Managing Your Subscriptions

My Subscriptions

Data-Driven Subscriptions

Creating a Data-Driven Subscription

Data-Driven Subscriptions and Security

Data-Driven Subscriptions and Event-Driven Behavior

Data-Driven Subscriptions and Report Caching

Site Settings

The General Site Settings Page

Name

Report History Default

Report Timeout

Custom Report Builder Launch URL

Other Pages Accessed from the Site Settings Page

Site-Wide Security

Shared Schedules

Managing Reporting Services Through the SQL Server Management Studio

The Server Properties Dialog Box

My Reports

Security and My Reports

When to Enable the My Reports Option

Report Execution Logging

Additional Settings

A Sense of Style

The Reporting Services Style Sheet

Modifying the Reporting Services Style Sheet

Building On

Chapter 12: Teamwork: Integrating Reporting Services

Using Reporting Services Without the Report Manager

URL Access

Basic URL Access

Command Parameters

Passing Parameters

Controlling the Report Viewer

URL Access Using an HTTP Post

Web Service Access

Using a Web Service Call to Execute a Report

Managing Reporting Services Through Web Services

The Report Viewer Control

Displaying a Report from a Report Server

Displaying a Local Report in the Report Viewer

SharePoint Web Parts

Installing the Web Parts

Adding the Web Parts

Reporting Services Utilities

The RSKeyMgmt Utility

The RSConfig Utility

The RSReportServer.Config File

The RS Utility

Using the RS Utility to Manage System Properties

Log Files

Best Foot Forward

Chapter 13: Well Begun: Best Practices

Report-Authoring Practices

Use Report Templates

Use Shared Data Sources

Use Views and Stored Procedures

Use Navigation Features

Report Deployment Practices

Create a Backup of the Encryption Key

Review Reports Before Deploying

Use Linked Reports

Use Folders and Descriptions to Organize Reports

Assign Security at the Folder Level

Assign Security to Domain Groups

Assign Only the Rights Needed

Hide Items

Deploy Supporting Items to the Report Server

Use Caching and Snapshots

Where Do We Go from Here?

Index