Downloaded 122 times
Uploaded byBala Subra
Basics & Intro to SQL Server Reporting Services: Sql Server Ssrs 2008 R2
SQL Server Reporting Services provides reporting capabilities through traditional and interactive reports. It offers a scalable and manageable server infrastructure that integrates with SharePoint, Office applications, and browsers. The platform supports structured data like relational, hierarchical, and multidimensional data. SQL Server Reporting Services supports the full reporting lifecycle including authoring, management, and delivery of reports.
Basics & Intro to SQL Server Reporting Services: Sql Server Ssrs 2008 R2
- 1. SQL Server Reporting Services Bala Subra @bsubra @bsubra
- 2. Executive Summary Deliveredthrough: Traditional and interactive reports Scalable, manageable and embeddable server infrastructure Integration with SharePoint, Office applications, browser and other familiar tools Single platform and tools for all types of structured data (relational, hierarchical, multidimensional) Product enabling employees at all levels of an organization to realize the promise of BI and to promote better decision making @bsubra
- 3. BI User ProfilesInformation Consumers Information Explorers 5-10% of users 15-25% of users 65-80% of users Analysts @bsubra SQL Server Reporting Services SQL Server Analysis Services
- 4. Reporting Services Architecture(2005) SQL Server Catalog Report Server Delivery Delivery Targets (E-mail, File share, Custom) Security Services (NT, Passport, Custom) Security Data Sources (SQL, OLE DB, ODBC, Oracle, Custom) Output Formats (HTML, Excel, PDF, Custom) Report Processing Browser Management Custom App URL WMI Web Service Data Retrieval Rendering @bsubra
- 5. Reporting Services Architecture(2008) @bsubra
- 6. Service Architecture (ReportingServices) @bsubra
- 7.
- 8. Reporting Services availableWeb service development options @bsubra
- 9. Business Scenarios EnterpriseReporting In-house reports (Sales, Finance, HR) Manageability, portal and LOB integration Embedded Reporting Report processing included in applications Extensible, embeddable architecture B2B Reporting Partner reporting via extranets, Internet Data isolation, extensible security @bsubra
- 10. Reporting Lifecycle SQLServer Reporting Services supports the full reporting lifecycle, including Authoring, Management and Delivery @bsubra Authoring Management Delivery
- 11. Report Authoring ReportDesigner Integrated with Visual Studio Powerful, XML Report Definition Language (RDL) Freeform / Table / Matrix / Chart / Tablix Parameters (Dynamic, Hierarchical) Sorting / Filtering / Grouping / Aggregates Interactivity (Drill-down, Drill-through) Full VB.NET Expression Language Published RDL definition to enable 3rd parties to create custom authoring tools Extensible query execution architecture SQL Server / Analysis Services OLE DB / ODBC / Oracle @bsubra
- 12. Report Management WebService APIs for management Web management user interface Report Management Functions Report Metadata (Name, Description, Connections, Credentials, Parameters) Scheduling integrated with SQL Server Agent Execution properties (live, cache or snapshot) History of prior executions Extensible, role-based security architecture @bsubra
- 13. Report Delivery Singlereport definition can be rendered to diverse output formats Web Formats (HTML 4, HTML 3.2, HTML w/OWC) Print Formats (TIFF, RTF, PDF) Data Formats (Excel, XML, CSV) Personalized and administrator defined subscription and delivery Ability to select output formats per subscription Can deliver links as well as rendered reports Data-driven subscriptions for personal delivery Extensible delivery provider architecture E-mail, File System, Custom @bsubra
- 14. Reporting Services TerminologyData Source – a database Dataset – a query against a data source Data Region – an item that displays repeated rows of data Types of Data Regions Table – a banded, tabular report Matrix – a cross-tab (or pivot table) report List – data arranged in free-form fashion Chart – a graph @bsubra
- 15. Creating Reporting ServicesProject Creating a Report Creating a Group Interactive Sorting Creating Drill-Down Reports @bsubra
- 16. Calculations & FormattingReporting Services uses VB-like expressions You can use standard +. -, *, /, mod operators String concatenation with & (don’t use +) Aggregate functions: Sum, Count, Avg, Min, Max, First, Last, etc. Also supported: IIf(), Len(), Switch() @bsubra
- 17. Expressions Fields toaccess values from current row e.g. =Fields!LastName.Value Globals to access global report data e.g. =Globals!PageNumber User to access info about authenticated user running the report e.g. =User!UserID; =User!Language @bsubra
- 18. Expressions Parameters toaccess value of input parameters e.g., =Parameters!Country.Value ReportItems to access values from other sections of report e.g.= ReportItems!txtQuantity.Value * @bsubra
- 19. Using Custom CodeYou can write your own VB functions and include them in the Code tab of the Report|Report Properties dialog Visual Basic is the only language supported by SSRS in the code window You can also develop custom assemblies that you can call from reports Assemblies can be developed in any .NET language You have to follow a number of steps to reference, deploy, and access your assemblies @bsubra
- 20. Custom Code ExamplePublic Function ColumnSel (ByVal parameter as Parameter, ByVal ColID as integer) as Boolean If parameter.IsMultiValue then For i as integer = 0 to parameter.Count-1 If val(parameter.Value(i)) = ColID then Return True End If Next Return False Else Return False End If Return False End Function @bsubra
- 21. Formatting Fields ConditionalFormatting Use IIf, Switch, or a custom function as value for property in property sheet You can do this for textboxes and table rows (sections of a report) Need to display sales over $1,000 bolded and in green @bsubra
- 22.
- 23. Themes Core ProductEnhancements Extend features based on feedback Incremental enhancements, performance and scalability Tighter Integration Strengthen SQL Server end-to-end BI scenarios Tighter integration with Office and SharePoint Developer Focus Unparalleled 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 @bsubra
- 24. Feature sets ReportBuilder VS Integration VS Controls Interactive Sort Multi-valued parameters Parameter Date Picker Management Studio Integration MDX Query builder Configuration Tool Custom Report Item Floating headers Printing from IE (SP2) Web Parts (SP2) 64 bit Support Multi-instance Support Expression builder enhancements @bsubra
- 25.
- 26. Most Requested ItemsPPT rendering Shared Styles (Styles sheets) Templates Font embedding in PDF Many Chart enhancement Heterogeneous Join Edit in Preview @bsubra
- 27. Rich Client PrintingDirect 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 @bsubra
- 28. Enhanced Expression EditorAvailable for all expressions in VS Report Designer List of available VB.NET functions Inline parameter information Statement completion Real-time syntax checking @bsubra
- 29. Multi-Valued Parameters Reportdeveloper 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 @bsubra
- 30. Date Picker ParametersCalendar control for choosing dates Localized and Globalized Windows and Web support @bsubra
- 31. Interactive Sort Reportauthor 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 @bsubra
- 32. Floating Headers Newtype 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 @bsubra
- 33. SQL Server 2008R2 Enhancements New Data Sources SharePoint List SQL Azure SQL Server Parallel Data Warehouse Combining Data from More Than One Dataset Conditional Rendering Expressions =iif(RenderFormat.Name="EXCEL", True, False) Read/Write Report Variable Shared Datasets Report Parts @bsubra
- 34. Data Synchronization DomainScopeText Box Orientation @bsubra
- 35. Data Visualization DataBars Sparklines Maps Indicators @bsubra
- 36. MDX Query BuilderMDX and data mining query builders MDX parameter support Member extended properties Support for server aggregates @bsubra
- 37. Custom Report ItemEnables 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 @bsubra
- 38. Management Studio IntegrationSingle point of management for all SQL Server components Superset of Report Manager functions Script generation from property dialogs @bsubra
- 39. Server Configuration ToolGraphical configuration tool for Report Server Database Settings Virtual Directories E-Mail Key Management @bsubra
- 40. SharePoint Web PartsReport 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 @bsubra
- 41. Visual Studio IntegrationReport design completely integrated with Visual Studio language projects Natural extension of VS data functionality Included in VS Pro and above @bsubra
- 42. Report Controls Controlsmake 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 @bsubra
- 43. Report Builder Anew 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 @bsubra
- 44. Why Report Builder?Report Viewer Report Builder Report Designer @bsubra Report Consumers Business Users Power Users Developers
- 45. What Report Builderis 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 @bsubra
- 46. Report Builder ArchitectureSQL Server Catalog Web Service / URL interface Report Server Report Builder Client Model Designer Data Sources (SQL Server, Analysis Services) Drill through report generation Query generation Report Manager Report Designer SQL Mgmt Studio @bsubra
- 47. Report Builder ClientBuilt 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 @bsubra
- 48. Report Builder ReportsReport 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 @bsubra
- 49. Infinite Drill Automaticgeneration 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 @bsubra
- 50. Report Builder ModelDesigner Hosted in Visual Studio (new project type) For editing models used by Report Builder Auto-generation of models from data sources @bsubra
- 51. Model Design ModelDesigner 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 @bsubra
- 52.
- 53. Report Models Modelsdefine 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 @bsubra
- 54. Report Builder vs.Report Designer @bsubra Report Builder Report Designer Targeted at business users Targeted at IT pros and developers Ad hoc reports Managed reports Auto-generates queries using semantic layer on top of the source Native queries (SQL, OLE DB, XML/A, ODBC, Oracle) Reports built on templates Free-form (nested, banded) reports Click-once application, easy to deploy and manage Integrated into Visual Studio Cannot import Report Designer reports Can work with reports built in Report Builder
- 55. Wrap-up Report Builderis… 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 @bsubra
- 56. Comparing Report AuthoringEnvironments @bsubra
- 57. SQL Server ReportBuilder @bsubra
- 58. Planning a DeploymentTopology @bsubra or
- 59.
Editor's Notes
- #2 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Microsoft SQL Server 2008 Reporting Services provides a complete server-based platform that is designed to support a wide variety of reporting needs including managed enterprise reporting, ad-hoc reporting, embedded reporting, and web based reporting to enable organizations to deliver relevant information where needed across the entire enterprise. Reporting Services 2008 provides the tools and features necessary to author a variety of richly formatted reports from a wide range of data sources and provides a comprehensive set of familiar tools used to manage and secure an enterprise reporting solution. Reports are processed and delivered quickly and effectively, enabling users to either receive reports automatically through subscriptions, to access reports from a central report repository on an ad-hoc basis, or to consume reports in context of their business processes through reports directly embedded into their business or web applications.
- #3 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Parameterized reports Linked reports Snapshot reports Cached reports Ad hoc reports Clickthrough reports Drilldown reports Drillthrough reports Subreports
- #4 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Managed Reporting. Also often referred to as enterprise reporting - supports the creation of reports that span all aspects of the business and delivers them across the enterprise to provide every employee real time access to information relevant for their business area and enable better decision making Ad-Hoc Reporting. Enables users to create their own reports on an ad-hoc basis and provides them with the flexibility to quickly get the information that they need, in the format that they need it without submitting a request and waiting for a report developer to create the report for them. Embedded Reporting. Enables organizations to embed reports directly into business applications and web portals, enabling users to consume reports within the context of their business process. Deep integration with Microsoft Office SharePoint Server 2007 also enables organizations to deliver reports through a central report library or to use new web parts for thin rendering of reports directly within SharePoint enabling easy creation of dashboards. In this way organizations are able to bring all business critical data, structured as well as unstructured, from across the company together in one central location providing one common experience for information access so that users can see key business performance information at a glance.
- #5 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #6 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #7 In Reporting Services, a report server is implemented as a Windows service that consists of distinct feature areas that run in separate application domains. The service hosts Report Manager, the Report Server Web service, and background processing feature areas. 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #8 The following diagram shows the three-tier architecture of a Reporting Services deployment. It shows the report server database and data sources in the data tier, the report server components in the middle tier, and the client applications and built-in or custom tools in the presentation tier. It shows the flow of requests and data among the server components and which components send and retrieve content from a data store. 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #10 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #11 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #12 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. List . A freeform layout for data. Chart . A graphical format that displays data in the form of a chart. Table . A tabular format in which data is displayed in rows and columns. You can create a hierarchy of rows to reflect groupings in your data and display group totals. Matrix . A format that supports row and column groups, and which can display aggregated summary data in the cells where row groups and column groups intersect one another, similarly to a pivot table or crosstab. Tablix. A hybrid between Table and Matrix, introduced with the release of SQL Server 2008 Reporting Services.
- #13 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #14 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #15 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #16 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Creating a Group • Right-click on left margin of table and select Insert Group • Select a field to group on or enter an expression • Select any options (page break at start, page break at end, etc.) and click OK to create group • SSRS doesn't automatically add the group field to the group header (the wizard does, though) but you can add it easily enough Groups • You can use Sorting tab of Grouping and Sorting Properties dialog to sort groups and detail with groups • To edit group, right-click on left-hand margin of group header or footer and select Edit Group • Groups can be nested many deep Interactive Sorting • Lets users re-sort reports on columns that you pre-specify • Right-click column header, select Properties, & click Interactive Sort tab Creating Drill-Down Reports • Drill Down Reports (rptCustomerHierarchy2) ? Using the Visibility properties of TableRows you can create an interactive drill-down effect ? Printed output will always show the fully expanded data • Document Maps (rptCustomerHierarchy3) ? Use the Document map label property of Grouping and Sorting Properties dialog ? Converts to PDF Bookmarks Displaying Data on a Report • When you drag a field onto a section of a report, SSRS places it within a TextBox • TextBoxes can be bound to a field or to a member of a global collection Expressions • Reporting Services uses VB-like expressions • You can use standard +. -, *, /, mod operators • String concatenation with & (don’t use +) ? =Fields!City.Value & ", " & Fields!Country.Value • Aggregate functions: ? Sum, Count, Avg, Min, Max, First, Last, etc. • Also supported: ? IIf(), Len(), Switch()
- #17 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #18 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. • =Globals!TotalPages • =Globals!ExecutionTime, etc. Group Calculations rptEmployeeSales2 report • You can use standard aggregate functions ? Sum, Count, Avg, Min, Max, etc. ? e.g. =Sum(Fields!Cost.Value) • Calculating Percentages ? Trick is to give the textbox containing the denominator a name (e.g., txtTotalCost) and use it in expression,referencing it using ReportItems collection ? e.g., • =Sum(Fields!Cost.Value)/ReportItems!txtTotalCost.Value
- #19 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #20 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #21 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. =iif(code.ColumnSel(Parameters!Column_Sel,3)= true,false,true) Public Function GetColumnColor(ByVal parameter as Parameter) as string Public Function p(ByVal something as String) as String MsgBox(something) Return something End Function Public Function GetFontWeight(ByVal parameter as Parameter) as string
- #22 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Formatting Numbers/Dates ? Right-click on textbox and select Properties ? Enter a .NET format string or click on […] • Styles (Font, FontSize, Color, etc.) ? Use property sheet
- #23 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary. Creating a Report • A number of ways to create a new report, including ? Right click on the Reports node in Solution Explorer and select • Add New Report – starts report wizard • Add|Add New Item – to create a blank report • Import Reports – to import all of the reports from an Access database
- #24 Read/Write Report Variable Combining Data from More Than One Dataset Conditional Rendering Expressions Let’s say that you want to simplify the report layout when a user exports a report to Microsoft Excel. Sometimes other report items in the report can cause a text box in a data region to render as a set of merged cells when you are unable to get everything to align perfectly. The usual reason that users export a report to Excel is to filter and sort the data, and they are not very interested in the information contained in the other report items. Rather than fussing with the report layout to get each report item positioned and aligned just right, you can use an expression in the Hidden property to keep those report items visible in every export format except Excel. Simply reference the name of the extension as found in the RSReportServer.config file in an expression like this: =iif(RenderFormat.Name="EXCEL", True, False) 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #26 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #48 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #58 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #59 The single server deployment configuration is recommended in the following circumstances: For small to moderate report volumes where demand for report processing is evenly spaced throughout the day and the number of concurrent sessions is easily handled by the processing capability of the computer. When you are a developer and need to develop custom solutions that integrate with Reporting Services. When you are evaluating the software. A scale-out server deployment configuration is recommended in the following circumstances: For high-volume reporting, where activity is measured in concurrent users or in the complexity of reports that take a long time to process or render. For high-availability scenarios, where it is important that the reporting environment does not encounter unplanned downtime or become unavailable. When you want to improve the performance of scheduled operations and subscription delivery. 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
- #60 11/19/11 14:41 © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.