Introduction to SQL Server2000 Reporting Services

Jeff DumasTechnical Specialist

Microsoft Corporationjeffd@microsoft.com

Business Reporting NeedsReports Are How We Make

Business Decisions Everyday

IT NeedsAsked to Build More Custom Reports

with Less Time and Resources

Problems:-- Multiple reports-- Multiple data sources-- Different user needs-- IT in the middle

SQL Server Reporting Services

Solution:-- IT is a resource, not a middle man-- Users have direct access

to custom reports

Connects Users to Reports. Frees up IT Department.

Reporting Services Vision

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)

Enable employees at all levels of Enable employees at all levels of an organization to realize the an organization to realize the

promise of BI to promote better promise of BI to promote better decision makingdecision making

User Profiles

Analysts

Information Consumers

Information Explorers

5-10% of users

15-25% of users

65-80% of users

Reporting Services

Analytical and VisualizationTools

Reporting LifecycleSQL Server Reporting Services

provides a single platform for authoring, management and delivery of reports.

Architecture

Report Authoring Use Microsoft or 3rd party tools that support RDL Create single reports from multiple data sources

(Access, DB2, OLE DB, ODBC, Oracle, and .NET data providers)

Designer lets you preview report layout & graphical support Publish reports to Report Server Exposed RDL allows partners to leverage

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 Scalable & Extensible Web Services architecture

Report Delivery On-demand (“pull”) or event-based (“push”) delivery of reports Tight integration with Microsoft Office System Choose from multiple formats (html, Word, PDF, XML) Deliver reports to almost any device

Report Delivery

Report Delivery• Intermediate format for rendering reports to

diverse output formats – Web Formats (HTML 4 and 3.2)– Print Formats (TIFF, 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, SharePoint

Report Authoring

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 VS.NET– Query designers for SQL, Analysis Services

• Published definition enables 3rd party tools

Report Management

• Web Service APIs for management • Report Manager web 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

Office Integration

• Import reports from Microsoft Access• Office Document Formats

– Excel, Word• HTML rendering takes advantage of OWC

– Interactive Charts, PivotTables, Spreadsheets• SharePoint Team Services

– Report Web Parts– Report delivery to document libraries, lists– Report Library Template

• SharePoint Portal Server– Protocol handler for search– SSO, Profile integration

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

• Support for Analysis Services– Takes advantage of server aggregates,

formatting

Less customization work for IT

Improved developer productivity Plug in Web Services Familiar development environment Graphical VS .NET tools Easy to embed into BI & LOB apps

More control for end users Users can subscribe to reports Choose report formats from their desktop Familiar, easy-to-use environment using

Web browser and Office System applications

© 2002 Microsoft Corporation. All rights reserved.© 2002 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.in this summary.