Transcript SQL Server 2005 Reporting Services

Microsoft SQL Server Reporting
Services
Eric Nelson
Application Architect
Microsoft
http://blogs.msdn.com/ericnel (SQL Dev and UK ISV)
Reporting Services 101
SQL Server BI Platform
Analysis Services
OLAP & Data Mining
Integration
Services
ETL
SQL Server
Relational Engine
Management Tools
Development Tools
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)
Reporting Lifecycle
Reporting Services is an open and extensible platform
supporting the authoring, management and delivery of rich,
interactive reports to the entire enterprise.
Authoring
Management
Delivery
Report Authoring
Reports are defined in Report Definition Language (RDL), a
documented XML schema
Use Microsoft or 3rd party tools that support RDL
Create single reports from multiple data sources
(SQL, OLE DB, ODBC, Oracle, and .NET data providers)
More info on the RDL spec: www.microsoft.com/sql/reporting
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
 Choose from multiple formats (HTML, Excel, PDF, XML)
 Deliver reports to many devices (e-mail, file share, etc.)
Release Roadmap
SQL Server 2000 Reporting Services SP1
Bug Fixes, Scalability, Excel 2000 support
SQL Server 2005 Beta 2
Parity with SQL2K SP1 version
Cross SQL Server (AS, Management) integration
SQL Server 2000 Reporting Services SP2
Web Parts, Client Printing
SQL Server 2005 April, June, Sept CTP
New Features, 64 Bit support, Report Builder
SQL Server 2005 RTM – very soon…
What is new in SQL Server
2005 Reporting Services
Themes
Core Product Enhancements
Extend features based on feedback from SQL2K version
Incremental enhancements, performance and scalability
Tighter Integration
Strengthen SQL Server end-to-end BI scenarios
Tighter integration with Office and SharePoint
Developer Focus
Integration for the Visual Studio developer
Freely redistributable Windows and ASP.NET controls
Empowering End Users
Report Builder enables end-user reporting scenarios
Increased interactivity
What’s new in 2005
Printing from IE (SP2)
Expression builder
enhancements
Multi-valued
parameters
Parameter Date Picker
Interactive Sort
Floating headers
MDX Query builder
Custom Report Item
Management Studio
Integration
Configuration Tool
Web Parts (SP2)
VS Integration
VS Controls
64 bit Support
Multi-instance Support
Report Builder
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 ISVs and 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
Management Studio Integration
Single point of
management for all
SQL Server
components
Superset of Report
Manager functions
Script generation
from property
dialogs
Server Configuration Tool
Graphical
configuration tool for
Report Server
Database Settings
Virtual Directories
E-Mail
Key Management
SharePoint Web Parts
Report Explorer
provides browsing
of server
namespace and
subscription
Report Viewer used
to view reports
Parts can be
connected or used
standalone
Works in both SPS
and WSS
Visual Studio Integration
Report design
completely
integrated with
Visual Studio
language projects
Natural extension
of VS data
functionality
Included in VS Pro
and above
Report Controls
Controls make it
easy to embed
reporting
functionality into
applications
Windows Forms (rich
client) and Web
Forms (ASP.NET)
control
Local processing
mode (no server) or
connected server
mode
Items Not in 2005 – sorry 
Rich Text support
RTF/Doc rendering
PPT rendering
Shared Styles (Styles sheets)
Templates
Font embedding in PDF
Many Chart enhancements
Heterogeneous Join
Edit in Preview
And….. Report Builder
Report Builder
A new ad-hoc report design tool for Reporting
Services
Targeted at business users who want to find and
share answers to interesting questions
Driven from a business model of the data so users
do not need to understand the underlying data
structures
Fully integrated with Reporting Services and
delivered in SQL Server 2005
Why Report Builder?
Report
Consumers
Report Viewer
Business
Users
Report Builder
Power Users
Developers Report Designer
What Report Builder is not…
A replacement for Pivot Tables
Can be used for some data slice and dice but not
specifically designed for this capability
An analytical client
No support for advanced financial calculations, what ifs,
writebacks, financial or business modeling, etc.
A developer-focused design tool
No nested data regions, freeform layout, multiple
queries, conditional formatting, or custom code
An HTML-based design tool
Report Builder is a WinForms (.NET Framework)
application
Report Builder Architecture
SQL Mgmt
Studio
Report
Designer
Model
Designer
Report
Manager
Web Service / URL interface
Report Server
Drill through report generation
Data Sources
(SQL Server,
Analysis Services)
Query generation
SQL Server Catalog
Report Builder
Client
Report Builder Client
Built on top of familiar Microsoft Office paradigms (Excel,
PowerPoint)
Reports built via report templates (table, matrix, chart,
combinations)
“Click once” application
deployed from the Report
Server
Users download and
launch the client from
Report Manager (new or
existing report)
Finished reports can be
saved on the server
Report Builder
Report Builder Reports
Report Builder reports are just like any other
Reporting Services report…
Report Builder reports are defined in RDL
Managed by the same APIs
Secured by the same APIs
Except: Report Builder reports contain semantic
model metadata
When an Report Builder report is requested by report
processing, the metadata is used to generate a query in
the source language
A new component on the Report Server handles the
query generation
Infinite Drill
Automatic generation of new drillthrough reports
allows users to explore data along model navigation
paths
Queries for navigation reports are generated on the
fly, passing the context of the user’s current
“location”
Users can keep drilling as long as there is a path to
follow from the current item
Custom reports can be specified for model items
Generated navigation reports are not saved unless
the user explicitly saves them
Report Builder Model Designer
Hosted in Visual
Studio (new project
type)
For editing models
used by Report
Builder
Auto-generation of
models from data
sources
Model Design
Model Designer is interface for designing/editing a
model
New project type in BI Development Studio
Define fields and roles
Define calculated fields
Organize model
Models can be auto-generated in Model Designer
or on the Report Server
Generates entities, fields and roles based on source
metadata
Auto-generation against SQL Server and Analysis
Services databases
Model Designer
Report Models
Models define entities, fields and roles available for Report
Builder
Models reference a data source (either SQL Server or
Analysis Services)
Models are stored in the Report Server as a new item type
Includes new management APIs
Published models can be secured
Items within models (entities, fields, roles) can also be secured
Users only see items they have permissions to see
Model security and management through SQL Server
Management Studio
Report Builder Model Designer
Report Builder vs. Report Designer
Report Builder
Targeted at business users
Ad hoc reports
Auto-generates queries using
semantic layer on top of the
source
Reports built on templates
Report Designer
Targeted at IT pros and
developers
Managed reports
Native queries (SQL, OLE DB,
XML/A, ODBC, Oracle)
Free-form (nested, banded)
reports
Integrated into Visual Studio
Click-once application, easy to
deploy and manage
Cannot import Report Designer Can work with reports built in
reports
Report Builder
Wrap-up
Reporting Services in SQL Server 2005
We have listened to feedback – smoothed off the rough
edges
Report Builder is…
a new ad hoc report design tool for SQL Server Reporting
Services
targeted at business users who want to find and share
answers to interesting questions
driven from a business model of the data so users do not
need to understand the underlying data structures
is fully integrated with SQL Server Reporting Services
and delivered in SQL Server 2005
Resources
Product site
http://www.microsoft.com/sql/reporting/
How to license
http://www.microsoft.com/sql/reporting/howtobuy/howtolicensers.asp
Articles:
Intro to Report Builder http://www.developer.com/db/article.php/3520116
Webcasts:
Report Builder on MSDN TV
http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/2005
0531SQLServerCC/manifest.xml
Report Builder linked from
http://blogs.msdn.com/ericnel/archive/2004/11/11/255724.aspx
Reporting Services
http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263305
&culture=en-us
Books
Hitchhikers Guide to SQL Server 2000 Reporting Services – excellent!
Pubs and NorthWind http://go.microsoft.com/fwlink/?LinkId=30196
© 2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.