Table of Contents

1  Introducing Microsoft Reporting Services   1

1.1 What is RS? 2

Why do we need RS? 2

How is RS implemented? 4

RS and the Microsoft BI platform 5

1.2 RS at a glance 6

Authoring features 7

Management features 8

Delivery features 9

Extensibility features 9

Scalability features 10

Security features 10

Deployment features 11

1.3 RS architecture 11

The Report Server 13

The Report Server database 14

The Report Manager 15

1.4 Understanding Report Processing 17

Execution stage 18

Rendering stage 18

1.5 Delivering reports 20

On-demand delivery 20

Subscribed delivery 21

1.6 What is the report lifecycle? 22

1.7 RS in action 23

About the Adventure Works Reporter 23

Your first report 24

1.8 Evaluating RS 33

1.9 Summary 35

1.10 Resources 35

Part 1  Authoring reports   37

2  Report authoring basics   39

2.1 The report-authoring process: step by step 40

Analysis 41

Construction 42

Testing 42

Deployment 42

2.2 Authoring reports in VS.NET 43

Authoring reports with the Report Wizard 43

Authoring reports with the Report Designer 44

Importing reports from Microsoft Access 53

2.3 Creating reports programmatically 55

The AW Ad Hoc Reporter sample 56

Implementation details 58

2.4 Creating reports with third-party tools 60

Cizer’s Quick Query 60

Hitachi’s RDL Generator 62

2.5 Summary 62

2.6 Resources 62

3  Working with data   63

3.1 Working with data sources 63

Connecting to the database 64

Choosing an authentication mechanism 69

Deploying data sources 77

3.2 Working with report datasets 77

Understanding the dataset definition 78

Creating a report dataset 79

Using multiple datasets 83

3.3 Authoring dataset queries 84

Using the Graphical Query Designer 84

Using the Generic Query Designer 86

3.4 Parameter-driven reports 89

The role of parameters 89

Building parameter-driven queries 90

Setting up the report-level parameters 92

Working with stored procedures 95

3.5 Data limitations 100

Data source limitations 100

Parameter limitations 101

3.6 Summary 101

3.7 Resources 101

4  Designing reports   102

4.1 Anatomy of a report 103

Getting started with a new report 104

Understanding report sections 104

Understanding report items 105

Understanding data regions 107

4.2 Designing tabular reports 109

Parameterized tabular reports 113

Tabular reports with interactive features 117

Table region limitations 119

4.3 Designing freeform reports 119

Freeform reports with nested regions 119

Grouping freeform data 121

Freeform reports with side-by-side data regions 123

4.4 Designing Chart Reports 124

The chart data region 124

Working with charts 126

Nesting chart regions 127

4.5 Designing crosstab reports 129

Matrix region advantages 129

Working with the matrix region 130

Adjusting the report layout 134

4.6 Designing Subreports 135

Laying out the report 136

Synchronizing the subreport with the master report 137

4.7 Designing multicolumn reports 138

Setting up multiple columns 139

Testing multicolumn reports 139

4.8 Summary 140

4.9 Resources 141

5  Using expressions and functions   142

5.1 Understanding expressions 143

Using the Expression Editor 143

Expression syntax 145

Determining expression execution order 145

Understanding expression scope 146

Dealing with expression errors 147

5.2 Exploring the Report Object Model 148

Using the ReportItems collection 151

Using the Fields collection 155

Using the Parameters collection 157

Using the Globals collection 159

Using the User collection 159

5.3 Working with functions 160

Referencing external functions 160

Using aggregate functions 161

Using other internal functions 165

5.4 Designing reports with navigational features 167

Reports with hyperlinks 168

Reports with document maps 170

5.5 Report rendering considerations 173

Exporting reports to HTML 173

Exporting reports to MHTML 174

Exporting reports to HTML with Office Web Components 174

Exporting reports to other formats 176

5.6 Designing localized reports 177

Report localization basics 177

Localization techniques 179

5.7 Summary 181

5.8 Resources 182

6  Using custom code   183

6.1 Understanding custom code 184

Using embedded code 184

Using external assemblies 187

6.2 Custom code in action: implementing report forecasting 191

Forecasting with OpenForecast 192

Implementing report forecasting features 192

6.3 Using XML-based reports 204

Understanding XML exporting 205

Exposing the report content as an RSS feed 205

6.4 Summary 210

6.5 Resources 211

Part 2  Managing reports   213

7  Managing the Reporting Services environment   215

7.1 Managing RS with the Report Manager 216

How the Report Manager works 216

Managing Report Server settings 219

Managing content 223

Managing report execution 230

Managing linked reports 240

7.2 Managing RS with the Web service 242

Understanding the Web service management API 242

Tracing calls to the SOAP API 242

Deploying reports programmatically 245

Batching methods together 249

7.3 Managing RS with the WMI provider 249

Understanding the WMI provider 250

Implementing an RS management console 251

7.4 Other ways to manage Reporting Services 253

Managing RS with the script host 253

Using other management utilities 254

7.5 Analyzing report execution 255

Analyzing the Report Server execution log 255

Analyzing trace log files 257

7.6 Summary 259

7.7 Resources 259

8  Securing Reporting Services   260

8.1 Exploring Reporting Services role-based security 261

How Windows authentication works 262

Using role-based authorization 266

Managing role-based security with the Report Manager 272

Managing role-based security with the Web service 277

8.2 Understanding code access security 281

Defining code access terminology 281

Exploring the RS default security policy 285

Managing RS code access security 286

8.3 Best practices for securing reports 290

Filtering data 291

Using dynamic dataset queries 292

Implementing custom security models 294

Enforcing a secured connection to the Report Server 294

8.4 Summary 295

8.5 Resources 295

Part 3  Delivering reports   297

9  On-demand report delivery   299

9.1 How RS provides on-demand report delivery 300

9.2 URL-based report access 301

Understanding URL syntax 302

Requesting resources by URL 303

Requesting reports by URL 305

Working with report commands 306

Working with the HTML Viewer 309

URL access in action 312

9.3 Web service-based report access 317

Requesting reports with SOAP 318

Rendering images 321

Handing report sessions 324

Automating the report generation process 327

9.4 Evaluating URL and Web service access options 331

Evaluating URL access 332

Evaluating Web service access 334

Choosing an integration approach 334

9.5 Summary 335

9.6 Resources 336

10  Reporting for Windows Forms applications   337

10.1 Rich client wanted 338

Report-enabling rich clients 338

Using the Client-to-Report Server model 339

Using the Client-to-Fa?ade-to-Report Server model 341

10.2 Introducing the Adventure Works Report Wizard 342

Designing the Report Wizard 343

The Report Wizard step-by-step 344

10.3 Behind the scenes of the Adventure Works Report Wizard 348

Implementing the application framework 349

Selecting reports 354

Dealing with snapshot reports 359

Handling report parameters 359

Specifying the export format 368

Confirming the report request 368

10.4 Enhancing application performance 370

Using in-memory caching 371

Using multithreading 372

10.5 Summary 375

10.6 Resources 376

11  Reporting for web-based applications   377

11.1 Understanding web reporting 378

Reporting for intranet applications 378

Reporting for Internet applications 379

Reporting for extranet applications 382

Introducing the Adventure Works Web Reporter 383

11.2 Client-side reporting techniques 384

Requesting reports from hyperlinks 384

Creating write-back reports 388

Using HTTP-POST 390

Calling the RS Web service on the client side 392

11.3 Server-side reporting techniques 395

Using the ReportViewer control 396

Using the Adventure Works ReportViewer control 399

Reporting off application datasets 407

Business-to-consumer reporting 409

Business-to-business reporting 412

11.4 Summary 414

11.5 Resources 415

12  Reporting for OLAP applications   416

12.1 Understanding OLAP 417

OLTP vs. OLAP 417

How Reporting Services and Analysis Services compare 418

Understanding the OLAP storage model 419

Designing OLAP solutions 422

12.2 Implementing an OLAP solution: AW Data Miner 424

Implementing the data warehouse 426

Implementing the OLAP cube 427

Authoring OLAP-based reports with RS 440

Implementing AW Data Miner 443

12.3 Summary 453

12.4 Resources 454

13  Enterprise reporting   456

13.1 Understanding enterprise reporting 457

Evaluating enterprise reporting 457

Introducing the Adventure Works Enterprise Reporter 458

13.2 Behind the scenes of the Adventure Works Enterprise Reporter 461

Implementing the Report Configuration Store 462

Implementing the presentation layer 463

Implementing the Enterprise Reporting Fa?ade 466

Designing for scalability 470

13.3 Implementing custom application authorization 472

Understanding the Windows Authorization Manager 472

Securing the AW Enterprise Reporter 478

13.4 Summary 481

13.5 Resources 481

14  Subscribed report delivery   483

14.1 Understanding subscribed report delivery 484

Subscription-based reporting scenarios 484

The subscriber-publisher design pattern 484

How the Reporting Services subscription-based model works 485

14.2 Configuring subscribed report delivery 488

Creating a new subscription 489

Choosing the subscription type 490

Configuring delivery extensions 493

Managing subscriptions 495

14.3 Subscribed report delivery in action 497

“Pushing” reports via standard e-mail subscriptions 497

Archiving reports to a file share 499

Sending reports to a data-driven list of recipients 503

Triggering subscriptions programmatically 509

14.4 Summary 513

14.5 Resources 513

Part 4  Advanced reporting   515

15  Extending Reporting Services   517

15.1 Understanding Reporting Services extensibility 518

Understanding interface-based programming 518

Working with interface inheritance 519

15.2 Reporting off ADO.NET datasets with a custom dataset data extension 523

Design goals and tradeoffs 525

Authoring dataset-bound reports 526

Implementing the custom dataset extension 533

Debugging dataset extensions 537

15.3 Distributing reports to Web services using custom delivery extensions 537

Design goals and tradeoffs 538

Using the custom delivery extension 539

Implementing the custom delivery extension 540

Debugging custom delivery extensions 544

15.4 Implementing custom security 546

Design goals and tradeoffs 549

Intranet reporting with custom security 550

Implementing the custom security extension 553

Debugging the custom security extension 560

15.5 Using custom HTTP modules 560

The HTTP module design goals and tradeoffs 561

Implementing the custom HTTP module 561

15.6 Considerations for custom rendering extensions 563

15.7 Summary 563

15.8 Resources 564

16  Performance and scalability   566

16.1 Understanding capacity planning 567

Capacity planning fundamentals 567

The capacity planning process 572

16.2 Capacity planning with Reporting Services in action 577

Determining requirements 577

Setting up the testing environment 580

Performance testing 584

Analyzing performance results 585

Identifying resource constraints 587

Eliminating resource constraints 589

16.3 Summary 592

16.4 Resources 593

A  Installing Reporting Services   594

A.1 Choosing components to install 595

A.2 Selecting the service account 596

A.3 Specifying RS virtual folders 597

A.4 Configuring the Report Server database 599

A.5 Configuring Reporting Services for e-mail delivery 600

A.6 Setting up the RS samples 601

A.7 Configuring RS licensing mode 601

A.8 Post-installation steps 603

Installing RS sample reports 603

Backing up the encryption key 604

A.9 Resources 604