Contents

About the Authors

About the Technical Reviewers

Acknowledgments

Introduction

Chapter 1: Introducing the Reporting Services Architecture

Understanding the Benefits of SSRS

SQL Server 2008 R2 and 2012 Reporting Services Enhancements

Report Builder/Data Modeler

SSRS 2012 Integration with Microsoft Office SharePoint

Tablix Report Properties

Enhanced Charting and Report Item Visualizations

Enhanced Performance and Memory Management

Embeddable SSRS Controls

HTML Text Formatting

Microsoft Word Rendering

Report Parts

Lookup Functions

Shared Datasets

SSRS and Business Intelligence

Business Intelligence Development Studio and SQL Server Data Tools

SQL Server Management Studio (SSMS)

SSRS Architecture

SSRS Databases

The SSRS Report Server

SSRS Web Service Interface

Authentication Layer

The Report Processor

Data Processing

Report Rendering

Scheduling and Delivery

Client Applications

Report Manager

Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT)

Command-Line Utilities

Report Builder

Custom Clients

Installing and Configuring

Deploying SSRS Securely

Summary

Chapter 2: Report Authoring: Designing Efficient Queries

Introducing the Sample Relational Database

Introducing the Schema Design

Knowing Your Data: A Quick Trick with a Small Procedure

Introducing Query Design Basics

Creating a Simple Query Graphically

Creating an Advanced Query

Testing Performance with SQL Server Management Studio (SSMS)

Optimizing Performance: Dividing the Load

Using a Parameterized Stored Procedure

Using ISNULL to Evaluate the Parameters

Query Performance and Indexes

Column and Table Aliasing

Testing the Procedure

Summary

Chapter 3: Introduction to Reporting Services Design with SQL Server Data Tools

Exploring the Elements of BIDS

Setting Up a Basic IDE

Understanding Report Definition Language (RDL)

Adding a Report

Setting Up Data Sources and Datasets

Creating a Data Source

Creating a Dataset

Creating Other Data Sources

Configuring Parameters

Setting up Filters

Expressions

Summary

Chapter 4: Laying Out a Report

Setting Up Pagination

Using Report Objects

Implementing a List

Implementing a Textbox

Implementing a Table

Implementing a Rectangle

Implementing a Matrix

Summary

Chapter 5: Implementing Dashboard-Style Report Objects

Understanding the Chart Data Region

Implementing an Image

Implementing a Gauge

Implementing a Map

Implementing a Data Bar

Implementing a Sparkline

Implementing an Indicator

Summary

Chapter 6: Building Reports

Creating a Report with the Report Wizard

Building Reports from Scratch

Formatting the Output

Adding Subtotals

Adding Interactivity

Document Mapping

Visibility

Interactive Sorting

Hyperlink Actions

Adding a Bookmark Link

Adding a URL Link

Building the URL Link with a Report Parameter

Jumping to a Report

Adding Hyperlink Formatting and Tooltips

Setting Report Parameters with Stored Procedures

Working with Multivalued Parameters

Applying a Filter

Adding a Chart

Adding Tablix Elements

Configuring Report and Group Variables

Adding the Gauge Control

Adding the Final Touches

Summary

Chapter 7: Using Custom .NET Code with Reports

Using Embedded Code in Your Report

Using the ExceedMaxCosts Function

Using the ExceedMaxCost Function in a Report

Accessing .NET Assemblies from Embedded Code

Using Custom Assemblies with Your Report

Adding a Class Library Project to Your Reporting Solution

Deploying a Custom Assembly

Adding an Assembly Reference to a Report

Debugging Custom Assemblies

Troubleshooting Your Project

Summary

Chapter 8: Deploying Reports

Using Report Manager

Using Report Builder 3.0

Using BIDS and Visual Studio 2012

Configuring Report Deployment Options

Setting Up Deployments Using the Configuration Manager

Deploying Reports Through the Solution Explorer

Using the rs.exe Utility

Using the Report Server Web Service

Accessing the Web Service

Laying Out the Form

Coding the Form

Allowing Users to Enter a Server Name

Populating the TreeView Control With a List of Folders

Opening the RDL File and Uploading It to the Server

Running the Application

Summary

Chapter 9: Rendering Reports from .NET Applications

Implementing URL Access

URL Report Access Path Format

URL Parameters and Prefixes

Report Parameters

HTML Viewer Commands

Report Server Command Parameters

Credential Parameters

Report Viewer Web Part Commands

Example URLs

Integrating SSRS 2012 with .NET Applications

Building a custom Report Viewer Using the WebBrowser Control

Building the Report Viewer Using a Report Viewer Control

Rendering the Report Locally

Creating the Report’s Data Source

Designing the Report

Using the Report Server Web Service

Web Services Method Categories

Creating the GetParameters Form

Coding the Report Parameters Form

The GetParameters_Load Event

Calling the Web Services GetItemParameters Method

Rendering the Final Report

Building the Report Viewer in ASP.NET

Summary

Chapter 10: Managing Reports

Exploring Management Roles in SSRS Deployment

Managing Content

Setting Up Shared Schedules

Creating a Shared Schedule

Configuring a Report to Use a Shared Schedule

Updating and Uploading the RDL File Using Report Manager

Setting Up a Data Source for the Report

Altering Report Data Sources

Creating Snapshots for the Report History

Processing Reports and Performing Caching

Managing Subscriptions

Managing Standard Subscriptions

Creating a Standard Subscription

Configuring the Subscription

Managing Data-Driven Subscriptions

Designing the Subscription Query

Creating the Data-Driven Subscription

Performing Execution Auditing and Performance Analysis

Configuring SSRS Logging

Transforming the ExecutionLog Table

Designing the Log Report

Monitoring Performance

Controlling SSRS Programmatically

Controlling SSRS with SOAP

Controlling SSRS with WMI

Summary

Chapter 11: Securing Reports

Encrypting Data

Introducing Encryption

Securing Network Traffic Using SSL

Analyzing HTTP Traffic

Applying an SSL Certificate

Capturing HTTPS Traffic

Securing Data Storage in SSRS

Setting Up Authentication and User Access to Data

Introducing SSRS Roles

Testing SSRS Role Assignments

Filtering Report Content with User!UserID

Setting Data Source Security

Setting SQL Server Permissions

Auditing Reports

SSRS Auditing

Introducing Log File Auditing

Summary

Chapter 12: Delivering Business Intelligence with SSRS

Building SSRS Reports for SQL Analysis Services

Using an Analysis Service Cube with SSRS

Setting Up the Analysis Services Data Source

Working with the Graphical MDX Query Builder

Incorporating SSRS with Microsoft SharePoint 2010

Installing SharePoint 2010 and SQL Server 2012 on a Stand-Alone Server

Installing SharePoint 2010

Installing SQL Server 2012 Reporting Services in SharePoint Mode

Configuring SharePoint 2010

Installing and Starting the Reporting Services SharePoint Service

Creating a New Reporting Services Service Application

Configuring Reporting Services Integration with SharePoint

Deploying Reports in a SharePoint-Integrated SSRS Installation

Creating a Simple Dashboard to Display SSRS Reports

Creating Data Alerts

Summary

Chapter 13: Creating Reports Using Report Builder 1.0, 2.0, and 3.0

Getting User Feedback

Introducing the Report Model

Create a Report Model using BIDS 2008 R2

Adding a Data Source

Creating a Data Source View

Creating a Report Model

Creating Reports with Report Builder 1.0

Creating a Table Report

Creating a Matrix Report

Creating a Chart Report

Creating Reports with the Report Builder 2.0 Wizard

Creating Reports with Report Builder 2.0

Creating Reports with Report Builder 3.0

Report Parts

Summary

Index