Hitchhiker's Guide to SQL Server 2000 Reporting Services by Peter Blackburn, William R. Vaughn

From Barnes & Noble

The Barnes & Noble Review
If you build reports for SQL Server 2000, Microsoft’s new Reporting Services will make your little heart go pitty-pat. This book covers Reporting Services from soup to nuts: installation, configuration, report design, on-demand (pull) and event-based (push) delivery, and a whole lot more.

This book’s based on the final product, but the authors worked closely with Microsoft throughout its alphas and betas. They focused especially on security -- and this book presents dozens of security gotchas, techniques, and best practices, many covered in no other book.

The accompanying DVD contains dozens of recorded video demos walking you through some of Reporting Services’ most powerful techniques. Even better, buyers get six months’ free access to the authors’ Reporting Services private newsgroup, discussion groups, and tech support services. Try finding a deal like that elsewhere. Bill Camarda

Bill Camarda is a consultant, writer, and web/multimedia content developer. His 15 books include Special Edition Using Word 2003 and Upgrading & Fixing Networks for Dummies, Second Edition.

From the Publisher

“Bill Vaughn and Peter Blackburn have created a masterful work of Reporting Services documentation, which includes tips, tricks, and product insights that you just won’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, Hitchhiker’s Guide to SQL Server 2000 Reporting Services provides 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 called Hitchhiker’s Guide to SQL Server 2000 Reporting Services, I like to think of it in more of a movie context as Bill and Peter’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 customers’ reporting requirements since Access 1.0 in 1992. It’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 thedeveloper 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 read–contains 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 Services picks 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™ video demonstrations that show you how to safely navigate the trickier parts of this technology. The DVD also includes the evaluation version of Reporting Services, Books OnLine documentation refresh kit, Service Pack 1, code samples, and much more. A special website provides book and technology updates, as well as new templates and add-ins. Readers also receive six months of free access to a Premium Content area, which includes a private newsgroup, discussion groups, and technical support.

Key topic coverage includes:

• Reporting Services basics, including the Report Designer and Report Manager
• Data Source, data, and report security, including Code Access Security
• Solving installation, design, configuration, security, and subscription problems
• Report scripting and using the SOAP interface
• How to customize the Report Manager and Report Designer
• How to create Custom Data Processing extensions
• Up-to-the-minute tips, fixes, and workarounds

Insightful, fast-paced, and fun, Hitchhiker's Guide to SQL Server 2000 Reporting Services is your inside guide to getting the most from Reporting Services.

Books in the Microsoft Windows Server System Series are written and reviewed by the world’s leading technical authorities on Microsoft Windows Technologies, including principal members of Microsoft’s Windows and Server Development Teams. The goal of the series is to provide reliable information that enables administrators, developers, and IT professionals to architect, build, deploy, and manage solutions using the Microsoft Windows Server System. The contents and code of each book are tested against, and comply with, commercially available code. Books published as part of the Microsoft Windows Server System Series are an invaluable resource for any IT professional or student working in today’s Windows environment.

Product Details

• Table of Contents
• Forewords & Introductions

Table of Contents

Forewords & Introductions

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 very easily write a book on Reporting Services—after all, there was nothing too complicated 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 becausein 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 awful lot older 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.

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 what it doesn't), it will prove to be the most current book on the market. Even if you've bought another Reporting Services book, we're certain that this book will be well worth the price.

Yes, this book is most especially for you and the developers you support. Reporting Services is a great product, and it gives users a lot of long-sought after powerful functionality. But, and this is a big but, if you as a DBA implement Reporting Services in an insecure way, you might as well post the SA password and your private data on a public newsgroup or display it in Times Square for the world to see. We appreciate that Reporting Services calls for skills that fall outside the hitherto usual skill set of a DBA. Because of this we've gone to great lengths to ensure that we explain the skills you'll need in an easy-to-read but concise way. You see, Reporting Services is a product that also requires IIS skill. Many DBAs are gurus with the Query Analyzer and SQL Profiler. They're used to interrogating poorly coded T-SQL in stored procedures, rhythmically tut-tutting as they see yet more unbelievably clumsy T-SQL programming. However, when it comes to IIS,.NET programming, and SOAP, some DBAs break out in a cold sweat and freeze when developers start talking "OOP".

Well, hey, relax! We'll walk you through all of these dark alleys with a 20,000 candle-power light and an armed escort. Chapter 1 gives you insight into all the components of Reporting Services. Chapter 2 talks you through installing Reporting Services, showing you how to ensure that you choose the right accounts. This way you don't leave a back door open to your databases for all the (evil/curious/adventurous) ASP.NET developers in your organization. It also talks you through how to ensure that the Reporting Services website has an SSL certificate. This is very important. We firmly encourage you to setup Reporting Services with an SSL certificate. If you don't, we are aware of sniffing vulnerabilities that can be targeted to extract credentials as they are passed around the network. During Reporting Services installation, an SSL certificate is required by default and for excellent, important reasons. Microsoft goes to great lengths to encourage you to use SSL—they really want your data to be secure (and so do we). You have to take personal responsibility if you uncheck the SSL checkbox and install without SSL.

Chapter 4 is a good source of information, as most DBAs will be responsible for administering the Report Server. All the features are explained in an incremental way. No, you won't need to write any reports, but a lot goes into the configuration of a Report Server to ensure that correct security is established, and that data source Connection strings are configured correctly. While you might not be that bothered about writing reports, Chapter 5 is also an absolute must read for DBAs. Here we strongly encourage you to switch off Integrated Security on the Report Server so that you can sleep at night. Chapter 5 will give you all the ammunition you need to make sure when you sit in those management meetings and a user is trying to get your boss to force you to switch Integrated Security back on, you'll be able explain the risks. You should also make sure they give you these orders in writing, countersigned by the manager's mother: "Yes, I know my son is a manager with no technical background or understanding whatsoever, but he really does want you to enable Integrated Security. I'll take full responsibility for him. —Mom"

Now, many DBAs have only just taken the lid off Visual Basic for Applications (VBA), and to a large extent we have DTS to thank for that. However, there is no VBA for Reporting Services. If you want to do any scripting work, you need to do that with Visual Basic.NET and the report scripting tool called rs.exe. The report scripting tool uses the Report Server's SOAP interface, so you'll also find the magic of Chapter 9 quite helpful.

If you see yourself as just a user of reports and already have Reporting Services installed, of most interest to you in this book will be Chapter 1, in which we deal with an overview of the technology, and Chapter 4, in which we go through in detail how to use the Report Manager. Chapter 4 is good to be able to work through in a tutorial style because you'll see most of the functionality of the Report Manager, assuming that your report Administrator has enabled it. And if certain features are not enabled you'll know what privileges you want to petition him or her for.

If you want to create reports, in addition to Chapters 1 and 4, where report users should concentrate initially, you'll want to take the tutorial through Chapter 3 where we show you how to use the built-in report wizard. Working through this chapter will give you a grounding before you look at Chapter 5. Chapter 5 is pretty important because it explains the security considerations you need to bear in mind, but to begin to appreciate it you do need a good foundation. As Bill Baker (General Manager of SQL Server Business Intelligence) says in the foreword, it's a great idea to read Chapter 5 more than once. Certainly after you have worked through Chapters 6 and 7 you'll have a greater appreciation for why Chapter 5 is so important, especially if you want to keep your enterprise secure. As you become more proficient you'll want to experiment with charts and Matrix tables—those are covered in Chapter 8.

Once you're off the launch pad and feel confident in creating reports, you'll want to start pushing the envelope of what you can do with Reporting Services to make your productivity much better. In Chapter 9, we show you how and where you create report and project templates so that you can bring a consistent look and feel to your reports. We also show you how you can have styles applied to reports at runtime by creating your own.NET assemblies—and indeed we show you all you need to know about code access security as it is implemented in Reporting Services. Code access security is a complex topic, but we have gone to extreme lengths to lower the access bar to your understanding it—and if you work through it you'll feel you're head and shoulders above your colleagues. If you're already a code access security guru, Chapter 9 is still worth working through. You see, in Reporting Services there are a number of peculiarities about how code access security is implemented. As an added benefit of working through code access security, you'll probably find that you can then leverage what you have learnt to other.NET programming that you do. You'll be well on your way to becoming a.NET guru yourself.

If you're planning on utilizing Reporting Services with your own web applications, Chapter 10 will be of interest to you as it deals with how to use the Report Server via its URL interface—which is probably the best way to go. However, you may indeed be tempted to use the Reporting Services SOAP interface, so be sure to study Chapter 9. Yes that's 9, but it's not where you might suspect it is, so you'll have to look in the right place for it. A clue: 9_ is not between Chapters 9 and 10.

Now, here's the scoop. Say you already have your own application and you want to add reporting functionality to it. You look at Microsoft Report Manager, and it doesn't do just what you'd like it to do. You know it would if only you could customize it, change the colors, and most importantly specify the way parameters are collected from your users. You see, natively the Report Manager doesn't support multiple column selects. Well, the choice is yours. You might try to build your own Report Manager, which is a tedious amount of work, depending on how much functionality you are going to put into it. Or you can phone Microsoft and ask for the source code to the Report Manager. You'll probably hear the same thing we did, but Addison-Wesley won't print it—it wasn't very nice (something about open source being the bane of the industry). Or you can spend literally a few minutes^* tweaking the Report Manager by updating its cascading style sheet and implementing HTML behavior files to work with your custom parameter user interfaces. Well, Chapter 11 takes you behind the scenes and shows you how to do it. Yes, it's possible (and fairly likely) that Microsoft will come back and tweak things later; but hey, 30 minutes customizing the Report Manager is a short time investment as opposed to 30 person-months building your own Report Manager to get the same functionality, isn't it? Besides, we plan on making some tools available to help make the customizations even easier, so do check on our website (http://www.SQLReportingServices.NET). We also hope to take a proof of concept that we have working in our labs to a production product that enables people to do server-side printing from the Report Manager. Stay tuned.

If you have a unique source of data that you want to leverage, you'll need to figure out how to write your own Data Processing extension. We walk through the Microsoft example and explain how to make it work. Without our help you'll find it a bit tough—Microsoft left off a bit of information here and there. Next, we carefully guide you through the process of writing a Data Processing extension to generate reports from the Windows event logs in your Domain. You don't have to be a hardcore developer to work through Chapter 13, but if you follow the example, you'll be a very accomplished report programmer by the end of it.

When documenting a product as visually rich as Reporting Services, one of the problems is how to describe the visual aspects of the product. To address this challenge we are recording dozens of Camtasia screen capture sessions and will provide them on a DVD that ships with the book. These narrated Guide Me! sessions permit the reader to see Reporting Services in action while we walk through setup, report creation, using the Report Manager, creating subscriptions and Snapshots, and many other aspects of Reporting Services. In the accompanying DVD, we have gone much further to redefine the concept of what a technical book should provide. Our video demonstrations not only explain but also show how to navigate safely through some of the more difficult and confusing parts of the technology. The demonstrations walk the reader step by step through complex wizards by showing every step in motion—with instructor voices in the background—which creates a virtual classroom experience. These demonstrations are provided in a form that any Windows system can play. The DVD also includes lots of code samples, selected white papers, and supplementary text. This is truly a work that takes the reader far beyond the official Microsoft documentation.

Also included on the DVD are products and samples from several third-party companies that work closely with Reporting Services.

Thanks to the generosity of the Microsoft SQL Server team, we also include a demonstration version of Reporting Services that you're free to install and use on your own system.

When you purchase this book, you are provided with an individual code that enables you to register onto restricted areas of our website and have access to our support newsgroups, discussion groups, and special offers for one year. If you visit this site you'll see hundreds of threads that discuss common (and some not so common) questions and answers (if there is one). We regularly post responses here, as do many other experienced Reporting Services DBAs and developers. This is also where we'll be posting updates to any report examples, new templates, and add-ins that we might build or discover. You can also ask questions about the examples in the book (there are several dozen).

So what are you standing here reading this preface for? Take several copies over to the counter and buy them for your colleagues.

Peter Blackburn
Huntingdon, UK
July 2004

My original Hitchhiker's Guide series was started with the intent of providing a different approach to technical documentation and learning. When Hitchhiker's Guide to Visual Basic and SQL Server was published, I had been developing and presenting SQL Server, Visual Basic, and other technical courseware at Microsoft University for more than five years. Because I found this environment somewhat stifling, I wanted to create a book from which developers with almost any level of experience could get inside information—facts not shared by the Microsoft documentation or courseware. The idea was to provide fundamental information for developers just getting started, as well as advanced information for those more up to speed with the technology. The books not only told readers how to do something but why. I always included my own opinions and best practices based on decades of experience, real-world customer interaction, and direct contact with the development teams at Microsoft. The books also provided a solutions-oriented approach that showed the reader how a problem was actually solved—not endless lists of objects, properties, methods, and events. I also wanted the books to be easier to read than the Microsoft documentation, which had to be technically generic, politically correct, and (so it seemed) dull. Sure, some of the quips I used in the Hitchhiker's Guides were not understood by some Clevelandites or C++ developers, but they're both tough groups to get through to.

Sometimes the Hitchhiker's Guides brought "issues" (okay, bugs) to light that weren't always complimentary to Microsoft, and from time to time, I would hear low rumbling noises from the vicinity of the development and marketing teams on the Redmond campus (within artillery range of my house). However, once they understood that I was writing to "typical" developers in an attempt to help them better understand and use the tools, Microsoft began to understand how valuable the books could be and provided more and more assistance—and inside information. Eventually, Microsoft Press began publishing the Hitchhiker's Guide series—jabs, issues, one-liners, and all. Hitchhiker's Guide to Visual Basic and SQL Server was in its sixth edition when I retired from Microsoft in 2000 to focus on mentoring, writing, and lecturing.

At this point, I wanted to continue the popular Hitchhiker's Guide series, but I was waiting for the right subject, publisher, and technical resources. By happenstance, I met Peter Blackburn when he was assigned as technical editor for my ADO.NET book. Peter, who lives in the United Kingdom, proved to be an invaluable resource because of his vast technical depth and thorough understanding of SQL Server, Windows, security, and the web. We soon became good friends, despite the language barrier (he's a C# fan). What you might not appreciate is that Peter has the same dry "English" sense of humour that I do. When Peter approached me about doing a book on Reporting Services, I was initially hesitant, as my interests had shifted away from reporting. However, Peter's enthusiasm and in-depth knowledge of the Reporting Services technology convinced me. I agreed to help if he could provide the technical details.

Frankly, Peter is the primary author of this book. My contribution has been to rework the material to match the tone and readability of the Hitchhiker's Guide series. I also have edited the content to provide a "less-experienced" point of view so it can be more easily understood by those not as versed in the inner workings of Windows, security, the web, SOAP, CAS, LSMFT (and several other acronyms), Visual Studio.NET, and Reporting Services.

I think Hitchhiker's Guide to SQL Server 2000 Reporting Services is designed to address the needs of database administrators (DBAs), application developers, and their managers who are:

• Getting up to speed on Reporting Services and need a quick tutorial on how to get started and how to setup simple single-server installations or a web farm
• Planning to administer a Reporting Services installation where an in-depth understanding of the Report Manager, Report Designer, subscription model, security issues, report caching, and custom extensions is needed
• Learning to write and deploy reports using the Visual Studio.NET Report Designer
• Trying to solve Reporting Services installation, design, configuration, security, or subscription problems
• Importing reports from Microsoft Access or other platforms to Reporting Services
• Writing programs using the Reporting Services SOAP interface
• Designing your own custom Data Processing extensions and understanding how the extensions work
• In need of up-to-the minute fixes and workarounds for newly discovered issues as well as advice and Reporting Services developer community support

As you can see, Peter and I have ventured far off the beaten track. That's because we want this book to be the definitive source on Reporting Services. We understand that not all of our readers are familiar with SQL Server or Visual Studio, so we also provide easy-to-follow steps to get readers up to speed quickly—regardless of their experience level.

William R. Vaughn
Redmond, Washington
July 2004