SQL Server 2008 R2 and the Reporting Services

1. SQL Server 2008 R2 and the Reporting Services Christophe Codourey Consultant BI Christophe.codourey@trivadis.com TrivadisSA Rue Marterey 5 CH-1005 Lausanne

2. WebUI Programatic Interface (Web Service) Format (HTML, Excel, PDF, Word, …) Data Sources (SQL, SharePoint, ODBC, Oracle, Custom) ReportServer Rendering Data Retrieval SQL Server Catalog SQL Server 2008/R2 Reporting Services Browser Excel Custom App Atom Feed SharePoint Report Processing Security Delivery Distribution (E-mail, File share, Userdefined) Security Services (NT, Passport, Custom)

3. Report Design Tools Report Builder 3.0 BI Dev Studio RDL

4. WebUI Programatic Interface (Web Service) (HTML, Excel, PDF, Word, …) Data Sources (SQL, SharePoint, ODBC, Oracle, Custom) ReportServer Rendering Data Retrieval SQL Server Catalog Agenda Browser Excel Custom App Atom Feed SharePoint Report Processing Security Delivery Distribution (E-mail, File share, Userdefined) Security Services (NT, Passport, Custom)

5. Data extension supports SharePoint 2007/2010 sites Visual Query Designer SharePoint Data Source • <RSSharePointListxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> • <ListName>SuperLeagueTeams</ListName> • <ViewFields> • <FieldRef Name="ShortName" /> • <FieldRef Name="Title" /> • <FieldRef Name="Logo" /> • </ViewFields> • </RSSharePointList>

6. Available in SQL Server 2008 R2 Release Candidate SQL Azure and SQL Parallel Data Warehouse T/SQL SQL Azure

7. WebUI Programatic Interface (Web Service) (HTML, Excel, PDF, Word, …) Data Sources (SQL, SharePoint, ODBC, Oracle, Custom) ReportServer Rendering Data Retrieval SQL Server Catalog Agenda Browser Excel Custom App Atom Feed SharePoint Report Processing Security Delivery Distribution (E-mail, File share, Userdefined) Security Services (NT, Passport, Custom)

8. New text orientation option provides more flexibility to the report layout New Writing Mode: Rotate270

9. More control over page breaks Tablix, Rectangle and Group Page Breaks and Naming of Excel Worksheets =Globals!PageName & " Page: " & Globals!PageNumber & " of " &Globals!TotalPages ="Overall Page: " & Globals!OverallPageNumber & " of " & Globals!OverallTotalPages

10. Use expressions to determine what renderer is being used Dynamically disable/enable page breaks Dynamic page breaks Disable page breaks if exporting to Excel.

11. Control layout and write expressions that consider renderer format Renderer-based expressions Dynamically disable drill through expressions for non-interactive renderers and enable them for interactive ones

12. Nesting of aggregate functions is now supported e.g. Average Month Sales =Avg(Sum(Quantity, "Quarter"), "Year") Aggregates of Aggregates AVG AVG SUM

13. Datasets can be shared, stored, processed and cached externally from the report.  Cache refresh plans work on cached reports or shared dataset query results On first use Via a schedule Shared (Cached) Data Sets

14. Values from one DataSet can be usedto get Value(s) fromanother DataSet Lookup (1:1) MultiLookupGet multiple values,-> Param 1 is an array LookupSet (1:n) E.g. Get all phone nr from a selected customer Lookup & Co =Lookup(Fields!TeamA.Value ,Fields!Title.Value ,Fields!ShortName.Value ,"SuperLeagueTeam")

15. WebUI Programatic Interface (Web Service) (HTML, Excel, PDF, Word, …) Data Sources (SQL, SharePoint, ODBC, Oracle, Custom) ReportServer Rendering Data Retrieval SQL Server Catalog Agenda Browser Excel Custom App Atom Feed SharePoint Report Processing Security Delivery Distribution (E-mail, File share, Userdefined) Security Services (NT, Passport, Custom)

16. SSRS Data Visualization

17. Sparklines “Data intense, word-sized graphics” – Invented by Edward Tufte Automatic axis alignment. All charts will have the same number of data points, regardless of source data. Convertable into full charts Data Bars Show a single value in a cell. Min and Max are automatically determined, but are configurable. Sparklines and Data Bars Sparklines Data Bars

18. Sparklines = Fields!TeamB.Value & " " & Fields!Result.Value = iif(Fields!HomeGame.Value = 1, "Blue", "LightBlue")

19. Sparklines are regular charts with predifined property values Convert to Full Chart

20. Some details

21. Some details (2)

22. Some details (3)

23. Icons Show status against a value, a goal or a trend Own Icon Sets can becreated by Report Author Color, icon and size can be changed based on data Automatic support for comparing each member of a group against siblings • Indicators Indicators

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

25. Map Integration

26. “Map” Data • The product team currently plans on publishing links (Books On-Line/MSDN) to a number of map file providers when the product releases. However, we do not have a list that we can release at this time. Europe shape Files: http://www.cdc.gov/epiinfo/europe.htm

27. How to deal with Shape Files SZ.SHP http://www.sharpgis.net/page/shape2sql.aspx

28. Union on Shape Data Works currently only in Report Builder

29. Layers

30. Authoring ReportsMany types of visualizations

31. WebUI Programatic Interface (Web Service) (HTML, Excel, PDF, Word, …) Data Sources (SQL, SharePoint, ODBC, Oracle, Custom) ReportServer Rendering Data Retrieval SQL Server Catalog Agenda Browser Excel Custom App Atom Feed SharePoint Report Processing Security Delivery Distribution (E-mail, File share, Userdefined) Security Services (NT, Passport, Custom)

32. More intuitive UI Search Support Manage Report without rendering New “Reports Portal” (Native Mode)

33. Publish report items to the catalog for re-use Users can mix & mash different report items for their desired views Grab-and-go reporting

34. Atom Data Feed

35. ATOM Data Feed (do it yourself) • string uri ="http://.../ReportServer?/Dir/MatchResult&rs:Command=Render &rs:Format=ATOM&rc:DataFeed=xAx0x1"; • var client = new WebClient(); • client.Credentials = CredentialCache.DefaultCredentials; • Stream atomStream = client.OpenRead(uri); • XDocument document = XDocument.Load(atomStream); • var query = from feed in document.Descendants(XName.Get("properties", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata")) • select new • { • TeamA = feed.Element(XName.Get("TeamA", "http://schemas.microsoft.com/ado/2007/08/dataservices")).Value, • TeamB = feed.Element(XName.Get("TeamB", "http://schemas.microsoft.com/ado/2007/08/dataservices")).Value, • }; • ResultList.ItemsSource = query.ToList(); Feed-ID

36. ATOM Data Feed (do it yourself) • <?xmlversion="1.0" encoding="utf-8" standalone="yes"?> • <feedxmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" • xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> • <titletype="text"></title> • <id>uuid:0957b3c6-2d59-46b7-bcd8-ed0cfd5a48b0;id=2351</id> • <updated>2010-03-29T06:44:25Z</updated> • <entry> • <id>uuid:0957b3c6-2d59-46b7-bcd8-ed0cfd5a48b0;id=2352</id> • <titletype="text"></title> • <updated>2010-03-29T06:44:25Z</updated> • <author /> • <contenttype="application/xml"> • <m:properties> • <d:Roundm:type="Edm.Int32">1</d:Round> • <d:TeamA>FC Aarau </d:TeamA> • <d:TeamB>Grasshopper Club Zürich </d:TeamB> • <d:Result>01:00</d:Result> • <d:Pointsm:type="Edm.Int32">3</d:Points> • </m:properties> • </content> • </entry> • <entry> • <id>uuid:0957b3c6-2d59-46b7-bcd8-ed0cfd5a48b0;id=2353</id> • ...

37. Additional data sources, detail improvements for report authoring and data distribution SharePoint Lists, SQL Azure Page breaks, text rotation, info of render format, aggregates of aggregates Shared datasets Atom data feed New features in the area of better data visualization Data Bars Sparklines Indicators High flexibility for displaying Spatial Data Shape Files Bing Maps It’s not a revolution but the next evolution level Release 3.0 ;-) Conclusion

38. Thank you for your Attention! • For more Information please contact • Christophe Codourey • Consultant BI • Christophe.Codourey@trivadis.com • Trivadis SA • Rue Marterey 5 • CH-1005 Lausanne