Microsoft SQL Server Reporting Services

1
Microsoft SQL Server Reporting Services

• Doug Nelson
• Chief Architect
• SynApp north
• www.synappnorth.com

2
Discussion Outline

• Reporting Services Overview
• Creating Reports with Reporting Services
• Managing Reports
• Reporting Services Web Service
• Extending Reporting Services

3
Reporting Services Components
Web Browser
Report Designer
Client Utilities
Report Manager http//ltservergtreports
URL Access
SOAP Endpoint Reportservices.amx
WMI
Web Service (IIS / ASP.Net) http//ltservergtreports
erver
Win32 Service Delivery
Shared Components Data Retrieval Rendering
Security
SQL Server Database / SQL Server Agent
4
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.
5
Report Authoring Demo
6
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)

7
Management Tools

• Report Manager
• Web-based management application
• Built using ASP.net and Web Services
• Client Utilities
• Script Host rs.exe
• Server Configuration
• Encryption Key Management
• Custom Applications
• SQL Server 2005 Management Studio

8
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

9
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

10
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

11
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

12
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

13
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

14
Scheduling

• 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

15
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

16
Subscriptions

• 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

17
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.

18
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

19
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

20
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

21
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

22
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

23
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

24
Using Reporting Services with Applications
25
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

26
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

27
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)

28
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

29
URL Parameter Directives

• Report Parameters
• Parameter names must match report definition
• http//servername/ReportServer/ReportName?Category
  ID1
• User Credentials (dsu and dsp)
• Credentials for each report data set
• https//ltgt?dsuDataSet1MyUserNamedspDataSet1My
  Password
• Use Secure Protocols!

30
URL Parameter Directives

• Report Server Parameters ( rs )
• Name/Value pairs specifying server behavior
• Same for all Rendering Extensions
• httpltgt?rsClearSessionTrue
• 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
• httpltgt?rcSection7rc.HTMLFragmenttrue

31
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

32
Getting Started

• Add a Web Reference to your Visual Studio project
• http//servername/ReportServer/ReportService.asm
  x
• 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

33
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.Defaul
  tCredentials

34
Extending Reporting Services

• Security Extensions
• Delivery Extensions
• Rendering Extensions (Only for the very brave)
• Data Processing Extensions

35
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

36
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.

37
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

38
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 users unique identity
• IAuthorization interface
• Provides classes, enumerations, methods for
  authorizing the user
• Allows creating, verifying security descriptors
• Only available in Enterprise Edition

39
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

40
Introducing Data Processing Extensions
41
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.

42
SynApp Custom Dataset Extension

• These classes implement the necessary interfaces
• SynAppConnection IDbConnectionExtension/IDbConne
  ction
• SynAppCommand IDbCommand
• SynAppTransaction IDbTransaction
• SynAppDataParameter IDataParameter
• SynAppDataParameterCollection
  IDataParameterCollection
• SynAppDataReader IDataReader

Need to set a reference to Microsoft.ReportingServ
ices.Interfaces. This file is located in
C/Program Files/Microsoft SQL Server/MSSQL/Report
ing Services/ReportServer/bin/Microsoft.ReportingS
ervices.Interfaces.dll
43
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
• ltExtension Name"SynAppDS" Type"SynAppDataExtensi
  on.SynAppConnection, SynAppDataExtension"/gt
• Changes are also required to the rssvpolicy.config

ltCodeGroup class"UnionCodeGroup version"1"
PermissionSetName"FullTrust"
Name"SynAppDataExtension" Description"Code
group for my data processing extension"gt
ltIMembershipCondition class"UrlMembershipConditi
on version"1" Url"C\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\SynAppDataExtension.
dll" /gt lt/CodeGroupgt
44
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
• ltExtension Name"SynAppDS" Type"SynAppDataExtensi
  on.SynAppConnection, SynAppDataExtension"/gt
• Changes are also required to the
  rspreviewpolicy.config

ltCodeGroup class"UnionCodeGroup version"1"
PermissionSetName"FullTrust"
Name"SynAppDataExtension" Description"Code
group for my data processing extension"gt
ltIMembershipCondition class"UrlMembershipConditi
on version"1" Url"C\Program
Files\Microsoft SQL Server\80\Tools\Report
Designer\ReportServer\bin\SynAppDataExtension.d
ll" /gt lt/CodeGroupgt
45
Additional Resources

• Microsoft SQL Reporting Services
• www.microsoft.com/sql/reporting
• MSDN Reporting Services Developer Center
• msdn.microsoft.com/SQL/sqlwarehouse/ReportingServi
  ces
• Reporting Services Blogs
• blogs.msdn.com/tudortr
• blogs.sqlxml.org/bryantlikes
• odetocode.com
• Recommended Reading
• Microsoft Reporting Services in Action Teo
  Lachev
• Microsoft SQL Server 2000 Reporting Services
  Brian Larson
• Hitchhikers Guide to SQL Server 2000 Reporting
  Services Peter Blackburn William Vaughn

46
Questions?

• Presenter - Doug Nelson
• Company - SynApp north
• Web Site - www.synappnorth.com
• Email Address - dnelson_at_synappnorth.com