1 / 72

Microsoft SQL Server Reporting Services

Microsoft SQL Server Reporting Services. Dave Henson dhenson@certifiednetworks.com. Why Use SSRS. Secure Deployment of Professional Reports Accessible Anywhere – web based Define one report, render to many formats Html Pdf Xml Integrated with SQL 2008

happy
Download Presentation

Microsoft SQL Server Reporting Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft SQL Server Reporting Services Dave Henson dhenson@certifiednetworks.com

  2. Why Use SSRS • Secure Deployment of Professional Reports • Accessible Anywhere – web based • Define one report, render to many formats • Html • Pdf • Xml • Integrated with SQL 2008 • Easily extensible for .net developers

  3. Course Outline • Module 1: Introduction to Microsoft SQL Server Reporting Services(SSRS) • Module 2: Authoring Basic Reports • Module 3: Enhanced Reporting • Module 4: Manipulating Data Sets • Module 5: Managing Content • Module 6: Administering SSRS • Module 7: Programming SSRS

  4. Course Logistics • Class Format • 50% Lecture & Demonstrations • 50% Lab • Course Dates • Course Hours • Lunch

  5. Definitions • IIS • SQL • Visual Studio .Net 2005 • SSRS • RDL • XML • Business Intelligence (BI) • OLTP • OLAP • OLEDB • .Net

  6. Reporting Lifecycle • Authoring to create .rdl file • Management • Security • Deployment • Delivery • Pull • Report Manager • Custom App • Push • Email • Custom App

  7. Reporting Services Scenarios • Internal • External • Embedded – online portal • Standard vs. Ad-hoc Reports

  8. Delivery and Rendering Options • HTML, MHTML • PDF, TIFF • CSV, XML • Custom .Net Rendering • Content: • Standard, Crosstab, Charting, Graphics • Real time or scheduled as a task

  9. SSRS Architechture • Report Server • http://localhost/reportserver • Report Manager • http://localhost/reports

  10. SSRS Components • Application layer • Report Authoring • Standard • Ad Hoc • Report Server Management • Server Layer • Report Server • Report Processor • Data Layer • Any OLEDB Datasource

  11. Authoring Components • Report designer templates added to Visual Studio during install • Report Builder • Thin client .net app installed through browsing • Uses a published report model to build query • Reporting Services API for authoring

  12. Management Components • SSRS Configuration Tool • Report Manager • Command Line • Rsconfig • Rs • Rskeymgmt • SSRS API

  13. Report Delivery Components • Report Manager • Report Processor • Scheduling and Delivery Processor

  14. Server Extensions • SSRS fully exposed through .net libraries • Extensibility • Authentication • Data Processing • Report Processing • Rendering • Delivery

  15. Installation • Prerequisites • Windows Server (2003/2008) • IIS • SQL Server – local to machine or otherwise • Versions • Workgroup(Express) • Standard • Enterprise • Trial

  16. Server Configuration • IIS • Security Accounts • SSRS Config Manager • SSRS Services and accounts • Windows Service • Web Service • Impersonation

  17. SSRS Quick Tour • Demonstration of basic reporting: • Create BI Project • Add Datasource • Add Dataset • Design Report • Deploy Report

  18. Module 2: Authoring Basic Reports

  19. Visual Studio 2008 – New BI Project

  20. Data Source Setup

  21. Adding the dataset

  22. Structuring Data in the Report

  23. Report Layout and Presentation

  24. Publishing the Report

  25. Managing the Report

  26. Accessing the Report

  27. Module 3: Enhanced Reporting

  28. Parameters • Replace items in where clause with sql named parameters • Use a query to populate a dropdown for the parameter

  29. Formatting • Use .Net formatting rules in the “format” property: • 0N = number: 100.99 • 0C = money: $100.99

  30. Filters • Blanket reduction of rows returned from the dataset

  31. Data Regions • Areas on report with repeated data: • Table: fixed columns • Matrix: variable columns • List • Subreports

  32. Actions • Link a report item to data stored elsewhere • Report • Url (.aspx, .php, .htm)

  33. Visibility • Use “Hidden” property to hide fields, rows, etc. • Use the ToggleItem property for interactive control of visibility

  34. Recursive Groups • Using Parent Grouping in the Grouping Properties for recursive display • Use the Level function to identify the relationship of the current row to the top row =Level(“RecursiveGroup”)

  35. Module 4: Managing Datasets

  36. IIS Session • Report execution is stored by default in user session • Cached instances share the report across multiple sessions(multiple visitors)

  37. Working with a shared schedule • Create a master schedule used by a series of reports for updating cached information

  38. Snapshot Reports • Report is executed on a scheduled basis and delivered to a folder

  39. Report parameters • Parameters used as a “filter” can be used against the snapshot data

  40. Calculation Load • Structure of your query defines the load point of running the report: • SQL Backend • Reporting Services Engine

  41. Manual Caching • Running an SQL job to create a table with the results of a report • Pre-aggregating

  42. Module 5: Managing Content

  43. Publishing Content • Report Designer • Report Manager • SQL Server Management Studio • Rs.exe and .rss file • .Net libraries

  44. Enabling User Reports • Enable a My Reports folder for each user

  45. Report Builder • .net library delivered over the web • Allows power users to create their own reports • Requires a report “model” be published by admin • Describes tables and relationships • Published as SMDL file • Data Source View (DSV) enables subset of base data

  46. Reverse-Scripting Objects • Visual Studio Script Generation • .rss script file can be used with the rs.exe utility

  47. Data Sources • Updates needed through time • Report Manager • Visual Studio .Net 2005

  48. Configuring Snapshot Reporting

  49. Snapshot Report History • Viewing • Configuring

  50. Report Subscriptions • Supports the push model of reporting

More Related