Professional Microsoft® SharePoint® Server 2007 Reporting with SQL Server® 2008 Reporting Services

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-48189-9

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2009931749

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft, SharePoint, and SQL Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

I dedicate this book to my wonderful wife, Kader, and our daughter, Tuana.

—Coskun Cavusoglu

I dedicate this book to my wife, who has never once complained about not having a book dedicated to her in the past, and to my kids, Matthew, Hayden, and Wendy, for continually inspiring me to be better than I was yesterday.

—Jacob J. Sanford

I dedicate this book to my parents, who gave me my past and continue to encourage and support me towards my vision of the future. I am enormously thankful to have been allowed to follow in their footsteps.

—Reza Alirezaei

About the Authors

Coskun Cavusoglu is the Director of Consulting Services and the Chief Solution Architect of Captaré Consulting, LLC. He has been architecting and implementing technology solutions for more than 10 years in both large, enterprise organizations and fast-growth midmarket firms. As a Software Engineer specializing in the .NET Framework and the Office Server System, specifically Windows SharePoint Services and Microsoft Office SharePoint Server, he has extensive experience designing, implementing, and supporting Internet solutions using Microsoft technologies. Coskun is a technology writer, blogger, and published author on Microsoft server products. His latest book which he co-authored with fellow SharePoint experts is Professional Microsoft SharePoint 2007 Design by Wrox Publishing. He also is a speaker who attends various community events both locally and internationally where he talks about SharePoint topics and his past and future engagements can be found at his blog, www.SharePointCoskun.com. Coskun is a Microsoft Certified Professional and is also an MCTS for SharePoint Portal Server 2003 and Microsoft Office SharePoint Server 2007.

Jacob J. Sanford is a senior consultant with Resolute Solutions Corporation, a Microsoft Gold Certified Partner and a National Systems Integrator. He has been working with web application development using Microsoft technologies for more than 10 years, specializing in .NET solutions since the 1.0/1.1 Framework. Jacob is a frequent speaker at local and regional .NET and SharePoint events and is the creator of the Tallahassee SharePoint Experts Exchange for Developers (SPEED), a SharePoint User Group in Tallahassee, FL. He has written two previous books for Wrox Press: ASP.NET 2.0 Design (September 2007) and Professional Microsoft SharePoint 2007 Design (September 2008). Jacob is a Microsoft Certified Technology Specialist in the .NET Framework 2.0 (Web Applications) and a Microsoft Certified Technology Specialist (Microsoft Dynamics CRM 4.0 Customization and Configuration).

Reza Alirezaei is an independent consultant and Microsoft Office SharePoint Server MVP with a focus on developing custom solutions with SharePoint, Office, Reporting Services, and the .NET platform. As a technical leader with more than 10 years of experience in software, he has helped many development teams architect and build large-scale, mission-critical applications. This former SQL Server Reporting Services MVP has been also nominated and awarded three times as one of the Top 20 Contributors to the Microsoft Developer Network (MSDN) Wiki since 2006. In addition to consulting, Reza is a SharePoint instructor and speaker. He speaks at many local and international conferences. Reza frequently blogs at http://blogs.devhorizon.com/reza and can be reached at reza@devhorizon.com.

Credits

Acquisitions Editors

Paul Reese

Katie Mohr

Project Editor

Kelly Talbot

Technical Editors

Darrin Bishop

Kanwal Khipple

Production Editor

Rebecca Anderson

Copy Editor

Christopher Jones

Editorial Director

Robyn B. Siesky

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Carrie Hunter, Word One

Dr. Nate Pritts, Word One

Indexer

Robert Swanson

Acknowledgments

Coskun, Jacob, and Reza would like to first thank everyone at Wrox that helped make this book happen. To Katie Mohr, the Acquisitions Editor who first green-lighted the project, and to Paul Reese who carried the torch when she took a new position at Wiley, thanks for getting us going and having faith in us to get this book out. To Kelly Talbot, the Development Editor that has worked with both Coskun and Jacob in the past, thanks for working with us again and for keeping us motivated. You put the pressure on when we needed it and told us everything was going to be okay when we needed that. You have been a constant friend and source of great support and guidance throughout this entire process. Again.

To Darrin Bishop and Kanwal Khipple, the Technical Editors for this book, we thank you for your time and expertise. Without your keen observations and technical recommendations, this book would not be as strong. You made us rethink our original ideas and helped us improve upon them. This book is much better because of your involvement.

To all of our friends and families, thank you so much for your continued support and understanding. Writing a book is so time consuming and, at times, frustrating that it’s amazing that all of our relationships are still intact. You are the reasons we do it and we don’t thank you enough for allowing us to do this time and time again.

To our clients, colleagues, and employers, we thank you for providing the experience and training that enables us to do what we do. None of us would be where we are today without your assistance and encouragement.

And, finally, to anyone reading this book, we thank you for patronage. We hope you get as much out of reading this book as we did in writing it.

Introduction

Microsoft Office SharePoint 2007 is a great technology. It is the fastest growing product in the Microsoft suite of applications. It provides an easy to use portal interface that conveniently integrates document management and a collaborative environment for intranet and Internet web applications. Users without any technical background can easily create lists of data, alerts, and collaborative tools that enable colleagues, no matter how far apart, to work together to get their jobs done. And, with a little knowledge and motivation, users can even set up workflows and design elements to really make their portal come to life.

However, one of the things that could be improved in SharePoint is its reporting capabilities. While it is certainly true there are some reporting functions that come with SharePoint, they are fairly limited in use and extensibility. Fortunately, though, this is where Microsoft SQL Server Reporting Services (SSRS) comes in.

SSRS provides a very rich environment to create stunning reports that quickly and accurately report data from your line of business applications. You can pull lists directly from your legacy systems and databases or even directly from SharePoint itself. You can present this data in tabular style reports with a matrix look and feel and full drill-down capabilities, or you can simply provide graphical representations of your data through the use of gauges and charts. Designing dashboards and similar reporting interfaces has never been simpler than with the latest version of SSRS, Microsoft SQL Server 2008 Reporting Services.

This book will take you through the nuances and caveats of setting up your SharePoint environment to take full advantage of SSRS 2008. You will see how to create your environment, how to plan and develop your reports, and how to deploy them to your current SharePoint environment. At the end of this book, you should have all the tools necessary to take data reporting in your SharePoint environment to the next level.

Who This Book Is For

This book is intended for the SharePoint professional who wants to learn more about integrating Reporting Services into their SharePoint environment. The book is mostly targeted towards the individual who has familiarity with SharePoint and has at least been exposed to Reporting Services in some capacity. However, special attention was paid to try to ensure that if you are pretty new to either technology (or both of them) that you can still follow along with the examples and concepts in this book.

What This Book Covers

This book covers all the concepts of running Microsoft SQL Server 2008 Reporting Services in SharePoint Integrated mode in your Microsoft Office SharePoint 2007 environment. You will see how to take your existing SharePoint environment and set up reporting services to be an integral part of the farm. You will actually go through the process of setting up SQL Server 2008 from scratch and setting its properties so that it runs in SharePoint Integration mode, which you will also learn about.

You will find out what goes into planning a good report and what features are available so that you know how to think about the final report while still in the design and planning phase. You will get an overview of the tools used to create reports in SSRS and how to create a simple report using the integrated report wizard.

You will learn how to create more complicated reports by learning how to replicate much of the wizard functionality manually. You will see how to include data from different data sources and how to make it all work together under the hood to make useful reports for your own projects.

Next, you will get into deployment for your reports. You will see some basic deployment steps and then get into much more advanced deployment techniques, such as deploying through PowerShell and Managed Code.

Finally, you will get into different ways to display your reports once they are deployed. You will get exposed to the web parts that come with SSRS that are specifically intended to help display your reports in your SharePoint environment. You will also see some cool things SharePoint can do with displaying reports that you may have never even considered.

However, one thing that is intentionally not covered in this book is setting up your initial installation of SharePoint. This books begins with the assumption that you have a SharePoint environment already set up and active. This book just shows you how to make SSRS 2008 an integrated part of that environment.

How This Book Is Structured

The audience for technical books typically falls into one of two categories. The first category is the person that will read the book cover to cover and try all the examples to try to get a really deep understanding of a new (at least to them) technology. The second category is the group that will use the book simply as a reference guide that they pull out when they have a specific issue they need help resolving. This book was written in the hopes that it could satisfy both audiences.

The book flows in a logical sequence to developing reports. First, you will see how to set up your reporting development/production environment, including server requirements and detailed installation instructions for setting us SSRS 2008 to run in your SharePoint environment. Next, you will discover best practices and guidance for planning your report and gathering requirements for your report projects. You will move on to gain an overview of setting up your development tools and how to use them. At this point you will be prepared to create your reports and, as such, you will spend three chapters doing exactly that. The next chapters will target the deployment and display of your reports so that you can make your reports to be viewable to the right people.

However, the authors of this book wanted to make sure that each and every chapter could stand on its own. For the most part, you don’t need to have the end product of one chapter to start the next chapter. In other words, you don’t have one report from Chapter 5 that you need to have at a certain point in its development before you can start Chapters 6 or 7. This way, if you want to learn a specific technique from, say, Chapter 9, you don’t have to read the preceding eight chapters to follow the examples in that chapter.

Of course, this autonomy isn’t always completely possible. The first chapters are about setting up SSRS to run in your SharePoint environment, and all subsequent chapters rely in some part on that being in place. So, if you don’t have SSRS set up properly, you may have difficulty deploying reports to your server. But, as much as was possible, the authors tried to make the book as solid a desk reference as it is a page-by-page manual.

What You Need to Use This Book

The most important thing you need for this book is the motivation and desire to get deep into a combination of technologies that not a lot of people are very deep in.

But more related to general prerequisites, you should be at least moderately technical in some capacity. This doesn’t mean that you have to be well versed in SharePoint or SSRS. Having a .NET background, for example, would be perfectly suitable. But much of the discussion in this book is technical, and if you don’t have something of a technical background, you may find it hard to follow the examples. For many of the critical ideas presented in this book, the authors tried to at least give a cursory explanation or a link where you can learn more, even if a detailed discussion of the topic was deemed out of this book’s scope. So, even without a really strong technical background, the initiated reader should be able to follow along.

As far as specific hardware and software requirements are concerned, the book begins with the premise that you have WSS 3.0 or MOSS 2007 already installed and available for use as the development environment for the projects in this book. So, before getting too far into this book, you need to make sure you have that in place.

You will also need to have some version of SQL Server 2008 installed with its tools to get the development tools you will use in this book.

Finally, for at least one of the chapters in this book, you will need a copy of Visual Studio 2008.

In at least the first chapter when an application is needed, the authors will point out where you can get the application and, if a known free version exists, how to get it for free.

Conventions

To help you get the most from the text and keep track of what’s happening, we’ve used a number of conventions throughout the book.

Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

Notes, tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

We show keyboard strokes like this: Ctrl+A.

We show URLs and code within the text like this: persistence.properties.

We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use gray highlighting to emphasize code that's particularly important in the

present context.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at http://www.wrox.com. Once at the site, simply locate the book’s title (either by using the Search box or by using one of the title lists), and click the Download Code link on the book’s detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; this book’s ISBN is 978-0-470-48189-9.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, such as a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher-quality information.

To find the errata page for this book, go to www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book’s errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don’t spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We’ll check the information and, if appropriate, post a message to the book’s errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to email you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

4. You will receive an email with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P, but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum emailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

Chapter 1

An Introduction to SQL Server Reporting Services

If you have been doing web application development or design for any amount of time, you have almost assuredly been asked to create reports. Maybe you have created dashboard reports that show very high-level snapshots of productivity or other key measures. Perhaps you have created drill-down employee utilization reports so that managers can see how employees are spending the majority of their time. Or maybe Human Resources has needed to create summary reports for annual performance reviews. Whatever the reason, as a developer, creating data reports has probably been required of you.

To this end, you have many options. You can obviously create a custom reporting solution using something like C# and its related controls to provide the intended information. And depending on the size and requirements of the data, this might be a very attractive solution.

However, what if the report features a large amount of data spanned over an extensive amount of time? And what if management wants drill-down capabilities or, even worse, graphical representations such as charts and graphs? While it is certainly possible to accomplish this in C#, it also adds a level of complexity that many developers either do not want to deal with or, quite frankly, do not have the time to deal with.

To address this need, several server-side reporting technologies have been introduced over the years. If you have been in the developer community long enough, you have probably heard of some of the big names such as Oracle Reports or Crystal Reports. In the last few years, however, Microsoft has introduced its own server-based report generation software: SQL Server Reporting Services (SSRS). While there are plenty of similarities between SSRS and its competitors, SSRS has at least one glaring advantage over any other game in town; it is free. Well, sort of. It is tightly integrated within the SQL Server infrastructure, and if you have SQL Server installed, you can also have SSRS for free. (The sort of part of free is that you have to have pay for SQL Server, obviously. But once you do, SSRS is free.)

If you grasp the importance of reporting in a business environment, you can probably see how valuable this technology can be within a SharePoint installation. After all, SharePoint is first and foremost a web portal and, as such, is full of reportable data. Whether the data consists of tasks assigned to employees in a given time span or the number of active discussion boards employees participated in this month, there is plenty of data for any SharePoint installation. But beyond that, it is also fair to state that many organizations that deploy SharePoint have other business data they would like to report within SharePoint. Maybe they have an inventory and sales system used for tracking the ongoing business of their operations. Wouldn’t it be great to be able to report this data within SharePoint without having to import the data directly into your SharePoint lists or libraries?

However, integrating SSRS and SharePoint provides some unique challenges and architecture. This book will walk you through these considerations and give you a good idea of how to best use these two technologies to make awe-inspiring reports that will accurately provide the information that decision makers want and will even allow decision makers to easily manipulate the parameters of the reports to meet their needs.

This chapter will start you on your journey by giving you an understanding of exactly what SSRS is and providing an overview of some of the obstacles you will face as you develop reports in your SharePoint environment.

And with that, it’s time to get started.

What Is SQL Server Reporting Services (SSRS)?

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows you to deploy professional-looking and powerful reports fairly easily (once everything is set up). You get the drag-and-drop functionality that you may be familiar with if you have used any version of Visual Studio over the years. You can literally drop a chart onto your report, drag some data fields to the chart, and then deploy the report. You can also do more powerful things such as create pie charts where each pie segment, when clicked, takes the user to a related matrix of data where they can drill down even further to find more detail.

One of the nicer details about SSRS is that it will allow you, as a developer, to access a variety of data sources to feed the report. Of course, since it is a SQL Server product, you can easily tie into your SQL Server database. But beyond that, you can connect to a whole slew of other data objects, as seen in the following table (obtained from http://msdn.microsoft.com/en-us/library/ms159219.aspx):

Source of Report Data | Reporting Services Data Source Type | Version of Data Source
SQL Server relational database | Microsoft SQL Server | SQL Server 2008
|  | SQL Server 2005
|  | SQL Server 2000
|  | SQL Server 7.0
SQL Server relational database | OLEDB | SQL Server 2008
|  | SQL Server 2005
|  | SQL Server 2000
|  | SQL Server 7.0
SQL Server relational database | ODBC | SQL Server 2008
|  | SQL Server 2005
|  | SQL Server 2000
|  | SQL Server 7.0
Analysis Services multidimensional database | Microsoft SQL Server Analysis Services | SQL Server 2008 Analysis Services
|  | SQL Server 2005 Analysis Services
|  | SQL Server 2000 Analysis Services
Analysis Services multidimensional database | OLEDB | SQL Server 2008 Analysis Services
|  | SQL Server 2005 Analysis Services
|  | SQL Server 2000 Analysis Services
XML | XML | XML Web Services or documents
Report Server Model | Report Model | Models can be created from:
|  | SQL Server 2008
|  | SQL Server 2005
|  | SQL Server 2000
|  | SQL Server 2005 Analysis Services
|  | Oracle 9.2.0.3 or later
|  | Teradata v12 and v6.2
SAP multidimensional database | Sap BI NetWeaver | SAP BI NetWeaver 3.5
Hyperion Essbase | Hyperion Essbase | Hyperion Essbase 9.3.0
Oracle relational database | Oracle | Oracle 10g, 9, 8.1.7
Oracle relational database | Part of OLEDB | Depends on version of MDAC
Teradata relational database | Teradata | Teradata v12
|  | Teradata v6.20
DB2 relational database | Customized registered data extension name |
Generic OLEDB data source | OLEDB | Any data source that supports OLEDB
Generic ODBC data source | ODBC | Any data source that supports ODBC

When you see things like “Any data source that supports ODBC,” the field is pretty wide open. Any data source that supports ODBC? That is pretty encompassing.

One of the more interesting items on this list is the XML data source. This data source allows you to connect to pretty much any web service out there (given that you have the correct connection information and credentials). So anything not in this list? Well, you can create a web service on your server, grab the data you need, and send it back in a DataTable or DataSource object, and reporting services can read it. That is pretty powerful.

Is SSRS just Microsoft’s Version of Crystal Reports?

When looking at SSRS, it is not difficult to begin drawing comparisons to other similar products, especially something like Crystal Reports, if you have ever worked with those technologies in the past. They are very similar, at least as far as they both are tools that allow you to create reports on your business data.

So which is better? Has more features? Is easier to use? That probably depends on who you ask. If you do a Live Search for something like “SSRS versus Crystal Reports,” you will find page and page of hits and, depending on what camp the author comes from, the winner will be different on every page. Many people will say Crystal Reports is more powerful; others will argue that SSRS has better handled the primary functions of the reporting interface. Most people will probably say it also depends on how you plan to deploy your reports (web reports or standalone). However, at least in the world of SharePoint, SSRS wins.

For one thing, if you are using SharePoint, you already have access to SSRS. SSRS came as a free add-in for SQL Server 2000 and has been included with both SQL Server 2005 and SQL Server 2008. So, since you are most likely using a SQL Server technology for your data backend for SharePoint, it is a fair bet that you already have SSRS, even if you don’t have it set up yet.

Additionally, SSRS has two specific modes that can be installed: Native and SharePoint Integrated Mode. That’s right; one of the two SSRS modes that can be installed is specifically targeted toward SharePoint. Installing SSRS in this manner, as you will see in Chapter 2, will give you access to special Web Parts specifically meant to handle SSRS reports. It will also allow you to deploy your reports directly to libraries in your SharePoint site collection (as opposed to deploying them directly to the Reporting Services server).

Finally, another huge advantage is that, if you are a SharePoint developer (or any .NET developer, for that matter), you are already intimately familiar with the SSRS designer, which is called the Business Intelligence Development Studio (BIDS). This is because BIDS is simply an add-on to Visual Studio 2008, and all development work, as such, is done within the Visual Studio application. So when you begin development of your SSRS 2008 reports, you will be designing them within Visual Studio 2008, which you probably are already familiar with. To verify this, you need only to glance at Figure 1-1.

Figure 1-1

You will get an overview of BIDS, including where to get it and how to install it, in Chapter 4.

It is worth mentioning at this point that there is another option for developing SSRS 2008 reports: the Microsoft SQL Server 2008 Reporting Services Report Builder 2.0. You can download this product from the following location: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en. Alternatively, you could just use the following shortened reference to the same URL: http://tinyurl.com/ReportBuilder2-0.

This application, also free, is a standalone report designer that has all of the same features of BIDS in an arguably better Microsoft Word-like format, as can be seen in Figure 1-2.

Figure 1-2

While this might be a viable option for your own report development and certainly warrants your own evaluation, it will not be used for this book. Since BIDS is just using the Visual Studio 2008 shell, it will be familiar to a lot of SharePoint developers. And even if you are not a SharePoint developer, there is still a chance you have used Visual Studio in your experience in development. So the interface won’t be entirely new. Although Report Builder might work for you, it would be easier to explain BIDS within the Visual Studio context than it would be to explain an entirely new concept. And if you are already using Visual Studio, you will probably be more comfortable staying there. Thus, it isn’t really feasible to show how to create reports generated in both platforms throughout the entire book; using only one platform is more practical, and BIDS wins out. Much of the planning and development you do in BIDS, however, will transfer (knowledge-wise) in your future attempts to use Report Builder if you should so choose.

What Are Some of the New Features of SSRS 2008?

As with any new software release, SSRS 2008 has some very cool new features that appeal not only to the developers who create the reports but to the end users who will use them in their decision-making strategies. These include things like enhanced rendering extensions and graphs for the end user, as well as a simplified development experience with better integration to your current server infrastructure.

A Better User Experience

One of the most discussed new features of SSRS 2008 is the incorporation of Dundas charts bundled as part of the development environment. These include both radial and linear graphs, as well as some pretty neat “slider” charts. You will see a lot more of these charts in later chapters but, to give a small tease, look at a simple dashboard report that can now be easily created using the new Dundas Radial chart as seen in Figure 1-3.

Figure 1-3

While these new charts and graphs are impressive on their own, it is also worth noting that Microsoft has added 3D rendering to all of its previously included charts. For example, in SSRS 2005, you could produce an exploded pie chart similar to the one shown in Figure 1-4.

Figure 1-4

However, with the exact same data and with no additional formatting (i.e., using the default settings), you can now also produce a 3D version of the exploded pie chart, shown in Figure 1-5.

Figure 1-5

While the difference may be hard to see in the black-and-white constraints of this manuscript, it will become very apparent in the charts you create. Along with the Dundas gauges, your reports will have a level of elegance and professionalism not available out of the box in earlier versions.

In addition to all of the enhanced graphs and charts discussed in this section, it is also worth noting that reports can be exported in several different formats. As has been possible since at least the SSRS 2005 release, you can export your report directly from the web version to formats such as PDF, Excel, and CSV. With SSRS 2008, however, you can also export directly to Microsoft Word. This provides a new level of flexibility for generating and distributing your reports to those who need them.

As a final note with regard to a better user experience, users now have better integration with their SharePoint environment for reports deployed in SharePoint integrated mode (you can read more about SharePoint integrated mode in Chapter 2). For example, users can now create data-driven subscriptions for their reports deployed to a SharePoint library.

While there are several other features that directly apply to the end user, the ones highlighted in this section are surely some of the biggest and will get users excited about upgrading to the new SSRS 2008.

A Simplified Development Experience

While there are several new developer enhancements in SSRS 2008, the biggest one is probably the fact that SSRS no longer requires Internet Information Services (IIS) to run. And yet, even with this being true, none of the functionality of previous versions has been lost. This is done through the use of native support for HTTP.SYS and ASP.NET; URL management for site and virtual directory names; a new authentication layer; and health monitoring through new memory-management features. This is a pretty impressive change in the architecture of SSRS and will get a lot of developers buzzing.

Perhaps the most exciting part of diving into the developer experience in SSRS 2008 for the first time is that once you get started you will feel at home, especially if you are used to creating web applications using .NET technologies. If you are used to creating your web application pages in Visual Studio, guess what? You will be creating your reports in Visual Studio. If you are used to modeling your data backend in SQL Server, you can still do that, too. Maybe you are used to accessing web services to mash up data to display on your pages. You have the power to do that in SSRS as well.

While this is still going to be a little intangible this early in the book, if you have done web application development using tools like Visual Studio and SQL Management Studio, you will find yourself thinking “hey, I’ve done something very similar to this in the past,” or “this is much easier than I thought it would be,” several times as you read the following chapters.

However, at the same time, you will be able to tap into a power that you generally don’t get in your other applications. You will have a fairly easy way to create eye-catching graphical interfaces that pretty much all management loves to see. You will be able to take your data and, rather than just report it, you can showcase it. You will find how easy it is to create dazzling reports with simple drag-and-drop controls and intuitive designers. You will take what is familiar and go with it to a new and exciting place.

While the end users will get a lot out of the reports and the managers will certainly enjoy the end product, the development experience will be one that is familiar and, hopefully, a lot of fun to work with.

SSRS Deployment

While this will be covered in much more detail later in this book, deployment warrants at least a mention in this introductory chapter, as it is a crucial part of the reporting process. After all, if your users can’t get to the reports, what value do they have?

When SSRS is set up in SharePoint integrated mode (see Chapter 2), a couple of things happen. First, you actually deploy your reports and their related data sources directly to SharePoint libraries hosted in your target site collection. This means that, at any given time, you can navigate to your site, find your reports library, and see a listing of the currently deployed reports. You can even run the reports directly from the library simply by clicking them.

Another thing that happens is that you get new Web Parts installed on your server that will be available to all site collections that utilize SSRS. These Web Parts will allow you to set up a report to be hosted directly on any page within your site collection. This way, the reports will look as though they are a seamless part of your existing page, as shown in Figure 1-6.

Figure 1-6

Summary

This chapter has provided a basic overview of SSRS and the new features available in SSRS 2008. You have seen the basic development tools available to create new reports and some of the enhancements to the user and development experience that make SSRS 2008 much better than any of its predecessors. You have also been given a brief glimpse of how your reports integrate with your SharePoint installation and how your reports will look and feel once deployed to your site collection.

This chapter was meant to provide an SSRS 2008 and SharePoint integration. It was meant to foreshadow a bit for future chapters and to offer at least a cursory understanding of SSRS so that subsequent chapters will be more accessible. And with that it is time to get to the fun stuff: making reports.

Chapter 2

Setting up SQL Server Reporting Services for SharePoint 2007

This chapter mainly details how you can install SQL Server Reporting Services in SharePoint integrated mode. The chapter starts with the benefits of SharePoint integrated mode and dives into the steps you must take before installing SQL Server Reporting Services in SharePoint integrated mode. The chapter also features a step-by-step installation guide with screenshots that will provide guidance for your installation.

Let’s start the installation process by answering a big question: Why SharePoint integrated mode?

Why SharePoint Integrated Mode?

When you have SharePoint in your organization, this is one of the main questions you will find yourself trying to answer. The short answer is that it’s easier to maintain, easier to secure, and easier to access reports when you already have a SharePoint installation.

Microsoft first introduced SharePoint integrated mode capabilities with SQL Server 2005 SP2. Before integrated mode was available, there was partial integration provided by two web parts: Report Viewer and Report Explorer. Although these web parts offered an easy way to display SQL Server reports within SharePoint, this was not a full integration of technologies. In reality, the major components of both SharePoint and SQL Server Reporting Services, such as security and storage, were not integrated. SharePoint and SSRS handled their storage needs separately by having distinct databases for storage. Security was another component handled separately on both SharePoint and SSRS, so the web parts just provided a window to the report server, not true integration.

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!