SQL Server 2005 Reporting Services in Action (Paperback)

Description

"Helps you unlock Reporting Services."
-- From the Foreword by Brian Welcker, Group Program Manager, Microsoft SQL Server Reporting Services

"Insightful, professional, and full of information."
-- Dan Hounshell, Telligent Systems

"Incredible technical depth, interesting examples and solutions. Very well done."
-- Dave Corun, Director of Microsoft Technologies, Catalyst IT Services

"Great case studies!"
-- Nuo Yan, Microsoft MVP, MCDBA

"Excellent writing, very detailed."
-- Berndt Hamboeck, Team Leader, United Nations

Reports are the lifeline of business, so a good reporting environment is a big deal. With a powerful tool like Microsoft Reporting Services, .NET developers can add reporting to any type of application, regardless of its target platform or development language. Greatly improved for SQL Server 2005, Reporting Services now provides tighter integration with SQL Server, improved developer tools, and an expanded array of options to empower end users.

SQL Server 2005 Reporting Services in Action helps you build and manage flexible reporting solutions and develop report-enabled applications. In this clear, well-illustrated book, you'll follow a report from creation to publication. Along the way you'll explore flexible delivery options like web-based, on-demand, and subscribed reportscomplete with cool new features like direct printing and client-side sorting.

For applications that require custom reporting, you'll learn to define reports with RDL and push them to the Report Server using the Report Manager Web Service API. You'll also see how to write server extensions to expand the range of data processing and report delivery options.

Written for developers with a solid foundation in .NET and SQL Server.

 

foreword xvii
foreword to the first edition xix
preface xxi
preface to the first edition xxii
acknowledgments xxiv
about this book xxvi
about the source code xxix
about the authors xxxiv
about the cover illustration xxxv

1  Introducing SQL Server 2005 Reporting Services   1

1.1 What is Reporting Services? 2

Solving reporting problems with Reporting Services 3

Choosing a Reporting Services edition 4

Reporting Services and the Microsoft SQL Server platform 6

1.2 Reporting Services at a glance 7

Authoring features 8

Management features 10

Delivery features 11

Extensibility features 11

Scalability features 12

Security features 12

Deployment features 12

1.3 RS architecture 13

The Report Server engine 15

The Report Server database 16

The Report Manager 17

1.4 Understanding report processing 19

Execution stage 20

Rendering stage 20

1.5 Delivering reports 22

On-demand delivery 22

Subscribed delivery 23

1.6 What is the report lifecycle? 24

1.7 RS in action 25

About the Adventure Works Reporter 26

Your first report 26

1.8 Evaluating RS 36

1.9 Summary 37

Part 1  Authoring reports   39

2  Report authoring basics   41

2.1 The report-authoring process: step by step 42

Analysis 43

Construction 43

Testing 44

Deployment 44

2.2 Authoring reports in VS .NET 44

Authoring reports with the Report Wizard 45

Authoring reports with the Report Designer 46

Importing reports from Microsoft Access 55

2.3 Creating reports programmatically 57

Generating RDL: The AW Ad Hoc Reporter 59

Implementation details 60

2.4 Summary 62

3  Working with data   64

3.1 Working with data sources 64

Connecting to the database 65

Choosing an authentication mechanism 70

Deploying data sources 78

3.2 Working with report datasets 78

Understanding the dataset definition 79

Creating a report dataset 80

Using multiple datasets 84

3.3 Authoring dataset queries 85

Using the Graphical Query Designer 85

Using the Generic Query Designer 87

3.4 Parameter-driven reports 90

The role of parameters 90

Building parameter-driven queries 91

Setting up the report-level parameters 93

3.5 Working with stored procedures 98

Using a stored procedure as a dataset query 98

Defining query-based lookup parameter values 100

Creating cascading parameters 101

Working with multivalued parameters 102

3.6 Summary 102

4  Designing reports   104

4.1 Anatomy of a report 105

Getting started with a new report 106

Understanding report sections 106

Understanding report items 107

Understanding data regions 110

4.2 Designing tabular reports 112

Tabular reports with groups 113

Parameterized tabular reports 116

Tabular reports with interactive features 121

Table region limitations 123

4.3 Designing freeform reports 123

Freeform reports with nested regions 123

Grouping freeform data 125

Freeform reports with side-by-side data regions 127

4.4 Designing chart reports 128

The chart data region 129

Working with charts 129

Nesting chart regions 132

4.5 Designing crosstab (matrix) reports 133

Matrix region advantages 133

Working with the matrix region 134

Adjusting the report layout 138

4.6 Using other design features in your reports 140

Setting up multiple columns 140

Adding interactive sort 142

Passing multivalued parameters into a stored procedure 144

Designing subreports 145

4.7 Summary 148

5  Using expressions and functions   150

5.1 Understanding expressions 151

Using the Expression Editor 152

Expression syntax 153

Determining expression execution order 154

Understanding expression scope 154

Dealing with expression errors 156

5.2 Exploring the Report Object Model 157

Using the ReportItems collection 159

Using the Fields collection 163

Using the Parameters collection 166

Using the Globals collection 166

Using the User collection 167

5.3 Working with functions 167

Referencing external functions 168

Using aggregate functions 168

Using other internal functions 173

5.4 Designing reports with navigational features 175

Reports with hyperlinks 176

Reports with document maps 178

5.5 Report rendering considerations 181

Exporting reports to HTML 181

Exporting reports to MHTML 182

Exporting reports to other formats 182

5.6 Summary 183

6  Using custom code   185

6.1 Understanding custom code 186

Using embedded code 186

Using external assemblies 189

6.2 Custom code in action: implementing report forecasting 193

Forecasting with OpenForecast 194

Implementing report forecasting features 194

6.3 Using XML-based reports 207

Understanding XML exporting 208

Exposing the report content as an RSS feed 209

6.4 Summary 213

7  Ad hoc reporting with the Report Builder application   215

7.1 About the Report Builder application 216

A quick tour of the Report Builder 217

Setting up for ad hoc reporting 218

The Developer toolset for ad hoc reporting 220

7.2 Building the Report Model using BIDS 221

Building a report model project 222

Setting up the data source 223

Creating a data source view 227

Building the report model 233

Deploying report models 241

7.3 Ad hoc reporting in action 242

Launching the Report Builder 242

Creating the Product Profit Margin report 244

Getting more with infinite drill-through reporting 247

Advanced filtering with the Report Builder 249

7.4 Implementing Report Builder security 252

Securing the Report Builder with roles 252

Securing report model items 253

7.5 Summary 255

Part 2  Managing reports   257

8  Managing the Reporting Services environment   259

8.1 Managing RS with the Report Manager 260

How the Report Manager works 260

Managing Report Server settings 263

Managing content 268

Managing report execution 275

Managing linked reports 286

8.2 Managing RS with the Web service 288

Using the Web service management API 288

Tracing calls to the SOAP API 289

Deploying reports programmatically 292

Batching methods together 295

8.3 Managing RS with the WMI provider 296

Understanding the WMI provider 296

Implementing an RS management console 297

8.4 Other ways to manage Reporting Services 299

Managing RS with SQL Management Studio 299

Managing RS with the script host 300

Using the Reporting Services configuration tool 302

8.5 Analyzing report execution 305

Analyzing the Report Server execution log 305

Analyzing trace log files 307

8.6 Summary 309

9  Securing Reporting Services   311

9.1 Role-based security basics 312

The purpose of role-based security 312

Authentication models: using Windows or creating your own 312

9.2 Windows authentication: a closer look 313

Exploring the Client-to-Report Server model 314

Exploring the Client-to-Fa蓷de-to-Report Server model 316

9.3 Using role-based authorization 318

Understanding tasks 319

Defining roles 320

Understanding securable items 321

Defining policies 322

9.4 Managing role-based security with the Report Manager 324

Creating Windows user accounts and groups 325

Creating custom roles 326

Defining security policies 327

9.5 Managing role-based security with the Web service 329

Determining role-based security policies 329

Calling security-related Web service methods 330

Implementing “pseudo” report events 332

9.6 Techniques for advanced security issues 333

Filtering data 334

Using dynamic dataset queries 335

Hiding data 337

Implementing custom security models 337

Enforcing a secured connection to the Report Server 337

9.7 Summary 338

Part 3  Delivering reports   339

10  On-demand report delivery   341

10.1 How RS provides on-demand report delivery 342

10.2 URL-based report access 343

Understanding URL syntax 344

Requesting resources by URL 345

Requesting reports by URL 347

Working with report commands 348

Working with the HTML Viewer 351

10.3 URL access in action 354

URL Access with WinForms: AccessOptions 355

URL Access with WebForms: AWReporterWeb 358

10.4 Web service-based report access 366

Requesting reports with SOAP 368

Rendering images 370

Handling report sessions 373

10.5 Web service access (SOAP) in action 376

Generating report requests with SOAP on the client side 376

An automation solution: AW Campaigner 379

10.6 Evaluating URL and Web service access options 383

Evaluating URL access 384

Evaluating Web service access 385

Choosing an integration approach 386

10.7 Summary 387

11  Mastering the ReportViewer controls   389

11.1 How the .NET ReportViewer controls work 390

Controls for web and Windows applications 390

Choosing remote or local mode 391

Managing properties of the ReportViewer controls 392

11.2 Using ReportViewer in remote mode 394

Creating, configuring, and running the control 394

Additional customizations for the ReportViewer control 397

11.3 Using ReportViewer in local mode 397

Creating a local report with a database as the data source 398

Creating a local report with an object as the data source 401

11.4 Custom validation with the ReportViewer control 406

Creating a parameters section 407

Creating event methods 407

Write validation code 410

11.5 Converting report files 411

Converting RDL files into RDLC files 412

Converting RDLC files into RDL files 415

11.6 Deploying applications that use ReportViewer controls 416

Redistributing the ReportViewer controls 416

ReportViewer deployment for Windows applications 417

ReportViewer deployment for web applications 418

Using the ReportViewer web server control in a web farm 418

11.7 Summary 418

12  Subscribed report delivery   420

12.1 Understanding subscribed report delivery 421

Subscription-based reporting scenarios 421

The subscriber-publisher design pattern 422

How the RS subscription-based model works 422

12.2 Configuring subscribed report delivery 425

Creating a new subscription 426

Choosing the subscription type 427

Configuring delivery extensions 430

Managing subscriptions 432

12.3 Subscribed report delivery in action 434

“Pushing” reports via standard e-mail subscriptions 434

Archiving reports to a file share 437

Sending reports to a data-driven list of recipients 440

Triggering subscriptions programmatically 446

12.4 Summary 450

Part 4  Advanced reporting   451

13  Extending Reporting Services   453

13.1 Understanding Reporting Services extensibility 454

Interface-based programming 454

Working with interface inheritance 456

Extending RS with interface inheritance 459

13.2 Reporting with a custom dataset data extension 460

Identifying design goals and trade-offs 461

Authoring dataset-bound reports 462

Implementing the custom dataset extension 469

Debugging dataset extensions 473

13.3 Distributing reports to Web services using custom delivery extensions 473

Design goals and trade-offs 474

Using the custom delivery extension 475

Implementing the custom delivery extension 476

Debugging custom delivery extensions 480

13.4 Implementing custom security 482

Design goals and trade-offs 485

Intranet reporting with custom security 486

Implementing the custom security extension 488

Debugging the custom security extension 495

13.5 Summary 496

14  Performance and scalability   497

14.1 Understanding capacity planning 498

Capacity-planning fundamentals 498

The capacity-planning process 503

14.2 Capacity planning for Reporting Services in action 508

Determining requirements 508

Setting up the testing environment 511

Performance testing 515

Analyzing performance results 516

Identifying resource constraints 519

Eliminating resource constraints 520

14.3 Summary 523

appendix A  Installing SQL Server Reporting Services   524

A.1 Software requirements 524

A.2 Installing SQL Server Reporting Services 525

appendix B  Understanding .NET code access security   531

B.1 Code access security basics 532

B.2 Understanding code access terminology 532

B.3 Exploring the RS default security policy 536

B.4 Managing RS code access security 538

resources 542
index 549