Introducing SQL Server 2000 Reporting Services Geoff Snowman

Introducing SQL Server 2000 Reporting Services Geoff Snowman Developer Community Champion gsnowman@microsoft. com 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 Share. Point, 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 B 2 B 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 Report developers can create reports to be published to the Report Server using Microsoft or 3 rd 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 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 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 Report Server Output Formats (HTML, Excel, PDF, Custom) 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 Report Server Output Formats (HTML, Excel, PDF, Custom) 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 3 rd 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 Win 32 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]/Report. Server 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/Report. Server/Report. Name? Category. ID=1&Employee. ID=1 User Credentials (dsu: and dsp: ) Credentials for each report data set Example: https: //servername/Report. Server/Report. Name? dsu: Data. Set 1=My. User. Name& dsp: Data. Set 1=My. Password Can also use HTTPS POST Use Secure Protocols!

URL Parameter Directives Report Server Parameters (rs: ) Performance optimization avoids item type check Example: http: //servername/Report. Server/Report. Name? rs: Command=Rendering Device Info (rc: ) Name/Values pairs specifying information about the format being rendered to (i. e. , Find. String, Section, Zoom, etc. ) Unique per Rendering Extension Example: http: //servername/Report. Server/Report. Name? 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]/Report. Server/ 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 Basic Authentication System. Network. Credentials Dim rs As New My. Server. RSWeb. Service() ‘ Prompt user for credentials rs. Credentials = new System. Network. Credential ("user", "pwd", "domain") Integrated Authentication System. Net. Credential. Cache Dim rs As New My. Server. RSWeb. Service() rs. Credentials = System. Net. Credential. Cache. Default. Credentials

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.