Microsoft SQL Server 2005 Reporting Services

by Lisin, Michael; Joseph, Jim

• ISBN13: 9780672327995
• ISBN10: 0672327996
• Edition: 1st
• Format: Paperback
• Copyright: 2006-01-01
• Publisher: Sams

Summary

SQL Server Reporting enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions.Microsoft SQL Server 2005 Reporting Serviceswill help you understand Reporting Services from various perspectives: How it fits business scenarios How to install and configure Reporting Services Reporting Services components Developing reports As you progress to advanced user status, you will enjoy the chapter about accessing Reporting Services programmatically and extending Reporting Services, and you will be able to use this book as a convenient reference.

Table of Contents

Introduction	1	(4)
PART I: Introduction to Reporting Services
Introduction to SQL Server Reporting Services (SSRS)	5	(18)
What Is SSRS?	6	(1)
SSRS for End Users	6	(2)
Overview of Features	8	(1)
Enterprise Report Examples	8	(1)
SSRS in the Report Development Life Cycle	9	(10)
Authoring Stage of the Reporting Life Cycle	9	(4)
Managing Stage of the Reporting Life Cycle	13	(4)
Delivery Stage of the Reporting Life Cycle	17	(2)
Editions of Reporting Services	19	(1)
How Is SSRS Licensed?	20	(1)
Summary	21	(2)
Reporting Services Capabilities: Presentation, Navigation, and Programmability	23	(12)
Report Layouts	23	(3)
Tabular Report Layout	24	(1)
Free-Form Report Layout	24	(1)
Matrix Report Layout	25	(1)
Report Presentation (Rendering) Formats	26	(1)
Report Items, Visual Effects, and Charting	26	(3)
Report Navigation	29	(1)
Ad Hoc Reports New in 2005	29	(1)
Reporting Services Data Access Features	30	(1)
Programmability	30	(4)
Summary	34	(1)
Reporting Services Architecture	35	(14)
Report Server Web and Windows Service	36	(1)
Programmatic Interfaces	37	(1)
Report Processor	37	(1)
Command-Line Utilities	38	(1)
Reporting Services Extensions	38	(3)
Data-Processing Extensions	39	(1)
Delivery Extensions	40	(1)
Rendering Extensions	40	(1)
Security Extensions	41	(1)
Report Server Databases	41	(1)
Scheduling and Delivery Processor	42	(1)
Report Builder New In 2005	43	(1)
Report Model Designer New In 2005	44	(1)
Report Designer	44	(1)
Report Manager	45	(1)
SQL Server Management Studio New In 2005	46	(1)
Reporting Services Configuration Tool New In 2005	46	(1)
RSPrintClient Control New In 2005	46	(1)
WMI Provider	46	(1)
Performance Monitoring Objects	47	(1)
Summary	47	(2)
Reporting Services Deployment Scenarios	49	(16)
High-Availability Deployment Considerations	53	(1)
Internet Deployment Considerations	53	(5)
Internet Deployment Option 1: Enable Report Server for Anonymous Access	54	(1)
Internet Deployment Option 2: Deploy Report Server with Windows Authentication	55	(1)
Internet Deployment Option 3: Use the Programmatic Approach	55	(1)
Enabling a Report Manager for Internet Access	56	(2)
Minimum Hardware Requirements	58	(1)
Software Requirements	59	(2)
Key Features of SSRS by SQL Server 2005 Editions	61	(2)
Licensing	63	(1)
Summary	64	(1)
Installing Reporting Services	65	(8)
Summary	69	(4)
PART II: Report Authoring from Basic to Advanced
Report Designer	73	(18)
Two Main Report Designers from Microsoft	73	(1)
Visual Studio Haves Versus Have Nots	74	(1)
Solution, Project, File Hierarchy	74	(1)
Generating Reports with Visual Studio	74	(16)
Layout Screen	82	(8)
Summary	90	(1)
Report Definition Language	91	(10)
Language: A Way to Communicate	91	(1)
Use of XML	92	(1)
Declarative Programming	92	(1)
Report Elements	93	(6)
Report Element	93	(1)
Report Parameters	94	(1)
DataSets	95	(3)
Report Items	98	(1)
Data Regions	99	(1)
Summary	99	(2)
Expressions	101	(20)
What Is an Expression?	101	(1)
Expression Syntax	102	(2)
Adding Expressions	104	(1)
Collections	105	(4)
ReportItems	106	(1)
Fields	106	(1)
Parameters	107	(1)
Globals	108	(1)
User	108	(1)
Using Functions	109	(6)
Visual Basic Runtime Functions	109	(4)
Aggregate Functions	113	(2)
Other Functions	115	(1)
Using Expressions to Change Report Item Properties and Behavior	116	(2)
Example: Placing Emphasis on Certain Values	118	(1)
Summary	119	(2)
Accessing Data	121	(12)
Data-Processing Extensions	121	(1)
Types of Data Sources	122	(1)
Report-Specific Data Source	122	(1)
Shared Data Source	122	(1)
Data Source Expressions	123	(1)
Connection Strings	123	(2)
Querying Data	125	(6)
Graphical Query Designer	125	(1)
Generic Query Designer	125	(2)
Command Type	127	(1)
Queries and Data Parameters	127	(1)
Querying XML	127	(2)
Fields	129	(1)
Fields and XML	130	(1)
Filters	130	(1)
Adding a Data Source	131	(1)
Summary	132	(1)
Report Parameters	133	(8)
Setting Up Parameters	134	(1)
Parameter Properties	134	(1)
Data-Driven Parameters	135	(1)
Parameters and Data Sources	136	(1)
Expressions with Parameters	136	(1)
Dynamic SQL with Parameters	137	(1)
Parameter Dependencies	137	(1)
Example Using MultiSelect	138	(2)
Summary	140	(1)
Working with Report Items	141	(24)
Data Regions, Containers, and Independent Report Items	141	(2)
Report Designer's Toolbox	143	(1)
Line Report Item	144	(1)
Rectangle Report Item	144	(1)
Image Report Item	145	(3)
Textbox Report Item	148	(1)
Table Report Item	148	(3)
List Report Item	151	(2)
Practical Application of Report Items	153	(2)
Matrix Report Item	155	(3)
Chart Report Item	158	(4)
Chart Data (Value)	159	(1)
Chart Series Groups	160	(1)
Chart Category Groups	161	(1)
Chart's RDL	161	(1)
Practical Application of Chart and Matrix	162	(1)
Report Body Versus Page Header/Footer	163	(1)
Summary	163	(2)
Grouping, Sorting, and Aggregating Data, Working with Scope	165	(10)
Grouping Data on a Report	165	(3)
Sorting, Including Interactive User Sorting	168	(3)
Interactive Sorting	168	(2)
Data Source Sorting	170	(1)
Data Region and Group Sorting	170	(1)
Scope of an Aggregate Function	171	(1)
Level and InScope Functions	172	(2)
Summary	174	(1)
Advanced Report Formatting	175	(18)
Formatting-Related Report Item Properties	177	(2)
Formatting Numeric and Date/Time Values	179	(9)
Standard Numeric Format Strings	180	(2)
Custom Numeric Format Strings	182	(2)
Standard Date/Time Format Strings	184	(1)
Custom Date/Time Formatting	184	(4)
Creating Alternating Colors for the Lines on a Report	188	(1)
Paging Report	189	(1)
PageHeight and PageWidth	189	(1)
InteractiveHeight and InteractiveWidth	189	(1)
PageBreakAtEnd and PageBreakAtStart	189	(1)
Summary	190	(3)
Report Navigation	193	(12)
Hyperlink (Jump to URL) Navigation	194	(1)
BookmarkLink (Jump to Bookmark) Navigation	195	(1)
Document Map	196	(1)
Drillthrough (Jump to Report) Navigation	197	(1)
Hiding Items and Toggle Items	198	(1)
Practical Application of Action Items	198	(5)
Implementation	199	(4)
Summary	203	(2)
Working with Multidimensional Data Sources	205	(28)
Analysis Services Concepts	205	(3)
Data-Mining Concepts	208	(2)
Creating a Data Mining Model (DMM)	208	(2)
MDX and DMX	210	(1)
More In-Depth OLAP Concepts	210	(19)
Creating Data-Mining Reports	229	(3)
Summary	232	(1)
Ad Hoc Reporting New in 2005	233	(22)
Issues Facing Ad Hoc Reporting	233	(1)
Client-Side Reporting with SSRS	234	(1)
Report Models and the Model Designer	234	(18)
Report Model Projects	235	(1)
Model File Content	235	(11)
Features of Report Builder	246	(2)
Building Reports with Report Builder	248	(4)
Summary	252	(3)
PART III: Reporting Services Administration and Operations
Managing Reports, Data Sources, and Models	255	(20)
Deployment Options in Visual Studio	255	(3)
ServerName	255	(1)
Report Folder Location	256	(1)
Overwriting Data Sources	256	(1)
Target Folder for Data Source	256	(1)
Building and Deploying Reports	257	(1)
Deployment Through Report Manager	258	(4)
Creating a New Folder	258	(1)
Setting Up a Data Source	259	(1)
Uploading a Report	260	(1)
Changing the Data Source	261	(1)
Deploying Reports Through SQL Server Management Studio	262	(4)
Creating a New Folder	262	(2)
Setting Up a Data Source	264	(1)
Uploading a Report File	264	(2)
Changing Report Properties	266	(3)
Basic Properties	266	(1)
Moving Reports	267	(1)
Linked Reports	267	(2)
Setting Report History and Snapshots	269	(3)
My Reports	272	(2)
Enabling My Reports	273	(1)
Disabling My Reports	274	(1)
Summary	274	(1)
Securing Report Server Items	275	(16)
Reporting Services Security Model	275	(12)
What Can Be Secured?	276	(1)
How Role Assignments Work	277	(1)
Relationships Between Roles, Tasks, and Users	278	(1)
Overview of Built-In Roles	279	(2)
Assigning Built-In Roles	281	(6)
Defining Custom Roles	287	(3)
Creating/Modifying a Custom Role	287	(3)
Summary	290	(1)
Subscribing to Reports	291	(14)
Overview of Subscriptions	291	(2)
Parts of Subscriptions	291	(1)
Uses of Subscriptions	292	(1)
Standard Versus Data-Driven Subscriptions	292	(1)
Delivery Options	292	(1)
Subscription Processing	293	(1)
Overview of Delivery Extensions	293	(1)
Email	293	(1)
Delivering to a File Share	294	(1)
Creating a Simple Subscription	294	(5)
Creating a Data-Driven Subscription	299	(2)
Managing Subscriptions	301	(2)
My Subscriptions	302	(1)
Monitoring Subscriptions	303	(1)
Deleting/Inactivating Subscriptions	303	(1)
Summary	303	(2)
Report Execution and Processing	305	(10)
Managing Schedules	305	(3)
Types of Schedules	306	(1)
Creating/Modifying Schedules	306	(2)
Report Execution and Processing	308	(2)
Report Execution Timeouts	308	(1)
Running Processes	309	(1)
Large Reports	310	(1)
Report Execution History	310	(4)
What Is the Execution Log?	311	(1)
How to Report Off the Execution Log	311	(1)
Creating the RSExecutionLog Database	312	(1)
Overview of the Sample Reports	312	(2)
Summary	314	(1)
Deploying and Configuring SSRS	315	(16)
Overview of Deployment Scenarios	315	(2)
Advantages/Disadvantages of the Standard Model	316	(1)
Advantages/Disadvantages of the Scale-Out Model	317	(1)
Requirements for a Standard Deployment	317	(1)
Requirements for a Scale-Out Deployment	318	(3)
Overview of Report Server Initialization	318	(1)
Steps to Set Up SSRS in a Scale-Out Configuration	319	(2)
Configuring SSRS	321	(2)
Configurable Components	321	(1)
Configuration Files	322	(1)
Key Management	323	(4)
Backing Up the Symmetric Key	324	(1)
Restoring the Symmetric Key	325	(1)
Changing the Symmetric Key	326	(1)
Deleting the Symmetric Key	327	(1)
Summary	327	(4)
PART IV: Developing for Reporting Services
Implementing Custom Embedded Functions	331	(6)
Adding Embedded Code	331	(2)
Debugging Embedded Code	333	(2)
Summary	335	(2)
How to Create and Call a Custom Assembly from a Report	337	(22)
Strong-Named Custom Assemblies	344	(2)
.NET Security Primer for a SSRS Administrator	346	(4)
Assemblies That Require Other Than Execute Permissions	350	(3)
Debugging Custom Assemblies	353	(4)
Summary	357	(2)
How to Use URL Access	359	(10)
How to Integrate URL Access in an Application	365	(2)
Summary	367	(2)
How to Use Reporting Services Web Services	369	(14)
Report Management Web Service NEW in 2005 (ReportService2005.asmx)	375	(1)
How to Script Reporting Services (Using the RS Utility)	376	(2)
Working with Report Parameters	378	(1)
Security When Calling a Web Service	379	(2)
Some of the Commonly Used Methods with Short Code Snippets	381	(1)
Summary	382	(1)
Writing Custom Reporting Services Extensions	383	(12)
Common Considerations for Custom Reporting Services Extensions: Implementation, Deployment, and Security	386	(4)
Delivery Extension	390	(2)
Interactions Between User, SSRS, and a Delivery Extension	392	(2)
Summary	394	(1)
Report Rendering Controls and SharePoint Services Web Parts	395	(6)
Microsoft Visual Studio 2005 ReportViewer Web and Windows Controls NEW in 2005	395	(4)
Microsoft SharePoint Web Parts	399	(1)
Summary	400	(1)
Custom Report Definition Language (RDL) Generators and Customizing Report Definition	401	(24)
Summary	405	(4)
PART V: Appendices
A. References and Additional Reading	409	(2)
B. Glossary	411	(10)
C. FAQ	421	(4)
Index	425