Transcript SQL Server 2005 Reporting Services

SQL Server 2005
Reporting Services
Hamsa Gayatri
Palepu
Introduction
• SQL Server 2005 Reporting Services is a
key component of SQL Server 2005.
• Reporting Services was first released with
SQL Server 2000 and provided customers
with an enterprise-capable reporting
platform with a comprehensive
environment for authoring, managing, and
delivering reports to the entire
organization.
• Reporting Services in SQL Server 2005 provides
additional enterprise reporting capabilities and
addresses a new audience—business users
who want to interact with data in an ad
hoc fashion as well as create their own reports
from scratch and to share them with others.
• In Reporting Services, the requirements of
different types of users who want to interact with
reports can, for the first time, be addressed with
one reporting solution. This document describes
the new capabilities in SQL Server 2005
Reporting Services.
What is SQL Server Reporting
Services?
• Reporting platform for both traditional and
interactive reports
• Scalable and manageable server
infrastructure
• Integrated with SharePoint, Office
applications, browser and other familiar
tools
• Single platform and tools for all types of
structured data (relational, hierarchical,
multidimensional)
Report Server Architecture
Browser
Data Sources
(SQL, OLE DB, XML/A,
ODBC, Oracle, Custom)
Custom Application
Office
URL / XML Web Service Interface
Security Services
(Windows,
Custom)
Report Server
Report Processing
Output Formats
(HTML, Excel, PDF, Custom)
Data Processing
Security
Rendering
Delivery
SQL Server Catalog
Delivery Targets
(E-mail, File Share,
Custom)
Introducing SQL Server
2005 Packaging
.
Express
Workgroup
Standard
Fastest way for
developers to learn,
build & deploy simple
data driven
applications
Easiest to use & most
affordable database
solution for smaller
departments &
growing businesses
Complete data
management &
analysis platform for
medium businesses
and large departments
Enterprise
Fully integrated data
management and
analysis platform for
business critical
enterprise applications
1 CPU
1 GB RAM
4GB DB Size
2 CPU
4 CPU
3 GB RAM
Simple
Management Tool
Management
Studio
Unlimited RAM
(64-bit)
Database Mirroring
Adv. DB mirroring,
Complete online &
parallel operations,
Report Wizard &
Report Controls
Import/Export
OLAP Server
DB snapshot
Limited Replication
Publishing
Reporting Server
Advanced Analysis
Tools including full
OLAP & Data
Mining
Replication &
SSB Client
Back-up Logshipping
New Integration
Services
Data Mining
Full Replication &
SSB Publishing
Unlimited Scale
+ Partitioning
Report Builder +
Customized & High
Scale Reporting
Adv SSIS
SQL Server BI Platform
Analysis Services
OLAP & Data Mining
Integration
Services
ETL
SQL Server
Relational Engine
Management Tools
Development Tools
Reporting Services
Reporting Lifecycle
SQL Server Reporting Services supports the full
reporting lifecycle, including Authoring,
Management and Delivery
Authoring
Management
Delivery
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 can be cached for consistency and
performance
Scalable & Extensible server architecture
Report Delivery


Traditional (paper) and interactive (web) reports
On-demand (“pull”) or event-based (“push”) delivery
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
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)
 Deliver reports to many devices (e-mail, file share, etc.)
Rich Client Printing
• Direct routing of print
jobs without needing to
export
• Small ActiveX control
downloaded via
browser
• Select page range,
orientation, margins
• Preview page output
• Server enhancements
for performance
Enhanced Expression Editor
•
•
•
•
•
Available for all
expressions in VS
Report Designer
List of available
VB.NET functions
Inline parameter
information
Statement
completion
Real-time syntax
checking
Multi-Valued Parameters
•
•
•
•
Report developer can
specify any parameter
is multi-valued
Report processing
constructs SQL or
MDX syntax
Multiple default values
also supported
Value sets can passed
via URL or SOAP API
Date Picker Parameters
•
•
•
Calendar
control for
choosing
dates
Localized
and
Globalized
Windows
and Web
support
Interactive Sort
•
•
•
Report author can add
ability for end user to
resort data within data
regions
Resorting happens
without re-querying the
database
Support for multi
column and for nested
/ grouped sort
Floating Headers
•
•
•
•
•
New type of interactivity similar to freeze panes in
Excel
Works Horizontally and Vertically
Table header
Matrix column and row headers
Table columns
• Must be continuous columns anchored on the right or left
MDX Query Builder
•
•
•
•
MDX and data
mining query
builders
MDX parameter
support
Member extended
properties
Support for server
aggregates
Custom Report Item
•
•
•
•
•
•
Enables Developers to add custom visualizations to a report
Primary scenario is enhanced charting
–
–
–
Additional chart types
Maps
Gauges
Supports interactivity
–
–
Actions
Sort
Supports all data region features
–
–
–
–
Filter
Grouping
Group Filters
Etc…
The Report Designer supports
a conversion API
Currently limited to images
–
Future plans for all report item types
Advantages
•
•
•
•
Increased Productivity
Integrated End-to-End Feature Set
Delivering BI with Fewer Resources
Reliable and Available Environment for
Critical BI
•
Resources
http://www.microsoft.com/technet/prodtec
hnol/sql/2005/2005ssrs.mspx
• http://www.microsoft.com/sqlserver/2005/e
n/us/white-papers.aspx
• http://download.microsoft.com/download/a
/4/7/a47b7b0e-976d-4f49-b15df02ade638ebe/bi_business_value.pdf
• Pubs and NorthWind
http://go.microsoft.com/fwlink/?LinkId=30196
END