Deepak Kumbhar [June 11 th , 2009]

1. Reporting Services Deep Dive Deepak Kumbhar [June 11th, 2009]

2. SQL Server 2008 Reporting Services Deep Dive Agenda • Reporting Services 2008 Architecture • Report Engine Improvements • Report Rendering Improvements • Memory Management • New Controls • Tablix [demo] • Charts and Gauges [demo] • Report Builder 2.0 [demo] 2

3. SQL Server 2008 Reporting Services Deep Dive Benefits of Eliminating IIS from SSRS Architecture • Easier configuration • IIS settings for other applications impact SSRS. • Better resource management • IIS is designed for static or dynamic HTML pages and not for executing large reports for many concurrent users. • Memory management is easier to implement outside of IISand can be efficiently managed. • Consolidation of two services into one • Communication process between services is now eliminated. • Elimination of deployment obstacles • SQL Server DBAs lacking IIS skills. • IT policies prevent IIS and SQL Server on same server. 3

4. SQL Server 2008 Reporting Services Deep Dive Reporting Services 2008 Architecture 4

5. SQL Server 2008 Reporting Services Deep Dive Report Engine Improvements • On-demand processing of reports with many datasets and data regions • Reports with heavy usage of interactivity can be significantly faster • Faster page-by-page navigation when viewing reports interactively • Smaller memory footprint • You probably now think -- thanks that’s nice; and where is the catch?  5

6. SQL Server 2008 Reporting Services Deep Dive Processing and Rendering Architecture 2005 2008 Data Tablix Table Matrix List Chart Calcs Soft Page Layout HTML Webforms Group HTML Excel Chart Winforms Data CSV Image ROM Storage XML Hard Page Layout Image Print Image PDF 6

7. SQL Server 2008 Reporting Services Deep Dive Understanding Memory Management Medium Memory Pressure • Current requests continue • New requests might be accepted • Memory allocations reduced for all apps • Background largest reduction Low Memory Pressure High Memory Pressure • Current requests continue • New requests accepted • Background low priority • Current requests slow • New requests denied • Memory allocation reduced • Memory swaps to disk 7

8. SQL Server 2008 Reporting Services Deep Dive Configuring Memory Changes Boundaries of medium pressure % Full Memory Range KB WorkingSetMaximum MemoryThreshold MemorySafetyMargin WorkingSetMinimum High memory pressure Medium memory pressure Low memory pressure 8

9. SQL Server 2008 Reporting Services Deep Dive Introducing Tablix Tabl e + Matr ix 9

10. SQL Server 2008 Reporting Services Deep Dive Features of Tablix Control • Flexible grid layout • Fixed columns and dynamic rows like a Table • Dynamic rows and columns like a Matrix • Any combination of Table and Matrix layouts • Flexible grouping • Nested groups • Adjacent groups • Recursive groups 10

11. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Hierarchical rows with dynamic headers Current Desired 11

12. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Mixing dynamic and static columns Current Desired 12

13. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Parallel Dynamic Groups Current Desired 13

14. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Non-Aggregate Details in Subtotals Current Desired 14

15. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Scope-specific aggregates in subtotals Current Desired 15

16. SQL Server 2008 Reporting Services Deep Dive Tablix Examples Non-detail aggregates in subtotals 16

17. SQL Server 2008 Reporting Services Deep Dive Rendering Improvements • CSV Renderer redesign • True data renderer, not a combination of data and layout • Removing the layout information means the data output file can be • consumed more readily by other applications. • Excel Renderer • Supports rendering of subreports and nested data regions 17

18. SQL Server 2008 Reporting Services Deep Dive New Word Renderer • Output RDL to Microsoft Word binary format (.doc) • Support for Word 2000 and later • Documents fully editable in Word 18

19. SQL Server 2008 Reporting Services Deep Dive Rich Text Formatting • Apply different format properties to selected text within a textbox • - Font settings: Color, Font, TextAlign • - Paragraph settings: Indent, SpaceAfter, SpaceBefore • - List settings: ListLevel, ListStyle • Use HTML tags in selected text • - Action: HTML– Interpret HTML tags as styles 19

20. SQL Server 2008 Reporting Services Deep Dive • demo ReportingServices Tablix Control 20

21. SQL Server 2008 Reporting Services Deep Dive Data Visualization • MS has acquired Dundas Software’s Data • Visualization products • Chart • Gauge • Map • Barcode • Calendar • New Chart and Gauge in SSRS 2008 • Other controls planned for post-2008 21

22. SQL Server 2008 Reporting Services Deep Dive New Chart Types • Stepped Line • Range (Spline Area) • Pie Callout • Polar • Radar • Range Column/Bar • Funnel • Pyramid • Boxplot/Error 22

23. SQL Server 2008 Reporting Services Deep Dive Gauges and Indicators • Display and monitor real-time data • Use as dashboard or scorecard components forvisualizing KPIs 23

24. SQL Server 2008 Reporting Services Deep Dive • demo ReportingServices Data Visualization 24

25. SQL Server 2008 Reporting Services Deep Dive Report Builder 2.0 / Report Designer Data Pane Office-style Ribbon Properties Pane Globals Parameters Expression Placeholders Data Source & Dataset Group Task Pane 25

26. Feature Comparison Report Designer SQL Server 2008 Reporting Services Deep Dive VS Integration Office 12 Look & Feel Full RDL Support Shared Layout Surface Shared Dialogs Report Builder 2.0 Report Models Integrated Query and Layout Full Report Model Support Click through Reports Limited RDL Support Report Builder 1.0 26

27. SQL Server 2008 Reporting Services Deep Dive • demo ReportingServices Report Builder 2.0 27

28. SQL Server 2008 Reporting Services Deep Dive Resources • Application Domains for Report Server Applications, http://msdn2.microsoft.com/en-us/library/bb934330(SQL.100).aspx • Configuring Authentication in Reporting Services, http://msdn2.microsoft.com/en-us/library/bb283249(SQL.100).aspx • About URL Reservations and Registration (Reporting Services), http://msdn2.microsoft.com/en-us/library/bb677364(SQL.100).aspx • Group Variables in SSRS 2008 for Custom Aggregationhttp://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx • Reporting Services WMI Provider, http://msdn2.microsoft.com/en-us/library/ms152836(SQL.100).aspx 28

29. ThankYou Deepak Kumbhar dkumbhar@bennettadelson.com 29