Developing Rich Reporting Solutions with Microsoft SQL Server 2008 R2

1. SVR09 Developing Rich Reporting Solutions with Microsoft SQL Server 2008 R2 Stella Chan Principal Program Manager Microsoft Corporation

2. 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

3. Reporting Challenges How Do You Deal With: Data stored in multiple data sources Limitations in custom report engines Report development processes Expensive, 3rd party reporting tools • Pervasive delivery of information Scalability and reliable report processing Steep learning curves for new tools Delivery of contextual business insight

4. Typical Report Life Cycle Management Delivery Authoring 2007 Sales Embedded Reporting Web Report XML

5. Authoring ReportsMany types of visualizations

6. Authoring ReportsMany types of layouts Custom Subtotals Non-pivot columns Custom Headers Pivot Multi-Pivot Stepped or Block grouping Nested controls

7. 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

8. Delivering ReportsMany rendering targets XML SQL Report Builder Microsoft Excel HTML Microsoft Word CSV TIF ATOM Acrobat PDF

9. Delivering ReportsViewing the report Report Server Report Manager SharePoint

10. Embedding Report in LOB appWindows Forms and ASP .NET

11. What’s new in SQL Server 2008 R2? Management Delivery Authoring 2007 Sales Embedded Reporting Web Report XML

12. 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

13. Shared Datasets • Share queries between reports with new .rsdfile 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

14. Grab-and-go reporting

15. SSRS Data Visualization

16. Sparklines and Data Bars • 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. Sparklines Data Bars

17. Indicators • 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

18. Creating report using Sharepoint List as Data Source demo Stella Chan Principal Program Manager SQL Server Reporting Services

19. 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

20. MAPs

21. 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

22. Map Components (Example 1) Polygon Layer Map Legends Analytical Data Bubble w/size Rule Color Rule

23. Map Components (Example 2) Point Layer SQL Spatial Query Polygon Layer Tile Layer SQL Spatial Query

24. Map Example

25. 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

26. Using the Map Visualization demo Stella Chan Principal Program Manager SQL Server Reporting Services

27. What’s new in SQL Server 2008 R2? Management Delivery Authoring 2007 Sales Embedded Reporting Web Report XML

28. Microsoft Confidential Microsoft BI Offering BUSINESS USER EXPERIENCE BUSINESS MIDDLE TIER & 2010 Reports Dashboards DATA INFRASTRUCTURE & BI PLATFORM Reporting Services & 2008 R2

29. SSRS Add-In Install Report Server Report Server in SharePoint Mode Reporting Services Add-in Security Extension Data Management Report management UI Report Viewer web part SharePoint Object Model SharePoint Object Model SharePoint Integration Architecture WSS or MOSS WFE SSRS Proxy Report Server DB SharePoint Config / Content DB

30. 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

31. Programmability for SharePoint integration • New 2010 centralized management web service endpoint for both SharePoint integrated and native mode • AJAX enabled Report Viewer Web Part

32. What’s new in SQL Server 2008 R2? Management Delivery Authoring 2007 Sales Embedded Reporting Web Report XML

33. ATOM Renderer • 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

34. Rendering enhancements • 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

35. Viewing report in Firefox and exporting to Excel demo Stella Chan Principal Program Manager SQL Server Reporting Services

36. Visual Studio 2010 Report Viewer Controls • ASP.NET AJAX and Windows Forms • Free redistributable runtime components

37. 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

38. ReportDesigner (BIDS or RB 3.0) Report Viewer(Remote Mode) Publish Report DataSource Report Server (Remote) Mode Your Application Report Server Web Services API Report Processing and Rendering SQL Server Catalog (Metadata)

39. ReportDesigner (Visual Studio) DataSource Report Definition Report Data Embed Report Retrieve Data Local Mode Your Application Report Viewer(Local Mode) Report Processing

40. 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

41. 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

42. AJAX viewer programmability demo Stella Chan Principal Program Manager SQL Server Reporting Services

43. 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

44. 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

45. YOUR FEEDBACK IS IMPORTANT TO US! Please fill out session evaluation forms online at MicrosoftPDC.com

46. 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/learn Built by Developers for Developers….