Transcript DBA230: Introducing SQL Server 2000 Reporting Services

Introducing SQL Server 2000
Reporting Services
Geoff Snowman
Developer Community Champion
[email protected]
Microsoft Corporation
http://blogs.msdn.com/gsnowman
It has shipped!
Reporting Services Vision
Enable employees at all levels of an
organization to realize the promise of BI
to promote better decision making
Delivered through
Traditional and interactive reports
Scalable, manageable and embeddable
server infrastructure
Integration with SharePoint, Office applications, browser
and other familiar tools
Single platform and tools for all types of structured data
(relational, hierarchical, multidimensional)
Originally, a feature of Yukon…
…but Yukon’s late and Rosetta’s early!
Licensing Model
SQL Server
Scenarios
Enterprise Reporting
In-house reports (Sales, Finance, HR)
Manageability, portal and LOB integration
Embedded Reporting
Report processing included in applications
Extensible, embeddable architecture
B2B Reporting
Partner reporting via extranets, Internet
Data isolation, extensible security
User Profiles
Analysis Services
Analysts
Information
Explorers
5-10% of users
15-25% of users
Reporting Services
Information
Consumers
65-80% of users
Reporting Lifecycle
SQL Server Reporting Services supports the full
reporting lifecycle, including Authoring,
Management and Delivery
Authoring
Management
Delivery
Report Authoring
Authoring
Report developers can create reports to be published to
the Report Server using Microsoft or 3rd party design
tools that support the Reporting Services XML Report
Definition Language (RDL)
Authoring
Management
Delivery
Online
Access
Report
Definition
Managed
Report
Delivery
Channels
Report Management
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
Managed
Report
Delivery
Channels
Report Delivery
Delivery
Reporting Services supports both on-demand
(“pull”) and event-based (“push”) delivery of reports;
Users can view reports from a web-based front-end
or subscribe for reports to be delivered in e-mail
Authoring
Management
Delivery
Online
Access
Report
Definition
Managed
Report
Delivery
Channels
demo
Reporting Basics
Reporting Services Architecture
URL
WMI
Web Service
Report Server
SQL Server Catalog
Reporting Services Architecture
Data Sources
(SQL, OLE DB, ODBC,
Oracle, Custom)
URL
WMI
Web Service
Report Server
Report Processing
Data Retrieval
Rendering
SQL Server Catalog
Output Formats
(HTML, Excel,
PDF, Custom)
Reporting Services Architecture
Data Sources
(SQL, OLE DB, ODBC,
Oracle, Custom)
URL
WMI
Web Service
Output Formats
(HTML, Excel,
PDF, Custom)
Report Server
Report Processing
Security Services
(NT, Passport,
Custom)
Data Retrieval
Rendering
Security
Delivery
SQL Server Catalog
Delivery Targets
(E-mail, File share,
Custom)
Reporting Services Architecture
Browser
Data Sources
(SQL, OLE DB, ODBC,
Oracle, Custom)
Management
URL
Custom App
WMI
Web Service
Output Formats
(HTML, Excel,
PDF, Custom)
Report Server
Report Processing
Security Services
(NT, Passport,
Custom)
Data Retrieval
Rendering
Security
Delivery
SQL Server Catalog
Delivery Targets
(E-mail, File share,
Custom)
Report Authoring
Powerful XML Report Definition Language
Freeform / Table / Matrix / Chart
Parameters (Dynamic, Hierarchical)
Sorting / Filtering / Grouping / Aggregates
Interactivity (Drill-down, Drill-through)
Full VB.NET Expression Language
Extensible query execution architecture
SQL Server / Analysis Services
OLE DB / ODBC / Oracle
Report Designer
Integrated with Visual Studio .NET 2003
Published definition enables 3rd party tools
demo
Report Definition
Language
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
Report Management
Web Service APIs for management
Web and Win32 management user interfaces
Report Management Functions
Report Metadata (Name, Description, Connections,
Credentials, Parameters)
Scheduling integrated with SQL Server Agent
Execution properties (live, cache or snapshot)
History of prior executions
Extensible, role-based security architecture
demo
Report Management
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
URL Addressability
Virtual 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
URL Parameter Directives
Report Parameters
Parameter names must match report definition
Example:
http://servername/ReportServer/ReportName?
CategoryID=1&EmployeeID=1
User Credentials (dsu: and dsp:)
Credentials for each report data set
Example:
https://servername/ReportServer/ReportName?
dsu:DataSet1=MyUserName&
dsp:DataSet1=MyPassword
Can also use HTTPS POST
Use Secure Protocols!
URL Parameter Directives
Report Server Parameters (rs:)
Performance optimization avoids item type check
Example:
http://servername/ReportServer/ReportName?
rs:Command=Render
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?
rc:Section=7&rc:HTMLFragment=true
Web Service Interfaces
Namespace
Management
Item Properties
Report Execution
Report Parameters
Report History
Data Source
Management
Scheduling
Subscriptions and
Delivery
Linked Reports
Job Management
Security Management
Getting Started
Web Service Reference
http://[servername]/ReportServer/
Reportservice.asmx?wsdl
Proxy generated by Visual Studio .NET
Complex types defined in XSD
Synchronous and asynchronous support
Update operations may be batched
SOAP Headers
Session ID
Server Info
Batch ID
demo
Web Service WSDL
Getting Started
Web Service - Authentication
Authentication
Basic Authentication
System.Net.NetworkCredentials
Dim rs As New MyServer. RSWebService()
‘ Prompt user for credentials
rs.Credentials = new System.Net.NetworkCredential ("user", "pwd",
"domain")
Integrated Authentication
System.Net.CredentialCache
Dim rs As New MyServer. RSWebService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
WMI Configuration
Manage Report Server Configuration
Report Server Instance Name
Report Server and Report Server Web Application virtual
directory
Report Server Database Connectivity Information
WMI syntax supported
Per Property Gets
Puts (Create and Update)
Enumeration
Query
Configuration Only - No WMI events in first release
Extending the Report Server
Extensions provide a way to extend the Reporting
Services Platform
Managed code runs in server process
Published CLR interfaces
Extension Types
Data – Communicates to data sources and returns data
Delivery – Delivers reports over different protocols and to
different devices
Rendering – Renders to specific formats and devices
Security – Provides a means to authorize users to
custom authorization schemes
Partner Support
Cizer, Hitachi
Key Differentiators
Full Reporting Platform
All server components are extensible
Completely embeddable
Published, extensible report definition
Scalability and Performance
Designed from day one to be a .NET
web service
Web farm architecture
Optimized processing and rendering
Reports execute as CLR assemblies
Next Steps
Download the eval!
© 2003 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.