SQL Server 2005 Reporting Services:

1
SQL Server 2005 Reporting Services

• Pawas Goyal
• CYLSYS Technologies

2
Agenda

• Overview of Reporting Services
• Vision, Application, Lifecycle, Overview
• Understand Reporting Services Architecture
• Architecture, VS.NET, Creating Reports Simple,
  Chart based, Parameterized, Data Source Details
• Reporting Services Management
• WebServices, URL Addressing, Security, Exports,
  RDL, Extensions, Command line utilities

3
What is Microsoft SQL Server 2000 Reporting
Services?

• Server-based reporting platform
• Framework including services, tools, and APIs to
  implement, deploy, and manage reporting solutions
• Take advantage of existing SQL Server, Internet
  Information Services (IIS), and .NET Framework
  infrastructures
• Security
• Scalability
• Deployment
• Configuration

4
Product features overview (1 of 2)

• Server-based report storage, processing, and
  management
• Rich report designing features
• Supports a variety of data sources
• Desktop and Web-enabled reporting
• On-demand and scheduled processing of reports
• Report caching and history
• Role-based security
• Linked reports

5
Product features overview (2 of 2)

• Importing access reports
• Modular and extensible architecture
• Programming interfaces to enable the integration
  of reporting functionality in custom applications
• Feature and tool support for every phase in the
  reporting solution development life cycle
• Built-in diagnostics tracing and Performance
  Monitor counters

6
Agenda

• Overview of Reporting Services
• Vision, Application, Lifecycle, Overview
• Understand Reporting Services Architecture
• Architecture, VS.NET, Creating Reports Simple,
  Chart based, Parameterized, Data Source Details
• Reporting Services Management
• WebServices, URL Addressing, Security, Exports,
  RDL, Extensions, Command line utilities

7
Architecture and components
8
Report Execution lifecycle
Report Execution lifecycle
9
Server components

• The Report Server Web service
• The Report Server Windows service (scheduling
  and delivery processor)
• The Report Server catalog
• The Report Processor
• Rendering Extensions
• HTML 4.0, HTML 3.2, Excel, .pdf, .csv, image
  (TIFF), .pdf, XML, HTMLOWC, and Web archive
• Delivery extensions
• File share and e-mail

10
Designing reports process

• Defining report data sources and DataSets
• Defining the basic report layout
• Enhancing the report layout
• Using report expressions to control style and
  formatting
• Using report expressions to generate values for
  report Items
• Adding interactive features
• Previewing the report to verify functionality

11
Deploying reports to a report server

• Publishing report folders, reports, shared data
  sources, and resources to a report server
• Deployment options
• Deploying a report project from the Report
  Designer
• Manually creating report folders and uploading
  report items using the Report Manager
• Implementing and using administrative scripts to
  create report folders and deploy report items

12
Expressions

• Most properties take expressions
• Expressions use VisualBasic.NET syntax
• Values from the data sets
• Values of other items on the report
• Values of global properties and user properties
• Aggregates Sum, Avg, Count, Min, First, etc.
• Examples
• Fields!Name.Value
• Fields!First.Value " " Fields!Last .Value
• Sum(Fields!Sales.Value)
• IIF(Sum(Fields!Sales.Value)gt10, "green", "red")

13
Parameters

• Report parameters can be used in report
  expressions (including binding to query
  parameters)
• Set of valid values and default values can be
  static or query-based
• Parameters can be hierarchical
• Selection of parameter value can drive later
  valid values
• Accessed using global Parameters collection, e.g.
• IIF(Fields!Sales.Value gt Parameters!Goal.Value,
  PASS", FAIL")

14
Making Reports Interactive

• Actions can be added to report items
• Drill down within a report
• Drill through to a different report
• Bookmarks jump within a report
• Hyperlinks any web page
• Document map shows report structure

15
Code Libraries

• Assemblies can be added to report to provide
  user-defined functions
• Build any code in Visual Studio .NET Language
• Expressions can refer to functions

16
Importing Reports

• Microsoft Access XP only
• Charts and code-behind are not imported
• Redesign your top reports to use Reporting
  Services features, especially table and matrix

17
Agenda

• Overview of Reporting Services
• Vision, Application, Lifecycle, Overview
• Understand Reporting Services Architecture
• Architecture, VS.NET, Creating Reports Simple,
  Chart based, Parameterized, Data Source Details
• Reporting Services Management
• WebServices, URL Addressing, Security, Exports,
  RDL, Extensions, Command line utilities

18
Report Management
Report definitions, folders, and resources are
published and managed in a reporting web service
Managed reports can be executed either
on-demand or via schedule and are cached for
consistency and performance
Authoring
Management
Delivery
Online Access
Report Definition
ManagedReport
Delivery Channels
19
Configuring and managing a report server

• Report execution and caching
• Report history
• Data source connections
• Role-based security
• Subscriptions and delivery
• Managing schedules
• Configuring site-wide settings
• Tool support The Report Manager
• Note SQL Server Reporting Services configuration
  and management data is stored in
• Report Server catalog database.

20
The Report Manager (1 of 2)

• Web-based report server management and report
  access tool
• An ASP.NET Web application
• Uses the Reporting Services programmatic
  interfaces
• Default installation folder ltdrivegt\Program
  Files\Microsoft SQL Server\MSSQL\Reporting
  Services\Report Manager
• Access URL http//ltServerNamegt/Reports

21
The Report Manager browsing a report folder
22
The Report Manager viewing a report
23
Configuring report execution and caching
24
Configuring report history
25
Configuring data source connections
26
Site-wide settings
27
Role-based security system level
28
Role-based security item level
29
Defining item-level role-based security
30
Subscriptions and delivery (1 of 2)
31
Subscriptions and delivery (2 of 2)
32
Schedules

• Use cases
• Schedule report delivery for subscriptions
• Schedule report history generation
• Schedule snapshot updates
• Schedule cached report expirations
• Types
• Item specific
• Shared

33
Schedule definition UI
34
Accessing reports

• Viewing reports in Internet Explorer using the
  Report Manager
• Integrating report rendering in custom
  applications
• URL access
• Web service API
• Exporting reports to a desktop format
• Printing reports

35
URL AddressabilityVirtual Roots

• Report Server
• http//servername/ReportServer
• Web Service Interface
• Parameter directives
• Report Server Web Application
• http//servername/Reports
• SSL Support
• Fully localized
• Based on language in HTTP header

36
URL Parameter Directives

• Report Server Parameters (rs)
• Performance optimization avoids item type check
• Example
• http//servername/ReportServer/ReportName?rsCom
  mandRender
• Rendering Device Info (rc)
• Name/Values pairs specifying information about
  the format being rendered to (i.e., FindString,
  Section, Zoom, etc.)
• Unique per Rendering Extension
• Example
• http//servername/ReportServer/ReportName?rcSec
  tion7rcHTMLFragmenttrue

37
URL Parameter Directives

• Report Parameters
• Parameter names must match report definition
• Example
• http//servername/ReportServer/ReportName?Catego
  ryID1EmployeeID1
• User Credentials (dsu and dsp)
• Credentials for each report data set
• Example
• https//servername/ReportServer/ReportName?
• dsuDataSet1MyUserNamedspDataSet1MyPassword
• Can also use HTTPS POST
• Use Secure Protocols!

38
Reporting Services programming

• URL access
• Syntax http//server/virtualroot?/pathinfopre
  fixparam
• valueprefixparamvalue...n
• Web services API
• WSDL URL http//server/reportserver/ReportS
  ervice.asmx?WSDL
• Embedded code
• Custom assemblies
• Windows Management Instrumentation (WMI) provider

39
Report Delivery

• Single report definition can be rendered to
  diverse output formats
• Web Formats (HTML 4, HTML 3.2, HTML w/OWC)
• Print Formats (TIFF, RTF, PDF)
• Data Formats (Excel, XML, CSV)
• Personalized and administrator defined
  subscription and delivery
• Ability to select output formats per subscription
• Can deliver links as well as rendered reports
• Data-driven subscriptions for personal delivery
• Extensible delivery provider architecture
• E-mail, File System, Custom

40
Working from Command Line

• You can write scripts to administer and manage a
  Report Server
• rs utility executes the scripts
• Activate a Report Server and its Windows Service
• rsactivate allows to do so
• Configure the Report Server settings
• rsconfig utility
• Retrieve and apply the symmetric key used for
  encryption of passwords
• rskeymgmt utility

41
Resources

• Reporting Services Home - http//www.microsoft.com
  /sql/reporting/default.asp
• Technical Overview - http//www.microsoft.com/sql/
  reporting/techinfo/techoverview.asp
• CAS and Reporting Services - http//msdn.microsoft
  .com/data/default.aspx?pull/library/en-us/dnsql2k
  /html/dngrfCodeAccessSecurityInSQLServer2000Report
  ingServices.asp

42
Questions?

• You can ask now ?
• Email me at Pawas_Goyal_at_rediffmail.com

43
Thank You