Hitchhiker's Guide to SQL Server 2000 Reporting Services

by Blackburn, Peter; Vaughn, William R.

• ISBN13: 9780321268280
• ISBN10: 0321268288
• Edition: 1st
• Format: Paperback
• Copyright: 2004-10-01
• Publisher: Addison-Wesley Professional

Summary

"Bill Vaughn and Peter Blackburn have created a masterful work of Reporting Services documentation, which includes tips, tricks, and product insights that you just wonrs"t find anywhere else."Bryan Keller, Programmer-Writer, SQL Server Reporting Services, Microsoft Corporation"An insightful, informative, and sometimes irreverent look into the world of Reporting Services,Hitchhikerrs"s Guide to SQL Server 2000 Reporting Servicesprovides plenty of walk-throughs, examples, tips, and tricks to help you get the most out of the product. Peter and Bill take you step by step through the various features of Reporting Services, pointing out pitfalls and best practices along the way. An excellent addition to any Reporting Services library."Michelle Larez, Technical Writer, SQL Server Reporting Services, Microsoft Corporation"Although it is calledHitchhikerrs"s Guide to SQL Server 2000 Reporting Services, I like to think of it in more of a movie context asBill and Peterrs"s Excellent Adventure. Get ready for a totally excellent quest to get the most out of your SQL Server Reporting Services deployment."Brian Welcker, Group Program Manager, SQL Server Reporting Services, Microsoft Corporation"Microsoft Reporting Services for SQL Server 2000 is easily the best product for solving customersrs" reporting requirements since Access 1.0 in 1992. Itrs"s simple to use, powerful, and nothing comes close when it comes to producing reports for the web. Bill Vaughn and Peter Blackburn have put together a comprehensive resource that fills a big void in the developer market on this topic."Adam Cogan, Chief Architect, Microsoft Regional Director, Superior Software for Windows (SSW)"Chapter 9 provides a great explanation of code access security issues and procedures! Easy to readcontains a lot of useful info and no water."Alexandre Mineev, Software Design Engineer, SQL Server Reporting Services, Microsoft Corporation"I got to know Peter and Bill in the early beta stages of SQL Server Reporting Services, where they were among the most prolific participants. This book reflects their passion for this product, their drive to go beneath the surface and understand its inner workings and its architecture."Tudor Trufinescu, Development Lead, SQL Server Reporting Services, Microsoft Corporation"The ultimate resource for both DBAs and developers on SQL Server Reporting Services; this book and DVD will guide you from installation, security, report design, and deployment to developing extensions."Gert E.R. Drapers, Software Architect, SQL Server Development, Microsoft Corporation SQL Server 2000 Reporting Services streamlines the process of extricating meaningful reports from your data sources.Hitchhiker's Guide to SQL Server 2000 Reporting Servicespicks up where the Microsoft documentation leaves off, providing report developers, database administrators, application developers, their managers, and report users with invaluable inside information on the workings of Reporting Services. Written on the final product release, with input from the Microsoft Reporting Services Development Team, this book offers an authoritative yet independent look at the technology. The authors--both Microsoft MVPs who've worked with the Development Team since the beginning--reveal hidden problems and workarounds as they steer you through each step in report installation, management, security, creation, and programming. You can start setting up simple single-server installations or web farms right away while gaining an in-depth understanding of every Reporting Services feature. The special companion DVD includes narrated Camtasia

Author Biography

Peter Blackburn is the principal of Boost Data Limited, designing, building, and implementing database systems based on SQL Server. He is a Microsoft MVP who has contributed countless hours to the development of Reporting Services. Peter supports developers worldwide through newsgroups, beta programs, and training sessions on SQL Server and Reporting Services. He is the coauthor of ADO.NET Examples and Best Practices for C# Programmers (APress), and writes and speaks regularly on Reporting Services.

William R. Vaughn is the principal of Beta V Corporation and a Microsoft MVP who worked at Microsoft for fourteen years. His previous books include six editions of the popular Hitchhiker's Guides (Microsoft Press) and two editions of the bestseller ADO.NET Examples and Best Practices for C# Programmers (APress). Bill is a top-rated speaker at conferences worldwide, including Developer Connections and TechEd, and is the author of numerous articles for SQL Server Magazine, MSDN Online, and others.

Table of Contents

Foreword	xvii
Preface	xxi
Acknowledgments	xxxi
About the Authors	xxxix
Chapter 1 An Overview	1	(38)
What Is Microsoft SQL Server 2000 Reporting Services?	2	(1)
What Is the Source of the Report Data?	2	(1)
Reporting Services: The Main Components	3	(3)
Writing Reports: The Report Designer	6	(18)
Define the Data Source	8	(1)
Create a DataSet	8	(3)
Visually Lay Out the Report Controls	11	(5)
Advanced Features: Programmability in the Report Designer	16	(2)
Document Maps	18	(1)
Previewing in the Designer	19	(1)
Previewing with the RSReportHost Utility	20	(2)
RDL: Report Definition Language	22	(1)
RDS: Report Data Source Files	23	(1)
The Report Server	24	(8)
Rendering Extensions	26	(1)
Delivery Extensions	27	(2)
The Report Processor	29	(1)
Caching intermediate Format	30	(2)
The Report Manager	32	(4)
Managing Security Permissions	33	(1)
Managing Folders	33	(1)
Managing Shared Data Sources	34	(1)
Managing Linked Reports	34	(1)
Managing Shared Schedules	34	(1)
Managing the Cache, Snapshots, History, and Report Execution Timeout	34	(1)
Subscriptions	35	(1)
Help	35	(1)
Extending the Report Manager	35	(1)
Versions of Reporting Services	36	(1)
Summary	37	(2)
Chapter 2 Installing and Configuring Reporting Services	39	(48)
Installation Pathways and Preparation	40	(11)
Operating System Choices	41	(2)
All on One Machine (Typical Development Scenario)	43	(2)
Installing Only the Report Designer Add-In	45	(1)
Installing Only the Sample AdventureWorks2000 Database	46	(1)
Installing Reporting Services on a Server with IIS	47	(1)
Which Account Is Running the Install Wizard?	47	(2)
Installing Reporting Services on Web Farms	49	(1)
Licensing Your Reporting Services Installation	50	(1)
Command-Line Options for the Installation Wizard	51	(1)
Preparing Your System to Run the Setup Wizard	51	(1)
Step by Step: Installing Reporting Services on a Server with IIS	51	(13)
Stepping Through the Setup Wizard	52	(1)
Reporting Services Windows and Web Service Account Installation Options	53	(3)
Auto-Starting the Reporting Services Service	56	(1)
Choosing the Reporting Services Virtual Directories	56	(2)
Selecting the Report Server Database and Credentials	58	(2)
SMTP Mail Configuration	60	(3)
Report Server Samples Setup	63	(1)
Licensing Mode	63	(1)
Ready to Install	63	(1)
Installing Reporting Services in a Non-Default Website	64	(3)
Installation Log Files	65	(1)
Runtime Trace Log Files	66	(1)
After Installation-Tuning and Reconfiguring	67	(6)
The Reporting Services Configuration Files	67	(3)
The Reporting Services Command-Line Utilities	70	(1)
Activation...rsactivate.exe	70	(1)
Key Management...rskeymgmt.exe	71	(1)
What Is rsconfig.exe?	72	(1)
Report Server Windows Service Polling	72	(1)
Testing the Installation	73	(5)
Installing Reporting Services on a Web Farm	78	(4)
Web Farm Installations	78	(4)
Removing Reporting Services	82	(2)
Reporting Services Documentation Refresh	84	(1)
Reporting Services Service Pack 1	85	(1)
Summary	85	(2)
Chapter 3 Creating Your First Report with the Report Designer Wizards	87	(44)
Creating a Table Report with the Report Project Wizard	88	(24)
Creating a Matrix Report with the Report Project Wizard	112	(17)
Summary	129	(2)
Chapter 4 Using the Report Manager	131	(88)
Understanding the Role-Based Security Model	133	(16)
Launching the Report Manager as a User and as an Administrator	134	(1)
Launching the Web Browser Under Different Credentials	134	(14)
Viewing Reports	148	(1)
Search for (Managed Reports)	149	(4)
Drilling into the Search URL	150	(1)
Adding Search to Your Own Website	151	(2)
Rendering Reports	153	(25)
Exporting Managed Repots to Other Formats	154	(2)
Managing Folders	156	(1)
View and Manage Resources	157	(4)
Enabling tie Manage Reports Task	161	(6)
Caching Managed Reports	167	(3)
Managing Managed Report Snapshots	170	(3)
Displaying the Last Run Time	173	(1)
Limiting Managed Report Execution Time	174	(4)
Creating and Managing Managed Report Subscriptions	178	(15)
Individual Subscriptions	179	(2)
The Manage All Subscriptions Task Rights	181	(1)
Data-Driven Subscriptions	182	(10)
Delivery Extension Configuration Issues	192	(1)
Managed Report Item-Level Security Settings	193	(9)
Creating Linked Managed Reports	197	(5)
Managing Data Sources	202	(5)
Shared Data Sources-A Security Concern	203	(1)
Recovering Deleted Shared Data Sources	204	(2)
Using the Report Manager to Configure Data Sources	206	(1)
Managing Site Settings	207	(9)
Site Name	211	(1)
Managing the My Reports Folder	211	(2)
Managing the History Snapshots Limit	213	(1)
Managing Report Execution Timeouts	213	(1)
Enable Execution Logging	214	(1)
Shared Schedules	214	(1)
Managing Roles and Report Server Security	215	(1)
Summary	216	(3)
Chapter 5 Report Security	219	(18)
Motivation	220	(14)
Trojan Reports	221	(2)
Data Sources-Custom or Shared, Stored Credentials, or Integrated Security (SSPI)	223	(11)
Summary	234	(3)
Chapter 6 Building Data Sources and DataSets	237	(80)
Data Sources and DataSets	237	(5)
What Is a Data Source?	238	(1)
What Is a DataSet?	239	(3)
Preparing the Development Server and Workstation	242	(5)
Creating a Deployment Folder	242	(2)
Creating a Suite of Data Sources	244	(1)
Creating Mirrored Data Sources	245	(2)
Building New Reports from Scratch	247	(32)
Creating a New Report Project	247	(4)
Creating Shared Data Sources	251	(5)
Designing Report DataSets	256	(23)
Specifying and Capturing Parameters	279	(18)
Query Parameters	280	(13)
Report Expressions	293	(1)
Managing Cascading Parameters	294	(3)
Advanced Techniques	297	(17)
Managing RETURN or OUTPUT Parameters	297	(1)
Executing "Dynamic" SQL Queries	298	(8)
Sharing Code Between Reports by Creating Class Libraries	306	(8)
Summary	314	(3)
Chapter 7 Report Layout and Design	317	(70)
Previewing and Deploying Reports	317	(4)
Data Caching for the Designer's Preview	320	(1)
Laying Out Your Report	321	(31)
Data Binding and the Value Property	322	(1)
Working with SQL Queries	322	(30)
Working with Images	352	(13)
Setting the Background Image Property	353	(2)
Adding Images to a Report	355	(1)
Adding Images to Reports Using the image Control	356	(1)
External Images, AKA Web Images	357	(8)
Drilling Down into Report Data	365	(4)
Implementing a Document Map	367	(2)
Using the Table Control	369	(8)
Customizing and Annotating the Table Control	372	(5)
Advanced Tips and Techniques	377	(4)
Doing the "Impossible" with Page Headers and Footers	378	(3)
Creating Subreports	381	(5)
Hiding and Launching Subreports	383	(3)
Summary	386	(1)
Chapter 8 The Chart and Matrix Controls	387	(56)
A Brief History of the Chart Control	388	(1)
Fetching Chart Data	389	(2)
Using "Too Much" Data	390	(1)
Something About Nothing	391	(1)
Understanding the Chart Control's User Interface	391	(8)
What Should Be Placed in the Landing Zones'?	394	(4)
What Are the X- and Y-Axes?	398	(1)
Choosing the Chart Type	399	(2)
Creating a Chart	401	(4)
Programming the Chart Using the Data Tab	403	(2)
Working with Pie and Doughnut Charts	405	(3)
Annotating Charts	408	(5)
Adding Labels to Chart Elements	408	(5)
Using the Stock Charts	413	(3)
A Touch of Color	416	(3)
Chart and Plot Area Styles	416	(1)
Shades of Gray or Hashing'?	417	(1)
SemiTransparent Shading	418	(1)
Using the Bubble and Scatter Charts	419	(4)
The Scatter Chart	423	(1)
The Matrix Control	423	(1)
Getting Started with the Matrix Control	424	(16)
Joining Other Tables	424	(1)
Breaking Out the Date	425	(1)
Creating a Report with a Matrix Control	426	(5)
Sorting and Naming the Groups	431	(2)
Putting GreenBars in Matrixes Using RunningValue and CountDistinct	433	(3)
Summing Columns and Rows	436	(1)
Understanding and Tuning Matrix Performance	437	(1)
Exporting to Office Web Components	438	(2)
Monitoring the Report Processor	440	(1)
Summary	441	(2)
Chapter 9 Customizing Reports-Report Templates and Styles (Or, How We Came to Love Code Access Security)	443	(52)
Building Report Wizard Style Templates	444	(7)
Using Gradient Fills for Textbox Controls	449	(1)
Customizing Headers and Footers	450	(1)
Customizing Report and Data Source Templates	451	(9)
Customizing Report Templates	451	(9)
Creating Custom Data Source Templates	460	(1)
Using Code Access Security	461	(32)
Creating Custom Report Style Sheets	461	(27)
Creating the Final StyleSheet	488	(5)
Summary	493	(2)
Chapter 10 Accessing the Report Server via URL	495	(26)
Simple URL Addressing the Report Server	496	(23)
Exposing the Reporting Services URL Commands	499	(1)
URL Syntax and Parameter Prefixes	500	(1)
Handling rs: Parameters-Sending Instructions to the Report Server	501	(3)
Setting Parameters Passed to the Rendering Extension	504	(3)
Managing Report Server Sessions	507	(4)
Passing Data Source Passwords as URL Parameters	511	(2)
Passing Report Parameters in a URL	513	(1)
HTML Forms-HTTP Postback	513	(4)
Service Pack 1	517	(2)
Final Thoughts	519	(1)
Summary	519	(2)
Chapter 11 Customizing the Report Manager	521	(28)
Simple Customizations-Tuning the CSS File and Graphics Objects	522	(25)
The Images Folder	523	(1)
The Report Manager Cascading Style Sheet	523	(3)
Adding Your Company Logo in the Report Manager	526	(1)
Adding Behavior	527	(3)
Customizing the Parameters Area	530	(4)
Multiple-Value Select Report Example	534	(12)
Using ASP.NET Web Forms for Parameter Collection	546	(1)
Summary	547	(2)
Chapter 93/4 Managing Reports Using SOAP	549	(56)
Prior to Adding a Web Reference-McGonagall's Strict Admonition	551	(37)
Adding a Web Reference	552	(6)
Listing the Secure Methods	558	(4)
Exception Handling	562	(3)
Listing Report Items in Folders	565	(2)
Using the Find Items Method	567	(2)
Creating a Schedule	569	(3)
Listing Schedules	572	(2)
Repairing Deleted Data Sources the Hard Way-With SOAP Calls	574	(4)
Creating an Item-Level Role Definition	578	(3)
Creating a Report Folder and Assigning a Role Within a Batch	581	(2)
Manipulating Report RDL Files Through the SOAP Interface	583	(2)
Creating History	585	(2)
Listing History	587	(1)
Rendering Reports with SOAP	588	(16)
Understanding the Render Method's Input Parameters	590	(1)
The Render Method's Output Parameters	591	(9)
Thoughts on the ASP.NET IFrameless Approach	600	(1)
Using the Render Methods with a Windows Forms Application	601	(1)
Understanding the Session	602	(2)
Summary	604	(1)
Chapter 13 Writing a Custom Data Processing Extension	605	(46)
Building a Data Processing Extension Using the Microsoft Sample	606	(8)
Writing a Report Using the File System Data Processing Extension	614	(1)
Building a Data Processing Extension from Scratch	615	(25)
Designing the Custom Data Processing Extension	615	(1)
Understanding the Data Processing Extension Class	616	(1)
Coding the Custom Data Processing Extension	617	(22)
Impersonation	639	(1)
Deploying and Configuring	640	(4)
Deploying the New DLL	640	(1)
Tuning the Config Files	641	(3)
Building a Report with the EventLog Data Processing Extension	644	(4)
Security Considerations for Stored Credentials	648	(1)
Summary	649	(2)
Appendix A Installing SSL on a Web Server	651	(10)
Installing SSL on Your Web Server	651	(10)
Installing a Certificate Authority on Your Domain Controller	652	(2)
Installing SSL on Your Website	654	(7)
Appendix B Using Secure Sockets Layer for Reporting Services	661	(38)
Creating an SSL Certificate to Ensure Your Confidential Reports Remain Confidential	661	(2)
What Is SSL and Why Is It Important'?	663	(7)
SSL and Reporting Services	663	(2)
How Does Reporting Services Use SSL?	665	(1)
Getting an SSL Certificate	666	(4)
Obtaining a Web Server SSL Certificate	670	(23)
Caveat About Stand-Alone Certificate Authority	693	(2)
Install Reporting Services	695	(8)
Reporting Services Configuration Files	696	(3)
Appendix C Configuring SMTP	699	(4)
Appendix D Using the IIS Metabase Editor	703	(4)
Resolving 403.9 Errors on Windows XP	703	(4)
Further Thoughts-IIS Timeouts	705	(2)
Appendix E Reporting Services Books Online	707	(2)
Index	709

Excerpts

How This Book Came to Life In the late summer of 2003, although in the sands of time it seems a lifetime away on another planet in a parallel universe, the warmth of the late afternoon sun was beaming down on Bill and me, while we sat in the gardens of a hotel near the campus of the Microsoft UK headquarters. We'd just given a number of successful presentations at a VBUG developer's conference, and we were both "high" on the post-conference euphoria. One of the sessions I had presented was on Reporting Services for Yukon--that's SQL Server 2005, but at that stage it was still in alpha builds. The Reporting Services session had been well received, and, as always when Reporting Services is first shown to developers, there was a noisy and palpable interest from the developer community, so many desperately clamoring to be released from the chains of other obtuse reporting software they were compelled to use. Fortunately, Microsoft had announced it was decoupling Reporting Services from the SQL Server 2005 project to be able to release it early. It was designed to work with SQL Server 2000, so there was quite some excitement in the air. As we sat there and ordered more drinks, I idly suggested that we could veryeasilywrite a book on Reporting Services--after all, there was nothingtoocomplicated to Reporting Services. It's so intuitive that it couldn't take us long to turn out a 200-page book. Peter: Ahem! By the way, Bill, I wasn't serious. It was an idle pipe dream, just like, "when I grow up I'm going to be an astronaut." Bill: Is that why we put an astronaut space-walking on the cover of the book? Or is it because in the end we took the rocket science out of Reporting Services? Bill seized the idle, naive chatter at face value, and a few months later, after lengthy negotiations with Addison-Wesley, we started writing this book. We figured it would be done a few weeks after the product release, and we could have spent a leisurely spring lecturing and collecting royalties.Anyway, soon after we got started, my life stopped--perhaps just my old life--and a time warp began; I'm an awfullotolder now, and strangely my grandparents seem much younger than me. You see, that initially conceived "easy" 200 pages mushroomed as we covered more ground in the technical detail we'd be proud to put our names to. I tried telling Bill that I always assumed that Sondra Scott, our acquisitions editor at Addison-Wesley, really had 4-point print in mind when calculating 200 pages. Bill: What I didn't know was that Peter was thinking about 200 pages of microfiche. Who Is This Book For? When we are asked this question we usually answer "yes." This book is for you if you do anything with Reporting Services and value the enterprise security of your systems. So, that includes anyone from a user or manager with little technical knowledge, to a report developer or systems integrator, right up to and including the Reporting Services rocket scientists working with Microsoft. We kid you not; we were privileged to have worked very closely indeed with the Microsoft Reporting Services development team as we wrote this book, and get e-mails from members of the team asking us how we had implemented certain functionality, and we also fed back a lot of information to Microsoft as we wrote the book. In fact, one of the reasons we held back from publishing too soon after the launch of Reporting Services was because as a result of feedback we had given certain security-related functionality was scheduled to be placed in Service Pack 1. At that point, we realized too many issues needed resolution to rush the book to press. We could not in good conscience publish a book that did not give the reader the complete story. Since it includes detailed information on what Service Pack 1 fixes (and