SQL Server 2005 Reporting Services in Action

Bret Updegraff9781932394764, 1-932394-76-1

“A great resource to help you unlock the hidden gems in SQL Server Reporting Services.Brian WelckerGroup Program ManagerMicrosoft SQL Server Reporting ServicesWith Microsoft SQL Server 2005 you can structure and store terabytes of data-but how do you make sense of that much information? Reporting Services transforms raw data into meaningful reports and puts your valuable information to work.SQL Server 2005 Reporting Services in Action shows you how to build effective Windows and Web-based reporting solutions using SQL Server 2005. Using this example-driven book, you’ll build Reporting Services-centric solutions, including report forecasting, RSS feeds, ASP.NET web controls for server-side report generation, extending Reporting Services to report on ADO.NET datasets, delivering reports to Web services, authenticating users, performance testing, and capacity planning.You’ll quickly move beyond canned reports and put the Report Designer through its paces. The book will show you how to add interactive features to your tabular reports and how to improve performance by using external images without taking up valuable database real estate.When you have large systems, efficiency is key to productivity. Author Bret Updegraff shows you how to avoid doing the same tasks twice. You’ll see a nifty sales forecast that uses embedded code to create reusable utility functions that you can call from multiple places your reports. As well, you’ll find fundamentals like employing XSLT to create XML reports and advanced techniques such as accessing custom code in an external .NET assembly.Reporting Services 2005 lets you give users greater control over their reporting needs. SQL Server 2005 Reporting Services in Action will help you provide safe, user-friendly reporting options to your users and free up your time for more important tasks. This book shows you how to set up ad hoc reporting through Report Builder so business users can get the information you need from the database without your help! You won’t stop with the basics, though. You’ll also learn how to apply advanced filters and security practices to protect your data from unwelcome or misguided queries.Of course, this new edition is thoroughly updated to include coverage of all the new features introduced with SQL Server 2005. You’ll see:# Direct Client Printing: Send a report directly to a printer without having to export to printable format first# End User Sort: Let the end user to re-sort data within the report# Multi Valued Parameters: Use multiple values for parameters# Custom Report Items: Build and embed custom server controlsYou’ll also work with the improved Report Designer, Report Builder, and Analysis Services Query Builder. You’ll learn about SharePoint WebParts and the Report Viewer Controls for WinForms and ASP.NET, as well.

---

Table of contents :
SQL Server 2005 Reporting Services in Action……Page 1
contents……Page 10
preface……Page 22
about this book……Page 27
Author Online……Page 29
about the authors……Page 35
Introducing SQL Server 2005 Reporting Services……Page 38
1.1 What is Reporting Services?……Page 39
1.1.1 Solving reporting problems with Reporting Services……Page 40
1.1.2 Choosing a Reporting Services edition……Page 41
1.1.3 Reporting Services and the Microsoft SQL Server platform……Page 43
1.2 Reporting Services at a glance……Page 44
1.2.1 Authoring features……Page 45
1.2.2 Management features……Page 47
1.2.4 Extensibility features……Page 48
1.2.7 Deployment features……Page 49
1.3 RS architecture……Page 50
1.3.1 The Report Server engine……Page 52
1.3.2 The Report Server database……Page 53
1.3.3 The Report Manager……Page 54
1.4 Understanding report processing……Page 56
1.4.2 Rendering stage……Page 57
1.5.1 On-demand delivery……Page 59
1.5.2 Subscribed delivery……Page 60
1.6 What is the report lifecycle?……Page 61
1.7 RS in action……Page 62
1.7.2 Your first report……Page 63
1.8 Evaluating RS……Page 73
1.9 Summary……Page 74
Part 1 – Authoring reports……Page 76
Report authoring basics……Page 78
2.1 The report-authoring process: step by step……Page 79
2.1.2 Construction……Page 80
2.2 Authoring reports in VS .NET……Page 81
2.2.1 Authoring reports with the Report Wizard……Page 82
2.2.2 Authoring reports with the Report Designer……Page 83
2.2.3 Importing reports from Microsoft Access……Page 92
2.3 Creating reports programmatically……Page 94
2.3.1 Generating RDL: The AW Ad Hoc Reporter……Page 96
2.3.2 Implementation details……Page 97
2.4 Summary……Page 99
3.1 Working with data sources……Page 101
3.1.1 Connecting to the database……Page 102
3.1.2 Choosing an authentication mechanism……Page 107
3.2 Working with report datasets……Page 115
3.2.1 Understanding the dataset definition……Page 116
3.2.2 Creating a report dataset……Page 117
3.2.3 Using multiple datasets……Page 121
3.3.1 Using the Graphical Query Designer……Page 122
3.3.2 Using the Generic Query Designer……Page 124
3.4.1 The role of parameters……Page 127
3.4.2 Building parameter-driven queries……Page 128
3.4.3 Setting up the report-level parameters……Page 130
3.5.1 Using a stored procedure as a dataset query……Page 135
3.5.2 Defining query-based lookup parameter values……Page 137
3.5.3 Creating cascading parameters……Page 138
3.6 Summary……Page 139
Designing reports……Page 141
4.1 Anatomy of a report……Page 142
4.1.2 Understanding report sections……Page 143
4.1.3 Understanding report items……Page 144
4.1.4 Understanding data regions……Page 147
4.2 Designing tabular reports……Page 149
4.2.1 Tabular reports with groups……Page 150
4.2.2 Parameterized tabular reports……Page 153
4.2.3 Tabular reports with interactive features……Page 158
4.3.1 Freeform reports with nested regions……Page 160
4.3.2 Grouping freeform data……Page 162
4.3.3 Freeform reports with side-by-side data regions……Page 164
4.4 Designing chart reports……Page 165
4.4.2 Working with charts……Page 166
4.4.3 Nesting chart regions……Page 169
4.5.1 Matrix region advantages……Page 170
4.5.2 Working with the matrix region……Page 171
4.5.3 Adjusting the report layout……Page 175
4.6.1 Setting up multiple columns……Page 177
4.6.2 Adding interactive sort……Page 179
4.6.3 Passing multivalued parameters into a stored procedure……Page 181
4.6.4 Designing subreports……Page 182
4.7 Summary……Page 185
Using expressions and functions……Page 187
5.1 Understanding expressions……Page 188
5.1.1 Using the Expression Editor……Page 189
5.1.2 Expression syntax……Page 190
5.1.4 Understanding expression scope……Page 191
5.1.5 Dealing with expression errors……Page 193
5.2 Exploring the Report Object Model……Page 194
5.2.1 Using the ReportItems collection……Page 196
5.2.2 Using the Fields collection……Page 200
5.2.4 Using the Globals collection……Page 203
5.3 Working with functions……Page 204
5.3.2 Using aggregate functions……Page 205
5.3.3 Using other internal functions……Page 210
5.4 Designing reports with navigational features……Page 212
5.4.1 Reports with hyperlinks……Page 213
5.4.2 Reports with document maps……Page 215
5.5.1 Exporting reports to HTML……Page 218
5.5.3 Exporting reports to other formats……Page 219
5.6 Summary……Page 220
Using custom code……Page 222
6.1.1 Using embedded code……Page 223
6.1.2 Using external assemblies……Page 226
6.2 Custom code in action: implementing report forecasting……Page 230
6.2.2 Implementing report forecasting features……Page 231
6.3 Using XML-based reports……Page 244
6.3.1 Understanding XML exporting……Page 245
6.3.2 Exposing the report content as an RSS feed……Page 246
6.4 Summary……Page 250
Ad hoc reporting with the Report Builder application……Page 252
7.1 About the Report Builder application……Page 253
7.1.1 A quick tour of the Report Builder……Page 254
7.1.2 Setting up for ad hoc reporting……Page 255
7.1.3 The Developer toolset for ad hoc reporting……Page 257
7.2 Building the Report Model using BIDS……Page 258
7.2.1 Building a report model project……Page 259
7.2.2 Setting up the data source……Page 260
7.2.3 Creating a data source view……Page 264
7.2.4 Building the report model……Page 270
7.2.5 Deploying report models……Page 278
7.3.1 Launching the Report Builder……Page 279
7.3.2 Creating the Product Profit Margin report……Page 281
7.3.3 Getting more with infinite drill-through reporting……Page 284
7.3.4 Advanced filtering with the Report Builder……Page 286
7.4.1 Securing the Report Builder with roles……Page 289
7.4.2 Securing report model items……Page 290
7.5 Summary……Page 292
Part 2 – Managing reports……Page 294
Managing the Reporting Services environment……Page 296
8.1.1 How the Report Manager works……Page 297
8.1.2 Managing Report Server settings……Page 300
8.1.3 Managing content……Page 305
8.1.4 Managing report execution……Page 312
8.1.5 Managing linked reports……Page 323
8.2.1 Using the Web service management API……Page 325
8.2.2 Tracing calls to the SOAP API……Page 326
8.2.3 Deploying reports programmatically……Page 329
8.2.4 Batching methods together……Page 332
8.3.1 Understanding the WMI provider……Page 333
8.3.2 Implementing an RS management console……Page 334
8.4.1 Managing RS with SQL Management Studio……Page 336
8.4.2 Managing RS with the script host……Page 337
8.4.3 Using the Reporting Services configuration tool……Page 339
8.5.1 Analyzing the Report Server execution log……Page 342
8.5.2 Analyzing trace log files……Page 344
8.6 Summary……Page 346
Securing Reporting Services……Page 348
9.1.2 Authentication models: using Windows or creating your own……Page 349
9.2 Windows authentication: a closer look……Page 350
9.2.1 Exploring the Client-to-Report Server model……Page 351
9.2.2 Exploring the Client-to-Façade-to-Report Server model……Page 353
9.3 Using role-based authorization……Page 355
9.3.1 Understanding tasks……Page 356
9.3.2 Defining roles……Page 357
9.3.3 Understanding securable items……Page 358
9.3.4 Defining policies……Page 359
9.4 Managing role-based security with the Report Manager……Page 361
9.4.1 Creating Windows user accounts and groups……Page 362
9.4.2 Creating custom roles……Page 363
9.4.3 Defining security policies……Page 364
9.5.1 Determining role-based security policies……Page 366
9.5.2 Calling security-related Web service methods……Page 367
9.5.3 Implementing “pseudo” report events……Page 369
9.6 Techniques for advanced security issues……Page 370
9.6.1 Filtering data……Page 371
9.6.2 Using dynamic dataset queries……Page 372
9.6.5 Enforcing a secured connection to the Report Server……Page 374
9.7 Summary……Page 375
Part 3 – Delivering reports……Page 376
On-demand report delivery……Page 378
10.1 How RS provides on-demand report delivery……Page 379
10.2 URL-based report access……Page 380
10.2.1 Understanding URL syntax……Page 381
10.2.2 Requesting resources by URL……Page 382
10.2.3 Requesting reports by URL……Page 384
10.2.4 Working with report commands……Page 385
10.2.5 Working with the HTML Viewer……Page 388
10.3 URL access in action……Page 391
10.3.1 URL Access with WinForms: AccessOptions……Page 392
10.3.2 URL Access with WebForms: AWReporterWeb……Page 395
10.4 Web service-based report access……Page 403
10.4.1 Requesting reports with SOAP……Page 405
10.4.2 Rendering images……Page 407
10.4.3 Handling report sessions……Page 410
10.5.1 Generating report requests with SOAP on the client side……Page 413
10.5.2 An automation solution: AW Campaigner……Page 416
10.6 Evaluating URL and Web service access options……Page 420
10.6.1 Evaluating URL access……Page 421
10.6.2 Evaluating Web service access……Page 422
10.6.3 Choosing an integration approach……Page 423
10.7 Summary……Page 424
Mastering the ReportViewer controls……Page 426
11.1.1 Controls for web and Windows applications……Page 427
11.1.2 Choosing remote or local mode……Page 428
11.1.3 Managing properties of the ReportViewer controls……Page 429
11.2.1 Creating, configuring, and running the control……Page 431
11.3 Using ReportViewer in local mode……Page 434
11.3.1 Creating a local report with a database as the data source……Page 435
11.3.2 Creating a local report with an object as the data source……Page 438
11.4 Custom validation with the ReportViewer control……Page 443
11.4.2 Creating event methods……Page 444
11.4.3 Write validation code……Page 447
11.5 Converting report files……Page 448
11.5.1 Converting RDL files into RDLC files……Page 449
11.5.2 Converting RDLC files into RDL files……Page 452
11.6.1 Redistributing the ReportViewer controls……Page 453
11.6.2 ReportViewer deployment for Windows applications……Page 454
11.7 Summary……Page 455
Subscribed report delivery……Page 457
12.1.1 Subscription-based reporting scenarios……Page 458
12.1.3 How the RS subscription-based model works……Page 459
12.2 Configuring subscribed report delivery……Page 462
12.2.1 Creating a new subscription……Page 463
12.2.2 Choosing the subscription type……Page 464
12.2.3 Configuring delivery extensions……Page 467
12.2.4 Managing subscriptions……Page 469
12.3.1 “Pushing” reports via standard e-mail subscriptions……Page 471
12.3.2 Archiving reports to a file share……Page 474
12.3.3 Sending reports to a data-driven list of recipients……Page 477
12.3.4 Triggering subscriptions programmatically……Page 483
12.4 Summary……Page 487
Part 4 – Advanced reporting……Page 488
Extending Reporting Services……Page 490
13.1.1 Interface-based programming……Page 491
13.1.2 Working with interface inheritance……Page 493
13.1.3 Extending RS with interface inheritance……Page 496
13.2 Reporting with a custom dataset data extension……Page 497
13.2.1 Identifying design goals and trade-offs……Page 498
13.2.2 Authoring dataset-bound reports……Page 499
13.2.3 Implementing the custom dataset extension……Page 506
13.3 Distributing reports to Web services using custom delivery extensions……Page 510
13.3.1 Design goals and trade-offs……Page 511
13.3.2 Using the custom delivery extension……Page 512
13.3.3 Implementing the custom delivery extension……Page 513
13.3.4 Debugging custom delivery extensions……Page 517
13.4 Implementing custom security……Page 519
13.4.1 Design goals and trade-offs……Page 522
13.4.2 Intranet reporting with custom security……Page 523
13.4.3 Implementing the custom security extension……Page 525
13.4.4 Debugging the custom security extension……Page 532
13.5 Summary……Page 533
Performance and scalability……Page 534
14.1.1 Capacity-planning fundamentals……Page 535
14.1.2 The capacity-planning process……Page 540
14.2.1 Determining requirements……Page 545
14.2.2 Setting up the testing environment……Page 548
14.2.3 Performance testing……Page 552
14.2.4 Analyzing performance results……Page 553
14.2.5 Identifying resource constraints……Page 556
14.2.6 Eliminating resource constraints……Page 557
14.3 Summary……Page 560
A.1 Software requirements……Page 561
A.2.2 Starting the setup wizard……Page 562
A.2.3 Installing the components……Page 563
Understanding .NET code access security……Page 568
B.2 Understanding code access terminology……Page 569
B.2.2 Understanding code access security policies……Page 570
B.2.3 Overriding code access security policy……Page 571
B.3.1 Defining default code access permissions……Page 573
B.3.2 Understanding configuration files……Page 574
B.4.1 Defining custom permission sets and code groups……Page 575
B.4.2 Granting custom assemblies Full Trust rights……Page 576
B.4.3 Dealing with unmanaged resources……Page 577
resources……Page 579
A……Page 586
C……Page 587
D……Page 588
E……Page 589
H……Page 590
K……Page 591
M……Page 592
P……Page 593
R……Page 594
S……Page 597
T……Page 599
W……Page 600
Z……Page 601