You are previewing Microsoft® SQL Server™ 2005 Reporting Services Step by Step.
O'Reilly logo
Microsoft® SQL Server™ 2005 Reporting Services Step by Step

Book Description

Your hands-on guide to learning Microsoft SQL Server Reporting Services, Microsoft’s customizable reporting solution for business data analysis. Reporting Services is a powerful tool for business intelligence, so an understanding of the essentials—how to architect a report, as well as how to install and program Reporting Services—is key to harnessing the full benefits of SQL Server. This step-by-step tutorial shows you how to get started, how to use the report project wizard, how to think about and access data, and how to build queries. It also walks you through the creation of charts and visual layouts to enable maximum visual understanding of the data analysis. Interactivity features (enhanced in SQL Server 2005) and security are also covered in detail. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Whether you’re a beginning programmer or new to this version of the technology, you’ll understand the fundamental techniques for using Reporting Services. A companion CD includes data sets and sample code.

Table of Contents

  1. Microsoft® SQL Server™ 2005 Reporting Services: Step by Step
  2. A Note Regarding Supplemental Files
  3. Introduction
    1. Finding Your Best Starting Point
    2. About the Companion CD-ROM
    3. System Requirements
    4. Installing and Using the Sample Files
    5. Conventions and Features in This Book
  4. I. Getting Started with Reporting Services
    1. 1. Understanding Reporting
      1. Reporting Scenarios
      2. Reporting User Communities
      3. The Enterprise Reporting Life Cycle
        1. Authoring
        2. Management
        3. Access and Delivery
      4. Reporting Services Components
        1. Authoring Components
          1. Report Designer
          2. Report Builder
          3. Model Designer
          4. Programmatic Interface for Authoring
        2. Management Components
          1. Reporting Services Configuration Tool
          2. Report Manager
          3. Microsoft SQL Server Management Studio
          4. Command-Line Utilities
          5. Programmatic Interface for Management
        3. Access and Delivery Components
          1. Report Manager
          2. Processor Components
            1. Report Processor
            2. Scheduling and Delivery Processor
          3. Server Extensions
            1. Authentication Extensions
            2. Data Processing Extensions
            3. Report Processing Extensions
            4. Rendering Extensions
            5. Delivery Extensions
          4. ReportServer Databases
          5. Programmatic Interface for Access and Delivery
      5. 1. Quick Reference
    2. 2. Installing Reporting Services
      1. Considering Deployment Options
        1. Choosing a Reporting Services Edition
        2. Planning a Site Configuration
        3. Deciding Naming Conventions
      2. Preparing for Installation
        1. Reviewing Operating System Requirements
        2. Reviewing Software Requirements
        3. Reviewing Configuration Prerequisites
        4. Creating Reporting Services Credentials
      3. Installing Reporting Services
        1. Launching Setup
        2. Choosing the Components
        3. Selecting the Service Account
        4. Using the Reporting Services Configuration Manager
        5. Configuring Virtual Directories
        6. Specifying the Report Server Database
        7. Specifying Report Delivery Options
        8. Verifying the Installation
      4. 2. Quick Reference
    3. 3. Building Your First Report
      1. Authoring a Report
        1. Starting a New Report
        2. Connecting to a Data Source
        3. Getting Data for the Report
        4. Structuring Data in the Report
        5. Placing Data in the Report Structure
        6. Applying a Style Template
        7. Finishing the Report Wizard
        8. Checking the Report Layout
        9. Correcting Report Layout Issues
        10. Publishing a Report
      2. Managing a Report
        1. Reviewing Report Properties
        2. Changing Report Properties
        3. Reviewing Execution Properties
        4. Changing Data Sources Properties
        5. Changing Execution Properties
      3. Accessing a Report
        1. Displaying a Report
        2. Searching a Report
        3. Exporting a Report
      4. 3. Quick Reference
  5. II. Authoring Reports
    1. 4. Developing Basic Reports
      1. Understanding a Report Definition File
      2. Preparing a Report Using Report Designer
        1. Creating a New Report Project
        2. Creating a New Report
        3. Connecting to a Data Source
        4. Working with Datasets
      3. Structuring a Report Using Report Designer
        1. Adding Items from the Toolbox
        2. Working with Table Rows
        3. Sorting Table Rows
        4. Grouping Data in a Table
        5. Adding Group Headers
        6. Computing Group Subtotals
      4. Formatting a Report Using Report Designer
        1. Setting the Format Property
        2. Applying Styles
        3. Editing Properties
        4. Adding Floating Headers
        5. Triggering Page Breaks
        6. Adding a Textbox
        7. Setting Table Properties
        8. Working with Page Headers
        9. Working with Page Footers
        10. Adding Graphical Elements
        11. Adding Images
      5. 4. Quick Reference
    2. 5. Working with Expressions
      1. Using Expressions to Calculate Values
        1. Creating Calculated Fields
        2. Using Global Variables
        3. Using the ReportItems Collection
      2. Using Aggregate Functions
        1. Using Aggregate Functions in a Table
        2. Using Aggregate Functions in a Textbox
      3. Using Expressions to Change an Object’s Behavior
        1. Using Conditional Formatting
        2. Sorting
      4. 5. Quick Reference
    3. 6. Organizing Data in Reports
      1. Understanding Data Regions
        1. Comparing Types of Data Regions
        2. Using Data Regions
      2. Using a Matrix
        1. Adding a Matrix Data Region
        2. Grouping Rows
        3. Sorting Rows
        4. Grouping Columns
        5. Using Subtotals in a Matrix
        6. Using Static Rows and Columns in a Matrix
      3. Using a Chart
        1. Adding a Chart
        2. Adding Values and Column Groups to a Chart
        3. Grouping Data by Series
        4. Adding a Chart Legend
        5. Setting Chart Properties
      4. Using a List
        1. Adding a List
        2. Grouping and Sorting a List
        3. Using Fields in a List
        4. Nesting Data Regions
      5. 6. Quick Reference
    4. 7. Building Advanced Reports
      1. Using Parameters to Change Report Data
        1. Adding a Report Parameter
        2. Using the Parameters Collection
        3. Adding a Query Parameter
        4. Supplying Values for a Query Parameter
        5. Creating a Report Parameter for a Filter
        6. Adding a Filter
        7. Adding a Parameter Value to a Report
      2. Linking Information with Interactive Features
        1. Adding Actions
        2. Using the Hidden Property
        3. Using the ToggleItem Property
        4. Adding a Subreport
      3. Working with Hierarchical Data
        1. Displaying a Recursive Hierarchy in a Data Region
        2. Using the Level Function
        3. Using the Recursive Keyword
        4. Creating an Analysis Services Data Source and Dataset
        5. Using an Analysis Services Dataset
      4. 7. Quick Reference
    5. 8. Building Report Models
      1. Understanding Report Models
      2. Creating a Report Model
        1. Adding a Report Model Data Source
        2. Adding a Data Source View
        3. Adding a Report Model
        4. Reviewing a Report Model
      3. Modifying a Report Model
        1. Changing Model Objects
        2. Organizing Model Objects
        3. Adding Source Fields
        4. Adding Expressions
      4. Managing a Report Model
        1. Publishing a Report Model
        2. Securing a Report Model
      5. 8. Quick Reference
  6. III. Managing the Report Server
    1. 9. Managing Content
      1. Publishing Reports
        1. Defining Deployment Properties by Project
        2. Deploying Reports
        3. Uploading Reports
        4. Creating a Script File
        5. Using the rs Utility
      2. Organizing Content
        1. Working with Folders
        2. Moving Content
        3. Linking Reports
        4. Working with General Properties
      3. Using Properties to Manage Report Content
        1. Working with Data Sources
        2. Specifying a Default Value
        3. Disabling a Parameter Prompt
      4. Managing Report Execution
        1. Executing Reports with Current Data
        2. Implementing Cached Instances
        3. Working with Shared Schedules
        4. Managing Snapshots
        5. Using Report History
      5. 9. Quick Reference
    2. 10. Managing Security
      1. Using Report Server Security
        1. Adding Role Assignments
        2. Applying Item Security
        3. Applying System Security
      2. Applying Data Security
        1. Using Roles and Parameters to Restrict Data
        2. Restricting the Source Query by User
        3. Filtering the Report Data by User
      3. 10. Quick Reference
    3. 11. Managing Server Components
      1. Configuring Reporting Services
        1. Editing the rsreportserver.config File
        2. Changing Encrypted Configuration Information Using the rsconfig Utility
        3. Configuring Tracing on the Report Server
      2. Managing the Report Server
        1. How to Monitor Performance
        2. Managing Execution Logging
        3. Initializing an Execution Log Database
        4. Using an Execution Log Report
        5. Adding Current Data to the Execution Log
        6. Applying Time-outs to Source Queries
        7. Applying Time-outs to Report Execution
        8. Canceling Jobs
        9. Disabling a Shared Data Source
      3. Administering Reporting Services Databases
        1. Monitoring Database Storage Consumption
        2. Implementing a Backup and Restore Strategy
      4. 11. Quick Reference
  7. IV. Delivering Reports
    1. 12. Accessing Reports
      1. Finding and Viewing Reports
        1. Navigating the Folder Hierarchy
        2. Refreshing Reports
        3. Searching for Reports
        4. Using the HTML Viewer
        5. Using My Reports
      2. Saving Reports for Future Reference
        1. Creating a Report History Snapshot
        2. Saving Reports to Local Files
        3. Printing Reports
      3. 12. Quick Reference
    2. 13. Rendering Reports
      1. Comparing Rendering Formats
      2. Rendering for Online Viewing
        1. Rendering as MHTML
      3. Rendering for Document Management
        1. Rendering as a TIFF File
        2. Rendering as a PDF File
      4. Rendering for Data Exchange
        1. Rendering as an Excel File
        2. Rendering a Report as a CSV File
        3. Rendering a Report as an XML File
      5. 13. Quick Reference
    3. 14. Managing Subscriptions
      1. Creating a Standard Subscription
        1. Delivering a Report by E-Mail
        2. Delivering a Report to a File Share
      2. Creating a Data-Driven Subscription
        1. Creating a Subscription Delivery Table
        2. Creating a Data-Driven Subscription
      3. Managing Subscriptions
        1. Using the My Subscriptions Page
        2. Deleting Subscriptions
      4. 14. Quick Reference
    4. 15. Creating Reports with Report Builder
      1. Building Basic Reports
        1. Getting Started with Report Builder
        2. Building a Report
        3. Building a Matrix Report
        4. Using Clickthrough
        5. Building a Chart Report
      2. Enhancing Reports
        1. Formatting a Report
        2. Filtering a Report
        3. Adding Expressions
      3. 15. Quick Reference
  8. V. Programming Reporting Services
    1. 16. Report Authoring with Custom Development
      1. Using Custom Code
        1. Adding Custom Code to a Report
        2. Accessing Functions Using the Code Global Member
        3. Creating a Custom Class Library
        4. Using Functions from a Custom Assembly
      2. Creating Custom Data Processing Extensions
        1. Assigning a Namespace
        2. Creating a Connection Object
        3. Creating a Command Object
        4. Creating a Data Reader Object
        5. Deploying a Custom Data Processing Extension
      3. Generating Report Definition Language
        1. Creating a Console Application
        2. Adding a Data Source Connection
        3. Generating a Fields List
        4. Generating the RDL
      4. 16. Quick Reference
    2. 17. Programming Report Server Management
      1. Understanding Web Services
        1. Using the rs Utility
        2. Querying Report Server
      2. Using a Custom Application to Manage Reporting Services
        1. Querying Report Server
        2. Managing Reports
      3. Using the Reporting Services WMI Provider
        1. Querying a Report Server
      4. 17. Quick Reference
    3. 18. Building Custom Reporting Tools
      1. Using URLs
        1. How to Use URLs to Access Reports
        2. Viewing Reports
        3. Working with Report Parameters
        4. Using URL Access Parameters
      2. Using the Web Service
        1. Rendering a Report
        2. Authenticating Users
        3. Using Report Parameters in a Control
        4. Enabling Multiselect for Parameters
      3. 18. Quick Reference
  9. Glossary
  10. About the Authors
  11. Contributing Authors
  12. Index
  13. About the Authors
  14. Copyright