![Page 1: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/1.jpg)
Developing Rich Reporting Solutions with Microsoft SQL Server 2008 R2Stella ChanPrincipal Program Manager Microsoft Corporation
SVR09
![Page 2: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/2.jpg)
Increase your productivity with SQL Server 2008 R2 Reporting Services > Creating reports
> Report components and shared data set> New data visualizations> Connectivity to SharePoint List
> Managing reports via self-service> Sharepoint 2010 Integration
> Delivering reports> Rendering enhancements> Better cross browser support> Visual Studio 2010 Report Viewer Controls
![Page 3: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/3.jpg)
Limitations in custom report
engines
Data stored in multiple data
sources
Pervasive delivery of information
Expensive, 3rd party reporting
tools
Scalability and reliable report
processing
Steep learning curves for new
tools
Report development
processes
Delivery of contextual
business insight
Reporting Challenges How Do You Deal With:
![Page 4: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/4.jpg)
Management Delivery
XML
Embedded Reporting
Authoring
Web Report2007 Sales
Typical Report Life Cycle
![Page 5: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/5.jpg)
Authoring ReportsMany types of visualizations
![Page 6: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/6.jpg)
Stepped or Block grouping
PivotCustom
SubtotalsNon-pivotcolumns
Nested controls
Multi-Pivot
CustomHeaders
Authoring ReportsMany types of layouts
![Page 7: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/7.jpg)
Managing Reports
> Enterprise Scale Platform> Scalable report processing
> Central Deployment> Simplified deployment and host
processes> Central deployment to SharePoint> OR Native Mode
> Strong Manageability> Central manageability of report server
content > Advanced report scheduling> Cache handling> Extensible security model
![Page 8: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/8.jpg)
SQL Report BuilderHTML
CSV
TIF
XML
ATOM
Microsoft Word
Microsoft Excel
Acrobat PDF
Delivering ReportsMany rendering targets
![Page 9: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/9.jpg)
Delivering ReportsViewing the reportReport Server
Report Manager
Sh
are
Poin
t
![Page 10: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/10.jpg)
Embedding Report in LOB app Windows Forms and ASP .NET
![Page 11: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/11.jpg)
Management Delivery
XML
Embedded Reporting
Authoring
Web Report2007 Sales
What’s new in SQL Server 2008 R2?
![Page 12: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/12.jpg)
Componentizing Reports Sharing and reusing report parts
> Publish report items to the catalog for re-use
> Users can mix & mash different report items for their desired views
> Report authors can treat datasets & report items as building blocks
> Users are notified when updates are available
![Page 13: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/13.jpg)
Shared Datasets
> Share queries between reports with new .rsd file type> Create in Report Builder and BIDS> Save/deploy to report server
> Reports and report parts can reference shared datasets> Pass parameters> Add own filters and calculations
> Dataset query results can be cached > Faster execution during design or view> Use for long-running queries with non-volatile
data
![Page 14: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/14.jpg)
Grab-and-go reporting
![Page 15: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/15.jpg)
SSRS Data VisualizationSQL 2005 SQL 2008 SQL 2008 R2
Chart Chart Chart
Multiple areasNEW! Multiple areas
Multiple axesNEW! Multiple axes
Calculated seriesNEW!
Calculated series
GaugeNEW! Gauge
Bullet graphNEW! Bullet graph
MapNEW!
SQL spatialNEW!
Bing™ supportNEW!
SparklineNEW!
Data BarNEW!
IndicatorNEW!
![Page 16: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/16.jpg)
Sparklines and Data Bars
Sparklines
> Sparklines> “Data intense, word-sized
graphics” – Tufte> Automatic axis alignment.
All charts can have the same data points, regardless of source data.
> Convert into full charts to support additional small multiple scenarios
> Data Bars> Show a single value in a cell.> Min and Max are
automatically determined, but are configurable.
Data Bars
![Page 17: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/17.jpg)
> Icons used to depict the status against a value, goals or a trend.
> Report authors can create their own sets or choose from several pre-configured sets.
> Color, icon and size can be changed based on data.
> Automatic support for comparing each member of a group against siblings.
> Can also be placed inside gauges.
Indicators
Indicators
![Page 18: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/18.jpg)
Creating report using Sharepoint List as Data Source
Stella ChanPrincipal Program ManagerSQL Server Reporting Services
demo
![Page 19: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/19.jpg)
Creating report using SharePoint List> Report Builder 3.0> SharePoint List as a data source> Indicators, data bars, and sparklines> Publishing report to SharePoint> Viewing report in SharePoint 2010
![Page 20: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/20.jpg)
MAPS
![Page 21: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/21.jpg)
Key Map Features
> Maps contains one or more… > Point Layers (Cities, Customers)> Line Layers (Routes, roads)> Polygon Layers (Countries, States)> Tile Layers (based on Bing™ Maps)
> Support ESRI shape files and SQL spatial
> Show analytical data using size and color> Built-in distribution formulas
> Wizard for building common scenarios
![Page 22: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/22.jpg)
Map Components (Example 1)Polygon Layer
Color Rule
Bubble w/size Rule
Map LegendsAnalytical Data
![Page 23: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/23.jpg)
Map Components (Example 2)
Point Layer
Tile Layer
Polygon Layer
SQL Spatial Query
SQL Spatial Query
![Page 24: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/24.jpg)
Map Example
![Page 25: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/25.jpg)
Misc authoring enhancements
> Lookup functions - “joining” 2 datasets
> Interlaced rows for nested group> Via read/write report varialbes
> Better page numbering and naming support via new global variables
> Rotating Text 270 degrees> Expression based page breaks
![Page 26: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/26.jpg)
Using the Map Visualization
Stella ChanPrincipal Program ManagerSQL Server Reporting Services
demo
![Page 27: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/27.jpg)
Management Delivery
XML
Embedded Reporting
Authoring
Web Report2007 Sales
What’s new in SQL Server 2008 R2?
![Page 28: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/28.jpg)
Microsoft BI Offering
Microsoft Confidential
BUSINESS USER EXPERIENCE
DATA INFRASTRUCTURE & BI PLATFORM
& 2008 R2Reporting Services
& 2010
BUSINESS MIDDLE TIER
Reports Dashboards
![Page 29: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/29.jpg)
SSRS Add-In Install
WSS or MOSS WFE
SharePoint Object Model
Report Server
Report Server in SharePoint Mode
Security Extension
Data Management
SharePoint Object Model
Reporting Services Add-in
Report Viewer web
part
Report manageme
nt UI
SSRS Proxy
Report Server DBSharePoint Config / Content DB
SharePoint Integration Architecture
![Page 30: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/30.jpg)
Preloading caches
> Caches can be preloaded ad-hoc or on a schedule
> Available for reports and shared datasets
> New “cache refresh plan” concept> Each plan consists of a description, schedule
and one combination of parameter values for which cache entry needs to be generated
> Many cache refresh plans per report or shared dataset
![Page 31: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/31.jpg)
Programmability for SharePoint integration> New 2010 centralized management
web service endpoint for both SharePoint integrated and native mode
> AJAX enabled Report Viewer Web Part
![Page 32: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/32.jpg)
Management Delivery
XML
Embedded Reporting
Authoring
Web Report2007 Sales
What’s new in SQL Server 2008 R2?
![Page 33: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/33.jpg)
> Exports report data as ATOM data feeds> Compliant format> Also used by SQL Server Data Services
and SharePoint 2010 lists> Each leaf data region has one or
more data feeds> Clients that consume ATOM feeds
(e.g., PowerPivot) can pull data directly from SSRS
ATOM Renderer
![Page 34: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/34.jpg)
> Improved Browser Compatibility> IE6-8, Firefox 3, Safari> Improved feature parity> Fixed standards mode rendering problems
> Renderer specific output> Via Globals!RenderFormat.Name to
control visibility of report items for specific renderers
> Naming the Excel worksheet tabs
Rendering enhancements
![Page 35: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/35.jpg)
Viewing report in Firefox and exporting to Excel
Stella ChanPrincipal Program ManagerSQL Server Reporting Services
demo
![Page 36: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/36.jpg)
Visual Studio 2010 Report Viewer Controls> ASP.NET AJAX and Windows Forms> Free redistributable runtime
components
![Page 37: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/37.jpg)
Key features in Visual Studio 2010 Report Viewer Controls
> SQL 2008 based local mode> On-demand processing> Support for 2008 RDLC (tablix,
chart/gauge, rich text)> Support for ASP.Net AJAX
> Viewer contains its own UpdatePanels> Interactivity is flicker free> Scroll position is maintained across
interactivity> No more frames – async/sync mode no
longer impacts rendering> Better browser compatibility
![Page 38: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/38.jpg)
Report Server
SQL Server Catalog (Metadata)
Web Services API
Report Processing and Rendering DataSource
Report Server (Remote) Mode
Your Application
Report Viewer(Remote Mode)
PublishReport
ReportDesigner
(BIDS or RB 3.0)
![Page 39: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/39.jpg)
Your Application
Report Viewer(Local Mode)
Report Processing
Local Mode
Report Definition
ReportDesigner(Visual Studio)
EmbedReport
Retrieve Data
Report Data
DataSource
![Page 40: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/40.jpg)
Additional Changes
> Extended Control API> Events for intercepting parameters> “Write a custom toolbar”> Printing and printer settings
> JavaScript API> Most toolbar functionality is available from the
client> Events and properties expose the state of the
viewer (error, report loading, loading completed)> UI Updates
> Resizable multi-value parameter dropdowns> Pop-up loading indicator
![Page 41: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/41.jpg)
VS 2010 Report Viewer Requirements> No SQL 2005 support
> Uses the 2008 RPL renderer on the server. HTML and GDI transformations happen in the viewer
> Must use AJAX> .Net 3.5 SP1 or 4.0 required
![Page 42: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/42.jpg)
AJAX viewer programmability
Stella ChanPrincipal Program ManagerSQL Server Reporting Services
demo
![Page 43: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/43.jpg)
Summary> Self service reporting for information
workers> Report components to enhance
collaboration and sharing> New data visualizations> Geo-spatial integration> Report as data source for PowerPivot> SharePoint 2010 integration
> Better cross browser support and fluid navigation via AJAX
> Embedded BI: Visual Studio 2010 Report Viewer Controls
![Page 44: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/44.jpg)
Links
> Visual Studio 2010 Beta2> SQL Server 2008 R2 CTP3
> ISO Image : http://go.microsoft.com/fwlink/?LinkID=165365&clcid=0x409 > x86 Build: http://go.microsoft.com/fwlink/?LinkID=165367&clcid=0x409 > x64 Build : http://go.microsoft.com/fwlink/?LinkID=165368&clcid=0x409 > IA64 Build: http://go.microsoft.com/fwlink/?LinkID=165369&clcid=0x409 > PowerPivot X86 :
http://go.microsoft.com/fwlink/?LinkID=168583&clcid=0x409 > PowerPivot X64 :
http://go.microsoft.com/fwlink/?LinkID=168584&clcid=0x409 > What’s New:
http://go.microsoft.com/fwlink/?LinkID=167918&clcid=0x409 > Release Notes: http://go.microsoft.com/fwlink/?LinkId=155801 > Readme : http://go.microsoft.com/fwlink/?LinkId=155103
> Brian Hartman’s Blog
![Page 45: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/45.jpg)
YOUR FEEDBACK IS IMPORTANT TO US!
Please fill out session evaluation
forms online atMicrosoftPDC.com
![Page 46: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/46.jpg)
Learn More On Channel 9
> Expand your PDC experience through Channel 9
> Explore videos, hands-on labs, sample code and demos through the new Channel 9 training courses
channel9.msdn.com/learnBuilt by Developers for Developers….
![Page 47: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/47.jpg)
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
![Page 48: Limitations in custom report engines Data stored in multiple data sources Pervasive delivery of information Expensive, 3 rd party reporting tools Scalability](https://reader038.vdocuments.us/reader038/viewer/2022110206/56649f425503460f94c629a9/html5/thumbnails/48.jpg)