FOREWORD xxxv INTRODUCTION xxxvii
PART I: GETTING STARTED
CHAPTER 1: INTRODUCING REPORTING SERVICES 3 Who Uses Reporting Services? 4
Business Information Workers 5
Business Managers 5
Software Developers 5
System Administrators 5
Dashboards, Reports, and Applications 6 Blurring the Line Between Applications and Reports 6 Launching Reports from an Application 7
User Interaction 7
Integrating Reports and Applications 8
Enterprise Reporting 9
Solution Types 10
Simple Report Design 10
IT-Designed Reports 11
User-Designed Reports 12
Server-Based Reports 15
Business Intelligence Reporting Solutions 16
Report Data Sources 18
Analytic Data Sources and Self-Service BI 18
Complexity and Report Performance 19
Customizing the Reporting Experience 20 Summary 20 CHAPTER 2: REPORTING SERVICES INSTALLATION
AND ARCHITECTURE 23
The Basic Installation 24
Installing Reporting Services 25
Installing the Reporting Services Samples and
SQL Server Sample Databases 41
ftoc.indd xv
ftoc.indd xv 4/27/2012 4:14:05 PM4/27/2012 4:14:05 PM
COPYRIGHTED MATERIAL
xvi
The Enterprise Deployment 41
SQL Server Editions 41
Named Instances 44
Topology 44 Modes 45
Installation Options 45
Command-Line Installation 46
The Reporting Life Cycle 46
Authoring 47 Management 47 Delivery 47
Reporting Services Tools 47
Report Designer 48
Power View 48
Report Builder 48
Third-Party Authoring Tools 49
Report Manager 49
SharePoint Libraries and Web Parts 49 Reporting Services Confi guration Manager 49 SQL Server Management Applications 49
Command-Line Utilities 50
HTML Viewer 50
Report Viewer Control 51
Reporting Services Web Service 51
Subscriptions 52 Reporting Services Windows Service 52
HTTP.SYS and the HTTP Listener 53
The Security Sublayer 54
Report Manager and the Web Service 55
Core Processing 55
Service Management 55
Confi guration Files 56
WMI and the RPC Interface 57
Reporting Services Processors and Extensions 57
The Report Processor 58
Report Session Caching 59
Report Execution Caching 59
Snapshots 60
Data Processing Extensions 60
Report Items 61
ftoc.indd xvi
ftoc.indd xvi 4/27/2012 4:14:06 PM4/27/2012 4:14:06 PM
xvii
Rendering Extensions 61
The HTML Rendering Extension 62
The CSV-Rendering Extension 62
The XML-Rendering Extension 62
The Image-Rendering Extension 63
The PDF-Rendering Extension 63
The Excel-Rendering Extension 63
The Word-Rendering Extension 64
The Scheduling and Delivery Processor 64
Delivery Extensions 64
Reporting Services Application Databases 64 ReportServer 65 ReportServerTempDB 66 Summary 66
CHAPTER 3: CONFIGURING SHAREPOINT INTEGRATION 69 The SharePoint Technologies 70
SharePoint Foundation 70
SharePoint Server 71
SharePoint Web Parts 73
Installation and Confi guration 74
Installing SharePoint Server 2010 74
Installing Service Pack (SP) 1 76
Installing and Confi guring PowerPivot for SharePoint 77 Installing and Confi guring Reporting Services for SharePoint 81
SharePoint Site Settings 89
Architecture 90 Summary 91
PART II: REPORT DESIGN
CHAPTER 4: BASIC REPORT DESIGN 95
What Good Are Wizards, Anyway? 95
Building Blocks 96
Following Along? 97
Let’s Get Started 97
Touring Report Builder 107
Offi ce Tabs and Ribbons 108
Home Tab 108
ftoc.indd xvii
ftoc.indd xvii 4/27/2012 4:14:06 PM4/27/2012 4:14:06 PM
xviii
Insert Tab 109
View Tab 110
Report Builder Menu 111
Viewing and Setting Properties 112
Data Sources 112
Server Reports 112
Manual Report Design 113
Building a Matrix 113
Wrapping It Up 118
Running and Saving the Report 120
Summary 120
CHAPTER 5: REPORT LAYOUT AND FORMATTING 123
Report Layout Types 124
Table Reports 124
Matrix Reports 125
List Reports 126
Chart Reports 126
Gauge Reports and Dashboards 127
Maps and Spatial Reports 127
Defi ning Table Groups 128
Group Expressions and Options 128
Formatting Table Values 130
Interactive Sort 134
Adding Page Breaks 135
Creating Drill-Down Reports and Dynamic Visibility 136
Formatting Report Data 138
Introduction to Dynamic Formatting 138 Designing Multicolumn Reports 139
Designing Gauge Reports 139
Browser Compatibility 140
Offl ine Viewing 141
Rendering Format Limits and Considerations 141 Summary 142
CHAPTER 6: DESIGNING DATA ACCESS 143 Business Intelligence Reporting 146 Reporting for Relational Data 148
Data and Query Basics 148
Data Sources 148
ftoc.indd xviii
ftoc.indd xviii 4/27/2012 4:14:06 PM4/27/2012 4:14:06 PM
xix
Creating a Data Source from the Project Add Item Template 149 Creating a Data Source in the Report Wizard 149 Creating a Data Source When Defi ning a Dataset 152 Data Sources and Query Languages 153
T-SQL Query Design 154
Data Warehouse Star Schema 155
Filtering Techniques 161
Filtering a Query 162
Parameter Concepts 163
Filtering Data with Query Parameters 165
Creating a Parameter List 167
Multivalue Parameters 168
Cascading Parameters 170
Report Parameters 175
Using Stored Procedures 176
Filtering Data with Report Parameters 177
Using Other Data Sources 180
Microsoft Access 180
Building a Query in a String Expression 183
Microsoft Excel 184
Oracle P/L SQL 185
Sybase Adaptive Server 186
Federating Data Sources 186
Best Practices 187
Summary 187
CHAPTER 7: ADVANCED REPORT DESIGN 189
Headers and Footers 190
Aggregate Functions and Totals 194
Adding Totals to a Table or Matrix Report 196 Creating Report Templates 198 Creating Composite Reports 199
Anatomy of a Textbox 199
Padding and Indenting 200
Embedded Formatting 201
Embedded HTML Formatting 202
Designing Master/Detail Reports 204
Groups and Dataset Scope 205
Using a List to Combine Report Items and
Data Regions 205
ftoc.indd xix
ftoc.indd xix 4/27/2012 4:14:06 PM4/27/2012 4:14:06 PM
xx
Designing Subreports 212
Federating Data with a Subreport 213
Execution and Resource Implications 216
Navigating Reports 219
Creating a Document Map 219
Links and Drill-Through Reports 221
Navigating to a Bookmark 223
Navigating to a URL 223
Reporting on Recursive Relationships 224 Summary 228
CHAPTER 8: CHART REPORTS 229
Chart Types 230
Column Charts 230
Stacked Charts 233
Area and Line Charts 233
Pie and Doughnut Charts 234
Bubble and Stock Charts 235
Chart Type Summary 237
The Anatomy of a Chart 239
Creating a Multiseries Chart 240
Using Multiple Chart Areas 242
Useful Properties and Settings 246
Control the Number of Items Displayed on an Axis 246 Manage Axis Text Placement and Rotation 246 Manage the Format of Axis Values 246 Change the Color and Width of a Series Line 246 Set a Tooltip for a Chart Value 246 Control the Width and Gap Between Columns or Bars 246 For a Chart with Multiple Chart Areas, Control the Exact Position
of Each Chart Area 247
Dynamically Increase a Chart’s Size 247 Summary 247
PART III: BUSINESS INTELLIGENCE REPORTING
CHAPTER 9: BI SEMANTIC MODELS 251
Introduction to Data Modeling 252
The BI Semantic Model 253
Conceptual Architecture 256
Data Model 257
ftoc.indd xx
ftoc.indd xx 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxi
Business Logic and Queries 260
Data Access 260
The Hybrid Nature of the BI Semantic Model 261 Summary 262
CHAPTER 10: REPORTING WITH ANALYSIS SERVICES 263 Why Use Analysis Services for Reporting? 264 Using Reporting Services with Analysis Services Data 266 Working with Multidimensional Expression Language 266
MDX: Simple or Complex? 267
Building Queries with the MDX Query Designer 267
Creating a Data Source 268
Building the Dataset Query 270
Using Parameterized Queries 273
Modifying the MDX Query 278
Adding Nonadditive Measures 285 When to Use the Aggregate Function 287 MDX Properties and Cube Formatting 289
Drill-Through Reports 290
Creating Cube Report Actions 291
Parameter Safety Precautions 292
Best Practices and Provisions 293 Summary 293
CHAPTER 11: OLAP REPORTING ADVANCED TECHNIQUES 295
Cube Dynamic Rows 296
Cube Dynamic Rows Anatomy 296
Parameters 296 Dataset 297
Matrix Content 301
Formatting the Row Label 301
Highlighting the Current Row 303
Dynamic Number Formatting 304
Self-Calling Drill-Through Action 304
Cube Dynamic Rows Summary 306
Cube Dynamic Rows Expanded 306
MDX Query Modifi cations 306
Design Surface Modifi cations 308
Tablix 308
Visualization Tweaks 308
Summary 309
ftoc.indd xxi
ftoc.indd xxi 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxii
Cube Restricting Rows 309
Designing the Report 309
pRowCount Parameter 309
Restricting the Number of Rows in the MDX Query 312 Adding pRowCount to Self-Calling Drill-Through Report Action 314 A Better Way to Interact with a Report Parameter 314 Summary 314
Cube Metadata 315
Designing the Report 315
MeasureGroups 318
Adding Other Cube Metadata 320
Final Thoughts 324
Cube Browser 324
Anatomy of the Reports 326
Cube Browser 326
Cube Browser Metadata 327
Cube Browser Member 329
Behind the Scenes 329
Cube Browser 329
Report Body 334
Restricting Rows and Columns 336
Swap Actions 336
Titles 338
Footer Information 342
Final Thoughts 345
Summary 346
PART IV: ENABLING USER REPORTING
CHAPTER 12: TABULAR MODELS 349
Introduction to PowerPivot 350
PowerPivot for Excel 352
Setup and Installation 354
Importing Data into PowerPivot 355
PowerPivot Window 360
The Home Tab 360
The Design Tab 361
The Advanced Tab 362
Analyzing and Enriching Data 363
Filtering and Sorting 363
Relationships 364
ftoc.indd xxii
ftoc.indd xxii 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxiii
Calculated Columns 367
Measures 368
Browsing the Model 371
Summary 372
CHAPTER 13: VISUAL ANALYTICS WITH POWER VIEW 373 Introduction to Power View 374
Power View Architecture 379
Architecture Overview 380
Reporting Services Add-in 381
Reporting Services Service Application 381
PowerPivot for SharePoint 382
Preparing a Model and Connection for a Tutorial 382 Deploying the FAA Flight Data Model 383 Creating a SharePoint Image Library for FAA Airline Images 384 Publishing the FAA Workbook Directly to the PowerPivot Gallery 386 Creating Data Source Connections for Power View 387 BI Semantic Model (BISM) Connection File 387 Report Data Source (RSDS) Connection 388 Visual Analytics with Power View 389
Getting Started with Power View 390
Creating a New Power View Report 390 Opening an Existing Power View Report 391 Introduction to the Design Experience 391
Creating a Table Visualization 392
Converting Visualizations 394
Sorting Inside a Chart 395
Expanding Visualizations 396
Filtering in Views 396
Multiple Views 400
Saving Reports 403
Permissions for Power View 405
Visualizations and Interactivity 405
Tile Visualizations 405
Highlighting in Visualizations 408
Matrix 409 Slicers 410 Filters 410
Card and Callout Views 411
Zooming in Charts 413
Scatter and Bubble 414
ftoc.indd xxiii
ftoc.indd xxiii 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxiv
Animated Timeline Charts 416
Refreshing Data in a Power View Report 418 Presenting and Exporting in Power View 418
Reading and Presentation Modes 419
Printing Views 421
PowerPoint Export and Interactivity 421
Tips and Tricks for Power View 424
Confi guring Data Source Connections 425 Connecting to PowerPivot Workbooks 427 Connecting to a BISM Connection File 428
Connecting to an RSDS 429
Authentication Scenarios 430
Comparison and Trade-off s 432
Kerberos Delegation with BISM or RSDS 432 BISM Connection and RS Service Account 433 RSDS and Stored Windows Credentials 433 RSDS with Impersonation and Eff ectiveUser 435 Analysis Services Tabular 437 Installing Analysis Services Tabular 437 Deploying a PowerPivot Workbook to Analysis Services Tabular 440 Summary 442 Resources 443
CHAPTER 14: REPORT BUILDER SOLUTION STRATEGIES 445 Report Builder and Semantic Model History 447 Planning a Self-Service Reporting Environment 448
You Need a Plan 448
Design Approaches and Usage Scenarios 448
Defi ne Ownership 449
Data Governance 450
Data Source Access and Security 451
User Education 451
Optimizing the Report Builder User Experience 452
Conducting User Training 452
Folder and Library Management 452
Report Branding 452
Data Source and Query Options 453
Using Shared Data Sources 453
Using a BI Semantic Model 454
Designing and Deploying Report Parts 454
ftoc.indd xxiv
ftoc.indd xxiv 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxv
Using Report Parts 457
Using Shared Datasets 457
User Report Migration Strategies 457
Report Migration Phases 457
Review 457 Consolidate 458 Design 458 Test 458 Maintain 459 Summary 459
PART V: SOLUTION PATTERNS
CHAPTER 15: MANAGING REPORT PROJECTS 463
Solutions and Projects 463
Project Structure 464
Multiple Reporting Environments 465 Multiple Logical Folders and Projects 465
Report-Naming Conventions 466
Shared Datasets and Data Sources 466
Key Success Factors 467
Solution Scope 467
Report Specifi cations 468
Report Template 469
Development Phases 469
Multiple Reporting Environments 469 Multiple Logical Folders and Projects 470
Version Control 471
Why Use Version Control? 471
Setting Up Version Control 471
Getting the Latest Version 472
Viewing a Report’s History 472
Restoring a Previous Version of a Report 472 Setting Check-out and Check-in Policies 472
Applying Labels 473
Synchronizing Content 473
Deploying an Individual Report 473
Deploying a Suite of Reports 473
Checking for Build Errors 473
Excluding a Report from a Deployment 473
ftoc.indd xxv
ftoc.indd xxv 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxvi
Managing Server Content 474
Checking the Deployment Location 474
Managing Content in Native Mode 475
Managing Data Sources 475
Managing Data Sets 476
Managing Reports 476
Managing Content in SharePoint 477
Getting Started with Azure Reporting 477
Deploying and Executing Reports 481
Summary 482
CHAPTER 16: REPORT SOLUTIONS, PATTERNS, AND RECIPES 483
Super Reports 484
Working with the Strengths and Limitations of the Architecture 484 Report Recipes: Building on Basic Skills 487 Dashboard Solution Data Sources and Datasets 488
KPI Scorecard 488
Deploying a Report Part 492
Interactive Sparkline and Chart 494
Map with Navigation and Zoom 502
Geographic Shape Colors 506
Adding Spatial Point Markers 508
Zoom, Pan, and Tilt 509
Using Report Parts to Assemble a Dashboard 511
Dynamic Colors and Themes 514
Table Report with Dynamic Columns 517
Designing the Report 517
Summary 521
PART VI: ADMINISTERING REPORTING SERVICES
CHAPTER 17: CONTENT MANAGEMENT 525
Using Report Manager 526
Content Management Activities 529 Folders 529
Shared Data Sources 531
Report Models 534
Reports 535
Report Resources 543
Shared Schedules 544
ftoc.indd xxvi
ftoc.indd xxvi 4/27/2012 4:14:07 PM4/27/2012 4:14:07 PM
xxvii
Item-Level Security 545
Content Management Automation 553
The RS Utility 553
Reporting Services Scripts 555
Summary 557
CHAPTER 18: INTEGRATING REPORTS WITH SHAREPOINT 559
Native Mode Web Parts 560
Native Mode Web Parts Installation 560
Report Viewer 561
Report Explorer 562
Publishing Reports to SharePoint 564 SharePoint Foundation and SharePoint Server Standard Edition 564 SharePoint Server Enterprise Edition 570
Report Management 572
Integrated Mode Web Parts 574 Native Mode Versus Integrated Mode 577
Report Models 578
Summary 579
CHAPTER 19: NATIVE MODE SERVER ADMINISTRATION 581 Security 582
Account Management 582
The Service Account 583
The Application Database Account 585 The Unattended Execution Account 586
System-Level Roles 587
Surface Area Management 589
Backup and Recovery 590
Application Databases 590
Encryption Keys 592
Confi guration Files 594
Other Items 595
Monitoring 595
Setup Logs 596
Windows Application Event Logs 596
Trace Logs 596
Execution Logs 600
Performance Counters 602
Server Management Reports 607
ftoc.indd xxvii
ftoc.indd xxvii 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxviii
Confi guration 607
Memory Management 607
URL Reservations 608
E-mail Delivery 610
Rendering Extensions 612
My Reports 614
Summary 616
PART VII: REPORTING SERVICES CUSTOM PROGRAMMING
CHAPTER 20: INTEGRATING REPORTS INTO CUSTOM APPLICATIONS 619
URL Access 620
URL Syntax 620
Accessing Reporting Services Objects 621 Folders 621
Data Sources 623
Resources 625 Reports 625 Reporting Services URL Parameters 627
Parameter Prefi xes 627
Parameters 628 Passing Report Information Through the URL 632
Report Parameters 633
Rendering Snapshot History 634
URL Rendering Summary 635
Programmatic Rendering 635
Common Scenarios 636
Custom Security 636
Server-Side Parameters 636
Rendering Through Windows 637
Building the Application Interface 637
Setting Up the Web Services 637
Retrieving Report Information 643
Retrieving Report Parameters 648
Rendering a Report to a File on the Filesystem 653 Rendering a Report to the Filesystem Summary 660
Rendering to the Web 661
Using Integrated Windows Authentication 661
ftoc.indd xxviii
ftoc.indd xxviii 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxix
Modifying the web.confi g File 662
Confi guring ASP.NET 2.0 in IIS 6 and Older Versions 662 Setting Up the Report Execution Web Service 662 Rendering to the Response Object 662 Using the ReportViewer Control 669 Embedding a Server-Side Report in a Windows Application 672 Summary 678
CHAPTER 21: USING EMBEDDED AND REFERENCED CODE 681 Using the Expression Builder 683
Calculated Fields 684
Conditional Expressions 687
IIF() Is Your Friend 687
Using Custom Code 690
Using Custom Code in a Report 691
Using a Custom Assembly 692
Custom Assembly Security 694
Errors, Warnings, and Debugging Code 695 Summary 695
CHAPTER 22: EXTENDING REPORTING SERVICES 697 Extension Through Interfaces 699
What Is an Interface? 699
Interface Language Diff erences 700
A Detailed Look at Data Processing Extensions 702 Creating a Custom Data Processing Extension 705
The Scenario 705
Creating and Setting Up the Project 706 Creating the DataSetConnection Object 708
Variable Declarations 710
Constructors 710 Implementing IDbConnectionExtension 711
Impersonate Property 711
IntegratedSecurity Property 712
UserName and Password Properties 712
Implementing IDbConnection 713
Begin Transaction Method 715
CreateCommand Method 716
ftoc.indd xxix
ftoc.indd xxix 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxx
Open Method 716
Close Method 717
ConnectionString Property 717
ConnectionTimeout Property 719
Creating the DataSetParameter Class 720 Declarations 720
Implementing IDataParameter 720
ParameterName Property 721
Value Property 722
Creating the DataSetParameterCollection Class 723 Namespaces 723 Implementing IDataParameterCollection 723 Creating the DataSetCommand Class 725
Variable Declarations 726
Constructors 727
Implementing IDbCommand 728
Cancel Method 729
ExecuteReader Method 729
CommandText Property 731
CommandTimeout Property 739
CommandType Property 739
CreateParameter Method 740
Parameters Property 740
Creating the DataSetDataReader Object 741 Declarations 741
Implementing IDataReader 742
GetFieldType Method 743
GetName Method 743
GetOrdinal Method 744
GetValue Method 744
Read Method 745
FieldCount Property 746
Installing the DataSetDataProcessing Extension 746
Server Installation 746
Server Security Confi guration 747
WorkStation Installation 748
WorkStation Security Confi guration 748
Testing DataSetDataExtension 749
Summary 753
ftoc.indd xxx
ftoc.indd xxx 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxxi
PART VIII: APPENDIXES
APPENDIX A: T-SQL COMMAND SYNTAX REFERENCE 757 T-SQL Commands, Clauses, and Predicates 758 WITH 758 SELECT 758
SELECT TOP 759
SELECT INTO 759
FROM 760 WHERE 760
GROUP BY 761
WITH ROLLUP 761
BY ROLLUP 761
WITH CUBE 761
BY CUBE 762
HAVING 762 UNION 762
EXCEPT and INTERSECT 762
ORDER BY 762
COMPUTE and COMPUTE BY Clauses 763
FOR Clause 763
OPTION Clause 763
CASE 763 INSERT 764 UPDATE 764 DELETE 764
DECLARE @local_variable 765
SET 765 LIKE 765
ALTER TABLE 766
PIVOT Operator 766
UNPIVOT Operator 766
CREATE DATABASE 767
CREATE DEFAULT 767
CREATE PROCEDURE 767
CREATE RULE 768
CREATE TABLE 768
CREATE TRIGGER 768
ftoc.indd xxxi
ftoc.indd xxxi 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxxii
CREATE VIEW 768
CREATE SCHEMA 769
CREATE PARTITION FUNCTION 769
CREATE PARTITION SCHEME 769
Script Comment Conventions 769
Reserved Words 770
ODBC Reserved Words 772
Future Reserved Words 775
APPENDIX B: T-SQL SYSTEM VARIABLES AND FUNCTIONS 779
System Global Variables 779
Confi guration 780
Cursor 782 System 782
System Statistics 782
System Functions 783
Aggregation 784 Checksum 785 Conversion 786 Cryptographic 786 Cursor 787
Date and Time 787
Error Handling 789
Image/Text 790 Mathematical 790 Metadata 792 Ranking 794 Rowset 794 Security 795
String Manipulation 796
System 798
System Statistics 801
APPENDIX C: MDX REFERENCE 803
Object Identifi ers 803
Reserved Keywords 803
Member References 806
Sets 807 Tuples 808
ftoc.indd xxxii
ftoc.indd xxxii 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
xxxiii
The SELECT Statement 808
Axis Defi nitions 809
The WHERE Clause 809
The FROM Clause 810
The WITH Clause 810
Parameters 811 MDX Functions and Keywords 812 Keywords 812
KPI Functions 813
Metadata Functions 813
Navigation Functions 815
Other Functions 817
Set Functions 818
Statistical Functions 821
String Functions 824
Time Functions 825
UI Functions 826
Value Functions 828
INDEX 829
ftoc.indd xxxiii
ftoc.indd xxxiii 4/27/2012 4:14:08 PM4/27/2012 4:14:08 PM
