microsoft sql server reporting services l.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft SQL Server Reporting Services PowerPoint Presentation
Download Presentation
Microsoft SQL Server Reporting Services

Loading in 2 Seconds...

play fullscreen
1 / 46

Microsoft SQL Server Reporting Services - PowerPoint PPT Presentation

  • Uploaded on

Microsoft SQL Server Reporting Services. Doug Nelson Chief Architect SynApp north Discussion Outline. Reporting Services Overview Creating Reports with Reporting Services Managing Reports Reporting Services Web Service Extending Reporting Services.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Microsoft SQL Server Reporting Services

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft sql server reporting services

Microsoft SQL Server Reporting Services

Doug Nelson

Chief Architect

SynApp north

discussion outline
  • Reporting Services Overview
  • Creating Reports with Reporting Services
  • Managing Reports
  • Reporting Services Web Service
  • Extending Reporting Services
reporting services components
Reporting Services Components

Web Browser

Report Designer

Client Utilities

Report Manager



SOAP Endpoint



Web Service

(IIS / ASP.Net)


Win32 Service


Shared Components

Data Retrieval Rendering Security

SQL Server Database / SQL Server Agent

setup prerequisites
Setup Prerequisites

Setup UI assumes you are using the default web site and port and integrated security to access the database. If this is not the case, you will need to use command line setup or configure the server after the setup UI completes.

management apis
Management APIs
  • Web Service/ SOAP API
    • Full SOAP API Implementation with complex types
    • Includes WDSL
    • Add service reference to Visual Studio .NET
    • Supports SSL and scripting
  • WMI
    • Used for managing service configuration
    • Enumerate instances of Report Server
    • Works even if the Web Service is not available
    • No WMI events ( configuration only)
management tools
Management Tools
  • Report Manager
    • Web-based management application
    • Built using and Web Services
  • Client Utilities
    • Script Host – rs.exe
    • Server Configuration –
    • Encryption Key Management –
  • Custom Applications
  • SQL Server 2005 Management Studio
role based security model
Role-Based Security Model
  • Tasks
    • Sets of low level operations
    • Item level ( e.g. create report) or system level (manage jobs)
    • Not customizable
  • Roles
    • Sets of tasks
    • Default roles installed by default (browser, publisher)
    • Default roles can be customized, new ones created
  • Groups / Users
    • Windows NT / Active Directory
  • Role Assignments
    • Associates groups / users with roles
    • Inherited from the parent namespace
report properties
Report Properties
  • Metadata is extracted from report definition at publishing and maintained in the database
    • Name
    • Description
    • Hidden
  • Report Definition
  • Parameters
    • Prompt
    • Prompt String
    • Default Names
  • Data Source Information
data source management
Data Source Management
  • Administrator can set connection type and connection string after publishing
  • Credential Operations
    • Prompt for Windows or database credentials
    • Securely stored Windows or database credentials
    • Integrated Security (Requires Kerberos delegation)
    • None (uses report execution account)
  • Shared Data Sources
    • Connection and credential information stored as a secured object in the namespace
    • Single point of management for multiple reports
managing report execution
Managing Report Execution
  • Execution Sessions
    • Automatically created for each report execution
    • Keeps consistency between server round trips (images, paging, exporting)
    • Session timeout set in server configuration file
  • Cache Snapshots
    • On-demand reports can be cached between users
    • Cache index is based on parameter values
    • Cache valid for a specified time after execution or cleared on schedule
    • Limitations – User-specific expressions (User ID, Language), stored credentials
snapshots and history
Snapshots and History
  • Execution Snapshot
    • Report execution is scheduled, all users get the same data
    • Single instance of processed report
    • Limitations: No query parameters or user-specific expressions, stored credentials
  • History Snapshots
    • Multiple instances of report snapshots for archiving, auditing purposes
    • Stored independently of data source, report definition
    • System and report-specific retention policy
managing report execution13
Managing Report Execution
  • Configure cache and snapshots via Report Manager or SQL Management Studio
  • Set execution timeouts on a system-wide or per-report basis
  • Long running reports can be stopped manually
  • Report execution log enables analysis of server usage
    • Optionally, executions are logged to Report Server database
    • Includes report, format, user, start, end, cache hit, size
    • Setup includes DTS package and sample reports
  • Management events can be scheduled on the report server
    • Caching
    • History
    • Subscriptions
  • Schedules are stored in database and integrated with SQL Agent
    • When triggered, Agent adds entry to the queue
  • Scheduled events are queued in database and polled by NT Service
shared schedules
Shared Schedules
  • Managed shared schedules independently of reports, subscriptions or snapshots
  • Change shared schedule properties
    • Name
    • Days, times or frequencies
    • Start and end dates
  • Pause and resume shared schedule
  • Expire a shared schedule
  • Delete a shared schedule
  • Personal or administrator defined
  • Subscriptions triggered by an event ( schedule, snapshot creation, external)
  • Delivery extension (e-mail, file share) specifies how report is delivered
    • E-mail delivery requires an SMTP server
    • Extensible delivery architecture
  • Can specify output format (HTML, XLS)
    • Can deliver links as well as rendered reports
  • Two types of subscriptions
    • Standard
    • Data Driven
standard subscriptions
Standard Subscriptions
  • Single report sent to a fixed set of addresses
    • End user wants to customize their own report delivery
  • How it works
    • User creates a standing request to run a report at a specific time and delivered in a certain format
    • Can be triggered based on a schedule or snapshot generation
    • Specify report, execution conditions, parameters, rendering format, delivery location, etc.
data driven subscriptions
Data Driven Subscriptions
  • When to use
    • Delivery of a report to a dynamic list of destinations with customized content for each destination
  • How it works
    • Set up by the adminstrator
    • Define delivery queue to return list of destinations and parameters
    • Specify delivery settings and parameter values as a static or field from delivery query
    • Set to run according to a defined schedule or trigger from a snapshot
  • Use Null Delivery Provider to deliver reports to cache
automating repetitive tasks
Automating Repetitive Tasks
  • Examples
    • Duplicate settings between servers
    • Migrate from test to production environment
    • Change shared data sources
    • Cancel running jobs
  • Automate web service tasks through Report Server Script host (rs.exe)
    • Visual Studio .Net not required for execution
scripting support
Scripting Support
  • VB .Net Code File
    • Create with text editor or Visual Studio .Net
    • Generated in SQL Server 2005 Management Studio
  • Unicode or UTF-8 text file with a .rss file extension
  • Written using generated Web Service proxy
  • Scripts must have one function with declaration:
    • Public Sub Main()
  • Connection to the report server is made automatically by the script host
server configuration files
Server Configuration Files
  • Unique per Report Server – not transferable
    • Exclude from synchronization across web farm
  • Specific areas of interest
    • Report Server Database Connection
    • Report Execution account and password
    • Extension Configuration ( including e-mail delivery)
  • Use text editor to change ( file monitoring)
  • Use rsconfig or WMI provider for encrypted settings
  • Code Access Security (CAS) for extensions stored in separate file
logging and monitoring
Logging and Monitoring
  • Performance Monitoring
    • Counters for performance
  • Event Log Integration
    • Critical events and errors
  • Trace Events
    • Traces all server activities, response times, security events
data encryption
Data Encryption
  • When data source credentials are encrypted in the Report Server database
  • Stored with symmetric key tied to instance / machine / service account
    • Decrypted by both Web Service and NT Service
    • Shared by all machines in web farm
  • Update when machine name, instance name or service account changes
  • Manage keys with rskeymgmt
    • Extract a copy of the encryption key
    • Apply stored encryption key
    • Remove encrypted data on machine
reporting platform integration
Reporting Platform Integration
  • .Net-based solutions (non reporting) with reporting needs
  • Report Authoring and Publishing Tools
  • Delivery, Data and Rendering Extensions (Add-ons)
  • Server Management applications
  • Portals and Collaboration Applications
programmatic interfaces
Programmatic Interfaces
  • Customizable XML report definition
    • Open Schema
  • Viewing Interfaces
    • URL Addressability
    • Web Service / SOAP
  • Management Interfaces
    • Web Service SOAP
    • WMI Interface
  • Extension Interfaces
    • Data, Delivery, Rendering and Security
generating reports
Generating Reports
  • Application specific report authoring requirements
  • Reports are defined in Report Definition Language (RDL)
    • XML format
  • Use .Net XML classes to generate report definitions
    • Leverage RDL Schema Description (XSD)
url addressability
URL Addressability
  • Embed or link to reports via the Report Server URL
    • http://[servername]/ReportServer
  • Supports both GET and POST methods
  • Can return any output in any format
  • Optional HTML Viewer for parameter prompting and navigation
  • Fully localized – based on language in the HTTP header
url parameter directives
URL Parameter Directives
  • Report Parameters
    • Parameter names must match report definition
      • http://servername/ReportServer/ReportName?CategoryID=1
  • User Credentials (dsu: and dsp:)
    • Credentials for each report data set


Use Secure Protocols!

url parameter directives30
URL Parameter Directives
  • Report Server Parameters ( rs: )
    • Name/Value pairs specifying server behavior
    • Same for all Rendering Extensions
    • http:<>?rs:ClearSession=True
  • Rendering Device Info ( rc: )
    • Name/Value pairs specifying behavior specific to the format being rendered to (i.e., FindString, Section, Zoom, etc.)
    • Unique per Rendering Extension
    • http:<>?rc:Section=7&rc.HTMLFragment=true
web service interface
Web Service Interface
  • Namespace Management
  • Scheduling, Subscriptions and Delivery
  • Report Execution, Report History, Linked Reports
  • Item Properties, Job Management, Security Management
  • Report Parameters, Datasource Management
getting started
Getting Started
  • Add a Web Reference to your Visual Studio project
    • http://[servername]/ReportServer/ReportService.asmx
  • Proxy classes are generated by Visual Studio
    • Complex types defined in WSDL
    • Synchronous and asynchronous support
    • Update operations may be batched
  • SOAP Headers
    • Session ID
    • Server Info
    • Batch ID
web service authentication
Web Service Authentication
  • Basic Authentication
    • System.Net.NetworkCredentials
    • ReportServer.RSWebSerive rs = new ReportServer.RSWebService();
    • rs.Credentials = new System.Net.NetworkCredential(“user”,”pwd”,”domain”);
  • Integrated Authenication
    • System.Net.CredentialCache
    • rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
extending reporting services
Extending Reporting Services
  • Security Extensions
  • Delivery Extensions
  • Rendering Extensions(Only for the very brave)
  • Data Processing Extensions
server extensibility
Server Extensibility
  • Extensions provide a way to extend the Reporting Services platform
    • Managed code runs in server process
    • Published CLR interfaces
  • Extension Types
    • Data Extensions – Communicates to data sources and returns data
    • Delivery Extensions – Delivers reports over different protocols and to different devices
    • Rendering Extensions – Renders to specific formats and devices
    • Security Extensions – Authenticates and authorizes non-Windows users
  • Extending should be seen as a last resort
rendering extensions
Rendering Extensions
  • Input: A processed report object model
  • Output: format-specific output stream
  • IRenderingExtension interface
    • Render method responsible for primary output stream
    • RenderStream method responsible for returning ancillary streams (images, etc)
    • GetRenderingResource may be used to provide non-report specific content
  • A rendering extensions are very difficult to write and maintain – consider using Xml output instead.
delivery extensions
Delivery Extensions
  • Input: event and specified destinations
  • Output: delivered reports or notifications
  • IDeliveryExtension interface
    • Deliver method delivers an input notification to a destination
    • ValidateUserData method verifies a set of delivery information is valid
    • ExtensionSettings property allows delivery extensions to be self describing
  • Can also provide UI to integrate with Report Manager
security extensions
Security Extensions
  • Input: user credentials, security policies
  • Output: authenticate user credentials and authorize server operations
  • IAuthentication interface
    • LogonUser method authenticates the user
    • GetUserInfo retrieves a user’s unique identity
  • IAuthorization interface
    • Provides classes, enumerations, methods for authorizing the user
    • Allows creating, verifying security descriptors
  • Only available in Enterprise Edition
data extensions
Data Extensions
  • Input: Connection information, query with optional parameter support
  • Output: DataReader
  • Subset of the .Net Managed Data Provider Interface (System.Data)
    • Any .Net managed provider will work
    • Required: IDbConnection, IDbCommand, IDbDataParameter, IDataReader
    • Optional: Extended Data Extension interfaces
  • Generic query designer in Report Designer
data extension process flow
Data Extension Process Flow
  • The report server creates a connection object and passes in the connection string and credentials associated with the report.
  • The command text of the report is used to create a command object. In the process, the data processing extension may include code that parses the command text and creates any parameters for the command.
  • Once the command object and any parameters are processed, a data reader is generated that returns a result set and enables the report server to associate the report data with the report layout.
synapp custom dataset extension
SynApp Custom Dataset Extension
  • These classes implement the necessary interfaces
    • SynAppConnection : IDbConnectionExtension/IDbConnection
    • SynAppCommand : IDbCommand
    • SynAppTransaction : IDbTransaction
    • SynAppDataParameter : IDataParameter
    • SynAppDataParameterCollection : IDataParameterCollection
    • SynAppDataReader : IDataReader

Need to set a reference to Microsoft.ReportingServices.Interfaces.

This file is located in C:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/ReportServer/bin/Microsoft.ReportingServices.Interfaces.dll

deploying synappdataextension
Deploying SynAppDataExtension
  • Deploying Data Extension to a Report Server
    • Copy assembly to bin directory of the Report Server
    • Changes required in the RSReportServer.config file
    • Find the existing data extensions and add the new extension
    • <Extension Name="SynAppDS" Type="SynAppDataExtension.SynAppConnection, SynAppDataExtension"/>
    • Changes are also required to the rssvpolicy.config

<CodeGroup class="UnionCodeGroup” version="1"



Description="Code group for my data processing extension">

<IMembershipCondition class="UrlMembershipCondition“


Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\SynAppDataExtension.dll"



deploying synappdataextension44
Deploying SynAppDataExtension
  • Deploying Data Extension to the Report Designer
    • Copy assembly to Report Designer folder
    • Changes required in the RSReportDesigner.config file
    • Find the existing data extensions and add the new extension
    • <Extension Name="SynAppDS" Type="SynAppDataExtension.SynAppConnection, SynAppDataExtension"/>
    • Changes are also required to the rspreviewpolicy.config

<CodeGroup class="UnionCodeGroup” version="1"



Description="Code group for my data processing extension">

<IMembershipCondition class="UrlMembershipCondition“


Url="C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportServer\bin\SynAppDataExtension.dll"



additional resources
Additional Resources
  • Microsoft SQL Reporting Services
  • MSDN Reporting Services Developer Center
  • Reporting Services Blogs
  • Recommended Reading
    • Microsoft Reporting Services in Action – Teo Lachev
    • Microsoft SQL Server 2000 Reporting Services – Brian Larson
    • Hitchhiker’s Guide to SQL Server 2000 Reporting Services – Peter Blackburn William Vaughn
  • Presenter - Doug Nelson
  • Company - SynApp north
  • Web Site -
  • Email Address -