Microsoft® SQL Server™ 2008 Reporting Services Unleashed

1 microsoft SQL server 2008 Reporting Services UnleashedCopyright 2010 by Sams PublishingAll rights reserved. No part of this book shall be reproduced, stored in a retrievalsystem, or transmitted by any means, electronic, mechanical, photocopying, recording,or otherwise, without written permission from the publisher. No patent liability isassumed with respect to the use of the information contained herein. Although everyprecaution has been taken in the preparation of this book, the publisher and authorassume no responsibility for errors or omissions. Nor is any liability assumed fordamages resulting from the use of the information contained : 978-0-672-33026-1 ISBN-10: 0-672-33026-1 Library of Congress Cataloging-in-Publication data is on in the United States of AmericaFirst Printing July 2009 TrademarksAll terms mentioned in this book that are known to be trademarks or service markshave been appropriately capitalized.

2 Sams Publishing cannot attest to the accuracy ofthis information. Use of a term in this book should not be regarded as affecting thevalidity of any trademark or service and DisclaimerEvery effort has been made to make this book as complete and as accurate as possi-ble, but no warranty or fitness is implied. The information provided is on an as is basis. The authors and the publisher shall have neither liability nor responsibility to anyperson or entity with respect to any loss or damages arising from the informationcontained in this SalesPearson offers excellent discounts on this book when ordered in quantity for bulkpurchases or special sales. For more information, please contact: Corporate and Government sales outside of the , please contact:International GettmanExecutive EditorNeil RoweAcquisitions EditorBrook FarlingDevelopment EditorMark RenfrowManaging EditorPatrick KanouseProject EditorSeth KerneyCopy EditorKeith ClineIndexerKen JohnsonProofreaderGeneil BreezeTechnical EditorJ.

3 Boyd NolanTechnical ContributorPrash ShirolkarPublishingCoordinatorCindy TeetersBook DesignerGary AdairCompositionMark ShirarIntroductionSQL server 2008 Reporting Services (SSRS) is a server -based, extensible, and scalable plat-form that delivers and presents information based on data that a business collects duringits , in turn, helps business managers to evaluate the current state of the enter-prise and make decisions on how to increase revenues, reduce costs, and increase customerand employee Reporting Services scope extends from traditional paper reports to interactive contentand various forms of delivery: email, file shares, and so on. SSRS is capable of generatingreports in various formats, such as Hypertext Markup Language (HTML), ExtensibleMarkup Language (XML), and Excel formats, thus allowing users to manipulate their datain whatever format is diving into this book, let s take a moment to understand the layout and some of theconventions used in the book.

4 First, we cover how this book is organized and what you canexpect in each section. Second, we cover the style and formatting conventions used in thisbook. It is particularly noteworthy to note the style changes in reference to code lines,including SQL server and .NET This Book Is OrganizedThis book begins with an introductory overview of SSRS and covers a broad range oftopics in the areas of report authoring, Reporting Services deployment and administration,and custom code development for Reporting chapters in Part I, Introduction to Reporting Services , provide a high-level overviewof Reporting Services and highlight key features of the Reporting Services , deploymentscenarios, typical users of Reporting Services , and Reporting Services architecture.

5 This partallows for leisurely reading and does not require you to have access to a chapters in Part II, Report Authoring from Basic to Advanced, take you throughreport development tools and processes. This part describes report building blocks andwalks through building a report from simple to SQL server 2008 Reporting Services UnleashedThe chapters in Part III, Reporting Services Management, discuss advanced topics ofReporting Services administration, such as setting proper security, managing ReportingServices as individual servers and in a web farm, and gathering report-execution chapters in Part IV, Reporting Services Customizations, are for those of you whomight want to extend Reporting Services and incorporate reports into your part covers key programmable aspects of Reporting chapters in Part V, SharePoint Integration.

6 Provide detailed drill-down into runningReporting Services in SharePoint integrated mode. If you are interested in allowing yourusers to view and manage reports from their SharePoint sites, this section providescomprehensive information on architecture, installation, tools, and Used in This BookSQL server 2008 Reporting Services is frequently abbreviated as SSRS, and where appropri-ate we may distinguish between versions of the product such as SSRS 2005 or SSRS Intelligence Development Studio is frequently abbreviated as features available in SQL server 2008 Reporting Services as compared to SQL Server2005 Reporting Services are labeled with New in 2008 . Names of products, tools, individual windows (docking or not), titles, and abbreviationsare capitalized: for example, SQL server , SQL server Reporting Services , Visual Studio,Report Designer, Report Builder, Report Manager, Windows, and so used to highlight the following.

7 Sections of code that are included in the flow of the text: Add a text box to areport and place the following code in the Background Colorproperty:= (value)..Filenames: Visual Studio creates a project with a single class Class1. Let s Solution Explorer to .Pathnames:Report server (the default directory is C:\Program Files\MicrosoftSQL server \ \ Reporting Services \ReportServer\bin)..Error numbers, codes, and messages: [rsRuntimeErrorInExpression] The Valueexpression for the textrun [0].TextRuns[0] containsan error: Request for the permission of type , mscorlib, Version= ,Culture=neutral, PublicKeyToken=b77a5c561934e089 failed..Names of permissions, constants, properties, collections, and variables:Execute,Fields,ReportParameter , indicate adjustable information, we use the following.

8 {}, mostly where the variable information can be confused with XML (for example,<Value>{EXPRESSION}</Value>). In this example, an {EXPRESSION}is any validexpression, such as =Fields! <>, where the variable information cannot be confused with XML (for example,=Fields!<Field Name>.Value).CHAPTER1 Introduction to SQLS erver ReportingServices (SSRS)IN THIS Is SSRS?.SSRS for End of in the ReportDevelopment Life of Reporting Is SSRS Licensed?In today s ultracompetitive business environment, havinggood information is essential. Companies are awash ininformation, and with the advent of technologies such asradio frequency identification (RFID), more and more infor-mation is coming. Technology has made the job of gather-ing information trivial, but making sense of it all stillremains elusive.

9 This makes good Reporting and businessintelligence tools first chapter is strictly nontechnical. This chapterfocuses on the following:.Capabilities of it fits into the microsoft Business development life cycle as it relates to of SSRSNOTEThis book abbreviates SQL server 2008 ReportingServices as SSRS and SQL server 2005 ReportingServices as features available in SSRS and not in SSRS2K5are labeled with New in 2008 . 6 CHAPTER 1 Introduction to SQL server Reporting Services (SSRS)TABLE of Information WorkersType of User PercentageTechnicalExpertiseExpectationA nalysts5% 10%HighAnalysts can develop reports, work with adhoc reports, and perform sophisticated calcu-lations (such as linear regressions and trendanalysis).

10 Analysts often publish reports toexplorers and Is SSRS?SSRS is microsoft s answer to business Reporting . It provides a unified, server -based, exten-sible, and scalable platform from which to deliver and present information. Its scopeextends from traditional paper reports to web-based delivery and interactive content. SSRScan also be configured to deliver reports to people s inboxes, file shares, and so on. SSRS iscapable of generating reports in various formats, such as the web-oriented HypertextMarkup Language (HTML) and desktop application ( microsoft Excel and CSV) formats,thus allowing users to manipulate their data in whatever format is required. In addition,SharePoint can be used as a front end for SSRS, allowing reports to be presented directly incorporate is just one of the components in the microsoft Business Intelligence (BI) , those components provide an excellent platform for enterprise data microsoft BI platform includes the following.