Learn to design more effective and sophisticated business reports While most users of SQL Server Reporting Services are now comfortable designing and building simple reports, business today demands increasingly complex reporting. In this book, top Reporting Services design experts have contributed step-by-step recipes for creating various types of reports.
Written by well-known SQL Server Reporting Services experts, this book gives you the tools to meet your clients' needs
Table of Contents:
FOREWORD xxiii PREFACE xxv
INTRODUCTION xxvii
CHAPTER 1: BUSINESS REPORTING PARADIGMS 1
Process and Operational Support 2
Report Types 2
Sales Orders, Invoices, Manifests, and Inventory Forms 2
Template Forms 3
Tabular and List Reports 4
Catalogs 6
Labels 6
Activity Summaries 7
Status Reports 8
Analytical Reporting 8
Report Types 9
Dashboards and Scorecards 9
Pivot Table and Matrix Reports 9
Charts 10
Maps 14
Interactive Reports 15
Application Integration 17
Report Integration into Applications 18
Desktop Applications 18
Web Applications 19
Portal Content 19
ReportViewer Control 20
Installing the Reporting Services Samples and SQL Server
Sample Databases 21
Server Reports 22
HTML Viewer 23
Report Viewer Control 23
Reporting Services Processors and Extensions 23
Report Caching 24
The HTML Rendering Extension 25
The CSV-Rendering Extension 25
The XML-Rendering Extension 25
The Image-Rendering Extension 26
The PDF-Rendering Extension 26
The Excel-Rendering Extension 26
The Word-Rendering Extension 26
Summary 26
CHAPTER 2: BASIC REPORT DESIGN CONCEPTS 29
Using Report Builder 30
Formatting and Sample Values 31
Data Sources 31
Datasets and Queries 32
Designing Queries 33
Dataset Best Practices 36
Filtering Data 37
Using Stored Procedures 38
Reports and Report Objects 39
Report Body 39
Headers and Footers 40
Aggregate Functions and Totals 41
Adding Totals to a Table or Matrix Report 42
The Tablix 43
Static and Dynamic Columns and Rows 43
Summary 44
CHAPTER 3: REPORT DESIGN ESSENTIALS 45
Tabular and Matrix Reports 45
Defining Table Groups 45
Group Expressions and Options 46
Formatting Table Values 48
Matrix Reports 51
Sorting Options 51
Sorting in the Query 51
Sorting in a Group 52
Interactive Sort 53
Adding Headers and Footers 53
The Low Down on Drill-Down 54
Report Navigation Essentials 54
Reports with Multi-Level Groups and Drill-Down Actions 54
Standard Terminology 54
Drill-Down 55
Creating a Drill-Down Report 59
Drill-Through Reports 60
Navigating to a URL 62
Report Navigation Summary 62
Charting Basics 63
Series and Category Axes 63
Polar and Radar Charts 65
Shape Charts 65
Bar Charts 65
Gauges 67
Scales 67
Pointers and Markers 67
Ranges 67
Radial Gauges 68
Linear Gauges 68
Maps 69
Map Gallery 69
ESRI Shape Files 69
SQL Server Spatial Data 69
Using Parameters 69
Creating a Parameter List 70
Modifying and Formatting MDX Queries 72
Multi-Value Parameters 73
Cascading Parameters 74
Report Parameters 75
Expressions and Custom Code 75
Calculated Fields 76
Conditional Expressions 76
Using Custom Code 78
Using Custom Code in a Report 78
Using a Custom Assembly 79
Formatting Report Data 80
Introduction to Dynamic Formatting 81
Designing Multicolumn Reports 81
Summary 82
PART I: COLUMNAR AND GROUPED REPORTS
Green Bar Reports 85
Designing the Report 85
Alternate Row Colors in an SSRS 2000 or 2005 Matrix 90
Final Thoughts 92
Credits and Related References 92
Alternate Background Shading for Table Groups 93
Designing the Report 93
Designing the Report for Reporting Services 2005 98
Final Thoughts 98
Credits and Related References 99
Nested Group Green Bar Effect 100
Designing the Report 100
Final Thoughts 104
Credits and Related References 104
Creating Dynamic Groups 105
Designing the Report 105
Final Thoughts 114
Credits and Related References 114
Hiding and Showing Columns in a Table 115
Designing the Report 115
Showing and Hiding Group Headers 120
Final Thoughts 120
Horizontal Table 121
Designing the Report 121
Designing the Report for Reporting Services 2005 124
Final Thoughts 124
Credits and Related References 124
Resetting the Page Number Based on Groups 125
Designing the Report 125
Final Thoughts 127
PART II: BI DASHBOARDS AND ELEMENTS
Creating Sparklines 131
Designing the Report 131
Sales Trends 131
Final Thoughts 137
Credits and Related References 138
Cube Dynamic Rows 139
Designing the Report 140
Final Thoughts 147
Credits and Related References 147
Cube Metadata 148
Designing the Report 148
Adding MeasureGroups (for Cube/Perspective) 151
Adding Other Cube Metadata 153
Final Thoughts 161
Credits and References 162
Cube Browser 163
Anatomy of the Reports 165
Cube Browser 165
Cube Browser Metadata 166
Cube Browser Member 167
Behind the Scenes 167
Cube Browser 167
Report Body 173
Restricting Rows and Columns 174
Swap Actions 175
Titles 176
Footer Information 179
Final Thoughts 182
Credits and Related References 184
Australian Sparklines 185
Designing the Report 186
Preparing the Data and Adding Extra Controls 187
Building a Full-Sized Australian Sparkline 197
Adding the Australian Sparkline to a Table 201
Final Thoughts 203
Credits and Related References 203
Angry Koala Cube Browser 204
Anatomy of the Reports 205
r100 - Angry Koala Cube Browser 206
r101 - Angry Koala Graph 207
r102 - Angry Koala Driver 208
r103 - Angry Koala Member 210
Behind the Scenes 210
Angry Koala Cube Browser 210
Report Body 219
Restricting the Number of Rows and Columns 220
Swap Actions 220
Titles 220
Report Footer Info 220
Final Thoughts 221
Credits and Related References 221
Bullet Charts 222
Designing the Report 222
Final Thoughts 226
Credits and Related References 227
Synchronizing Groups, Charts, and Sparklines 228
Designing the Report 228
Final Thoughts 232
Credits and Related References 232
PART III: CHART AND GAUGE REPORTS
Chart Custom Color Palette 235
Designing the Report 236
Custom Legends 237
Final Thoughts 238
Credits and Related References 238
Chart Keywords 239
Designing the Report 239
Final Thoughts 241
Credits and Related References 242
Column Chart with Goal Threshold Line 244
Designing the Report 244
Adding Dynamic Color 249
Final Thoughts 249
Creating a Personal Report Card 250
Designing the Report 250
Final Thoughts 259
Customizing Gauges with Images 260
Designing the Report 260
Final Thoughts 263
Credits and Related References 263
Exception Highlighting with Gauges/Bullet Graphs 264
Designing the Report 264
Final Thoughts 267
Credits and Related References 267
Grouped Pie Chart Slices 268
Designing the Report 268
Final Thoughts 271
Growing Bar and Column Charts 272
Designing the Report 272
Final Thoughts 275
Credits and Related References 275
Histogram Chart 276
Designing the Report 276
Final Thoughts 278
Credits and Related References 278
Linear Regression Line 279
Designing the Report 279
Final Thoughts 285
Creating a Multi-Series Multi-Y Axis Chart 286
Designing the Report 286
Credits and Related References 292
Pareto Chart 293
Designing the Report 293
Final Thoughts 296
Credits and Related References 296
PART IV: INTERACTIVE REPORTING
Conditional Linking 299
Designing the Report 299
Final Thoughts 304
Credits and Related References 304
Drill-Through for a Multi-Level Matrix Report 305
Designing the Drill-Through Target Report 305
Designing the Drill-Through Source Report in 2005 309
Designing the Drill-Through Source Report in 2008 315
Final Thoughts 317
Credits and Related References 317
Drill-Through Report Link Breadcrumbs 318
Designing the Report 319
Final Thoughts 325
Dynamic Pivoting as a Matrix Replacement 326
Designing the Report 326
Final Thoughts 330
Using a Document Map Table for Navigation 331
Designing the Report 331
Final Thoughts 337
Credits and Related References 337
PART V: INTEGRATED REPORTING APPLICATIONS
Creating a Report Server Usage Report 341
Designing the Report 343
Final Thoughts 346
Rotating Report Dashboard 347
Designing the Report 347
Final Thoughts 351
Credits and Related References 351
Updating Data From a Report 352
Designing the Report 352
Final Thoughts 358
Offline Reporting Using the Report Viewer Control 359
Designing the Report 359
Computer Requirements and Prerequisites 362
Wiring Up the Report 363
Programming the Code-Behind 365
Final Thoughts 372
PART VI: ENHANCED REPORT CONTENT
Creating a Calendar Report 375
Designing the Report 375
Final Thoughts 382
Credits and Related References 382
Creating Mailing Labels 383
Designing the Report 383
Final Thoughts 390
Credits and Related References 390
Barcodes 391
Designing the Report 391
Fonts 392
Custom Report Items (Barcode Components) 393
Final Thoughts 399
Credits and Related References 399
Currency Translation 400
Designing the Report 400
Final Thoughts 406
Custom Aggregation 407
Designing the Report 407
Designing the Median Report in SSRS 2005 407
Implementing the Report in SSRS 2008 409
Final Thoughts 414
Credits and Related References 414
Dynamic (Conditional) Page Breaks 415
Designing the Report 415
Designing the Report for Previous Versions of Reporting Services without the PageBreak.Disabled Property 418
Final Thoughts 419
Excel Worksheet Naming And Page Naming 420
Designing the Report 420
Final Thoughts 424
External Image Sources 425
Designing the Report 425
Creating the ASP.NET External Image Source 430
Final Thoughts 438
Language Localization 439
Multi-cultural Considerations 439
Designing the Report 440
Creating the External Resource Lookup with .NET 441
Final Thoughts 446
Credits and Related References 446
Page Running Total 447
Designing the Report 447
Final Thoughts 453
Renderer-Dependent Layout and Formatting 454
Designing the Report 454
Final Thoughts 459
Creating a Checkbox List to Show Existing Records 460
Designing the Report 460
Final Thoughts 463
Using a Checkbox List to Select and Deselect Records 464
Designing the Report 464
Using the Checkbox Report for Parameter Selection 472
Final Thoughts 473
Using the Map Wizard 474
Designing the Report 474
Final Thoughts 478
Credits and Related References 478
PART VII: FILTERING AND PARAMETERIZATION
Multiple Criterion Report Filtering 481
Designing the Report 481
Filtering in the Query 484
Using Code to Build the Query String 485
Filtering in the Dataset 487
Samples 488
Final Thoughts 488
Credits and Related References 489
Using Multi-Value Parameters with a Stored Procedure 490
Designing the Report 490
Final Thoughts 495
Using Multi-Value Parameters with a Subscription Report 496
Designing the Report 496
Final Thoughts 505
Parameterized Top Values Report 506
Designing the Report 506
Top Value Reports for Cubes 510
Final Thoughts 512
Cube Restricting Rows 513
Designing the Report 513
A Better Way to Interact With a Report Parameter 518
Final Thoughts 521
Creating Custom Sorting Reports 522
Parameterizing Custom Sorted Queries 523
Designing the Report 523
Parameterizing the Order By Clause 530
Custom Sorting in Tablix Groups 533
Using the Interactive Sort Feature 535
Creating a Custom Interactive Sort 536
Final Thoughts 542
Credits and Related References 543
Filtering User-Specific Report Data 544
Designing the Report 544
Final Thoughts 550
PART VIII: CUSTOM AND DYNAMIC DATA SOURCES
Using aWeb Service as a Data Source 553
Designing the Report 553
Final Thoughts 560
Credits and Related References 560
Reporting on SharePoint List Data 561
Preparing the Sample Data 562
Designing the Report 564
Designing the Report in 2008 R2 570
Final Thoughts 573
Credits and Related References 573
Dynamics AX Report Wizard 574
Designing the Report 574
Final Thoughts 581
PART IX: GAMES
Hangman Game 585
Reviewing the Report 585
Final Thoughts 589
Credits and Related References 590
Sea Battle Game 591
Reviewing the Report 592
How It Works 593
Final Thoughts 599
Credits and Related References 599
INDEX 601
