ptg
Microsoft
®
SQL
Server
™
2008
Reporting
Services
UNLEASHED
800 East 96th Street, Indianapolis, Indiana 46240 USA
Michael Lisin
Jim Joseph
Amit Goyal
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Microsoft® SQL Server™ 2008 Reporting Services Unleashed
Copyright © 2010 by Sams Publishing
All rights reserved. No part of this book shall be reproduced, stored in a retrieval
system, or transmitted by any means, electronic, mechanical, photocopying, recording,
or otherwise, without written permission from the publisher. No patent liability is
assumed with respect to the use of the information contained herein. Although every
precaution has been taken in the preparation of this book, the publisher and author
assume no responsibility for errors or omissions. Nor is any liability assumed for
damages resulting from the use of the information contained herein.
ISBN-13: 978-0-672-33026-1
ISBN-10: 0-672-33026-1
Library of Congress Cataloging-in-Publication data is on file.
Printed in the United States of America
First Printing July 2009

Patrick Kanouse
Project Editor
Seth Kerney
Copy Editor
Keith Cline
Indexer
Ken Johnson
Proofreader
Geneil Breeze
Technical Editor
J. Boyd Nolan
Technical Contributor
Prash Shirolkar
Publishing
Coordinator
Cindy Teeters
Book Designer
Gary Adair
Composition
Mark Shirar
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Contents at a Glance
Introduction....................................................................................................1
Part I Introduction to Reporting Services
1 Introduction to SQL Server Reporting Services (SSRS)...................................5
2 Reporting Services 2008 Architecture...........................................................23
3 Getting Started with Reporting Services Tools .............................................43
4 What's New in SQL Server Reporting Services 2008 ....................................55

29 Extending Reporting Services .....................................................................499
Part V SharePoint Integration
30 Reporting Services Integration with SharePoint ........................................519
31 SSRS 2008 SharePoint Integration Architecture .........................................527
32 Installation of Reporting Services Integrated with SharePoint..................535
33 SharePoint Mode Administration...............................................................545
34 Tools Support for SSRS Integrated with SharePoint ...................................557
35 Viewing Reports in SharePoint...................................................................565
36 Managing Reports in SharePoint................................................................573
37 Ad Hoc Reporting in SharePoint ................................................................589
Part VI Appendixes
A References and Additional Reading............................................................597
B Glossary.......................................................................................................601
C Frequently Asked Questions and Additional Information.........................613
D What's New in SQL Server SP1?..................................................................617
E What's New in SQL Server 2008 R2? ..........................................................623
Index ...........................................................................................................624
Microsoft®SQL Server™ 2008 Reporting Services
iv
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Table of Contents
Introduction 1
Part I Introduction to Reporting Services
1 Introduction to SQL Server Reporting Services (SSRS) 5
What Is SSRS?..................................................................................................6
SSRS for End Users ..........................................................................................6
Overview of Features.......................................................................................8
Enterprise Report Examples ............................................................................9

Reporting Services Configuration Tool.........................................................39
RSPrintClient
Control.................................................................................39
WMI Provider................................................................................................39
Performance Monitoring Objects .................................................................39
Summary .......................................................................................................40
3 Getting Started with Reporting Services Tools 43
Report Manager.............................................................................................43
Business Intelligence Development Studio ..................................................44
Report Designer.............................................................................................46
Report Builder 1.0 .........................................................................................46
Report Builder 2.0 .........................................................................................48
Reporting Services Configuration Manager .................................................50
SQL Server Management Studio ...................................................................51
Summary .......................................................................................................53
4 What’s New in SQL Server Reporting Services 2008 55
Report Server Architecture Changes.............................................................56
Reporting Services Configuration Manager .................................................58
SQL Server Management Studio Updates for Administrators......................58
Report Manager Update................................................................................62
Report Engine Architecture Changes ...........................................................63
Report-Processing Scalability Enhancements ...............................................65
Report-Rendering Enhancements .................................................................67
ReportViewer
Control Enhancements .........................................................69
Report Designer Enhancements ...................................................................69
Report Builder 2.0 .........................................................................................69
Tablix.............................................................................................................70
Data-Visualization Controls..........................................................................71
Rich-Text Support .........................................................................................73

Summary .......................................................................................................97
6 Installing Reporting Services 99
Summary .....................................................................................................109
Part II Report Authoring from Basic to Advanced
7 Report Server Project Wizard 111
Sample Database Setup ...............................................................................111
Using the Report Server Project Wizard to Create a Simple Report ..........112
Summary .....................................................................................................118
8 Report Designer(s) 119
Three Main Report Designers from Microsoft............................................119
Visual Studio Haves Versus Have Nots .......................................................121
Solution, Project, File Hierarchy.................................................................121
Generating Reports with Visual Studio ......................................................122
Design Screen.....................................................................................131
Summary .....................................................................................................135
9 Report Definition Language 137
Language: A Way to Communicate............................................................137
Use of XML .................................................................................................138
Declarative Programming ...........................................................................138
Report Elements ..........................................................................................139
Report
Element..................................................................................140
ReportParameters
Element ...............................................................140
DataSets
Element ...........................................................................144
Contents
vii
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Expression and Built-In Function Security.................................................174
Using Expressions to Change Report Item Properties................................175
Emphasizing Certain Values .......................................................................176
Summary .....................................................................................................177
11 Accessing Data 179
Data-Processing Extensions ........................................................................180
Types of Data Sources .................................................................................180
Report-Specific Data Sources .............................................................181
Shared Data Sources...........................................................................181
Data Source Expressions ....................................................................181
Data Source Credentials..............................................................................182
Connection Strings .....................................................................................183
Querying Data.............................................................................................186
Graphical Query Designer .................................................................186
Generic Query Designer ....................................................................187
Data Set Properties.............................................................................188
Command Type .................................................................................189
Parameters..........................................................................................190
Querying XML ...................................................................................192
Fields ..................................................................................................193
Microsoft®SQL Server™ 2008 Reporting Services
viii
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Fields and XML..................................................................................194
Filters..................................................................................................195
Adding a Data Source..................................................................................195
Summary .....................................................................................................195
12 Report Parameters 197

14 Grouping, Sorting, Aggregating Data, and Working with Scope 251
Grouping Data on a Report ........................................................................251
Sorting, Including Interactive Sorting and Data Source Sorting ...............254
Data Source Sorting ...........................................................................254
Contents
ix
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Data Region and Group Sorting........................................................255
Interactive User Sorting.....................................................................256
Scope
Parameter of Aggregate Functions...................................................258
Level
and
InScope
Functions....................................................................260
Summary .....................................................................................................261
15 Advanced Report Formatting 263
Formatting-Related Report Item Properties................................................264
Formatting Numeric and Date/Time Values ..............................................267
Standard Numeric Format Strings .....................................................268
Custom Numeric Format Strings.......................................................270
Standard Date/Time Format Strings..................................................272
Custom Date/Time Formatting .........................................................273
Creating Alternating Colors for the Lines on a Report..............................276
Paging Report (Improved in 2008) .............................................................277
PageHeight
and
PageWidth

17 Working with Multidimensional Data Sources 295
Analysis Services Concepts .........................................................................295
Data-Mining Concepts................................................................................298
Creating a Data-Mining Model .........................................................298
MDX and DMX...........................................................................................300
Advanced OLAP Concepts ..........................................................................300
Creating Data-Mining Reports....................................................................323
Summary .....................................................................................................326
Microsoft®SQL Server™ 2008 Reporting Services
x
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
18 Ad Hoc Reporting 327
Issues Facing Ad Hoc Reporting .................................................................327
Client-Side Reporting with SSRS.................................................................328
Report Models and the Model Designer.....................................................328
Report Model Projects .......................................................................329
Model File Content............................................................................329
Features of Report Builder .................................................................339
Building Reports with Report Builder ...............................................342
Report Builder 2.0 .......................................................................................345
Report Builder 2.0 Features................................................................346
Installing Report Builder 2.0 .............................................................347
Design Surface....................................................................................348
Ribbons ..............................................................................................349
Other Dialog Boxes............................................................................350
Managing Data Sources and Data Sets ..............................................352
Report Layout ....................................................................................354
Summary .....................................................................................................359

20 Securing Report Server Items 377
Reporting Services Security Model .............................................................377
What Can Be Secured? ......................................................................378
How Role Assignments Work ............................................................379
Relationships Between Roles, Tasks, and Users.................................380
Overview of Built-In Roles.................................................................381
Assigning Built-In Roles.....................................................................383
Defining Custom Roles...............................................................................386
Creating/Modifying a Custom Role ..................................................387
Summary .....................................................................................................388
21 Report Execution and Processing 389
Managing Schedules ...................................................................................389
Types of Schedules.............................................................................390
Creating/Modifying Schedules ..........................................................390
Report Execution and Processing ...............................................................392
Report-Execution Timeouts ...............................................................393
Running Processes .............................................................................394
Large Reports .....................................................................................394
Report-Execution History ...........................................................................395
What Is the Execution Log? ..............................................................395
How to Report Off the Execution Log ..............................................396
Creating the RSExecutionLog Database ............................................396
Overview of the Sample Reports .......................................................398
Summary .....................................................................................................399
22 Subscribing to Reports 401
Overview of Subscriptions ..........................................................................401
Parts of Subscriptions ........................................................................401
Uses of Subscriptions.........................................................................402
Standard Versus Data-Driven Subscriptions......................................402
Delivery Options................................................................................402

Scale-Out Deployment................................................................................424
Summary .....................................................................................................425
24 RS Utility 427
Inside the Reporting Services Script Files ...................................................427
Command-Line Arguments ........................................................................428
Sample Command Lines.............................................................................430
Code Examples............................................................................................430
Summary .....................................................................................................432
Part IV Reporting Services Customizations
25 Implementing Custom Embedded Functions 433
Adding Embedded Code .............................................................................433
Debugging Embedded Code .......................................................................437
Summary .....................................................................................................439
26 Creating and Calling a Custom Assembly from a Report 441
Initializing Assembly Classes ......................................................................446
Strong-Named Custom Assemblies.............................................................450
.NET Security Primer for an SSRS Administrator........................................450
Assemblies That Require Other Than
Execute
Permissions ......................457
Debugging Custom Assemblies ..................................................................460
Summary .....................................................................................................474
Contents
xiii
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
27 Using URL Access 465
How to Control Rendering Through URL Access ......................................465
How to Integrate URL Access in an Application........................................472

Summary .....................................................................................................526
31 SSRS 2008 SharePoint Integration Architecture 527
Reporting Services Add-In for SharePoint ..................................................528
Report Server Integrated Mode..........................................................529
Database Management ......................................................................530
Microsoft®SQL Server™ 2008 Reporting Services
xiv
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Security Management ........................................................................531
Deployment Architecture ..................................................................532
Summary .....................................................................................................533
32 Installation of Reporting Services Integrated with SharePoint 535
Installing Reporting Services ......................................................................536
Installing SharePoint...................................................................................536
Configuring Report Server in SharePoint Integrated Mode.......................537
Installing the RS Add-In for SharePoint .....................................................538
Configuring Report Server Integration via SharePoint Central
Administration..........................................................................................538
Upgrading from SSRS2K5 SP2.....................................................................542
Scaling-Out Deployments ...........................................................................542
Troubleshooting ..........................................................................................543
Summary .....................................................................................................543
33 SharePoint Mode Administration 545
Security Overview .......................................................................................545
User Authentication with SharePoint ........................................................546
Windows Integrated Security......................................................................547
Trusted Account with Windows or Forms Authentication ........................548
User Authorization with SharePoint ..........................................................549

SharePoint Document Management ..........................................................584
Summary .....................................................................................................587
37 Ad Hoc Reporting in SharePoint 589
Report Builder Reports in SharePoint.........................................................590
Data Sources in SharePoint.........................................................................593
Report Models in SharePoint......................................................................594
Report Builder 2.0 .......................................................................................594
Tips for Report Builder Usage .....................................................................595
Summary .....................................................................................................596
Part VI Appendixes
A References and Additional Reading 597
MSDN Websites...........................................................................................597
SQL Customer Advisory Team Technical Notes .........................................597
Microsoft SQL Server 2008 Samples ...........................................................598
SQL Server Reporting Services 2008 Forum ...............................................598
Blogs ............................................................................................................598
White Papers ...............................................................................................599
SharePoint Integration Links......................................................................599
Additional Books.........................................................................................600
B Glossary 601
C Frequently Asked Questions and Additional Information 613
D What’s New in SQL Server SP1? 617
What’s New in SP1? ....................................................................................617
E What’s New in SQL Server 2008 R2? 623
Map and Spatial Visualizations...................................................................624
Grab and Go Reporting ..............................................................................624
Microsoft®SQL Server™ 2008 Reporting Services
xvi
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

He earned his bachelor of science degree from UIUC (University of Illinois at Urbana
Champaign) and master’s degree from Stanford University.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg
Dedications
Michael Lisin: Dedicated to my family: my wife, Anna; my chil-
dren (Helen, Noah, and Alexander); my mother; and the memory of
my grandfather. Thank you all for always being extremely support-
ive in my life endeavors.
Jim Joseph: Dedicated to my wife, Deseere, and my son,
Christopher. Thanks for having the patience to deal with an absent
daddy.
Amit Goyal: Dedicated to my loving parents, Mr. Ram Charan
and Mrs. Manju Goyal; my wife, Meenakshi Agarwal; my daughter,
Aditi; and my sister, Rashmi.
Acknowledgments
The authors would like to thank the publishing team for working with us on this book.
Special thanks to Brook, Keith, Mark, Neil, J. Boyd, Seth, and others. Thank you for your
valuable feedback, answers, and hard work on this book. Without you all, this book
would not be possible.
Thank you to Matt Whitten and Stephen Rauch for getting Michael started with
Reporting Services and to Dr. John Groth of Texas A&M for instilling creativity in your
students. Thank you to Kevin Swailes for pointers about exception reports.
Amit Goyal would like to thank his colleagues on the SQL Server Reporting Services team
for help with content from their areas of expertise, especially Prash Shirolkar, Lukasz
Pawlowski, Neeraja Divakaruni, Robert Bruckner, Nico Cristache, and Sean Boon.
From the Library of STEPHEN EISEMAN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ptg