730 likes | 993 Views
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
E N D
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 • Easily extensible for .net developers
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
Course Logistics • Class Format • 50% Lecture & Demonstrations • 50% Lab • Course Dates • Course Hours • Lunch
Definitions • IIS • SQL • Visual Studio .Net 2005 • SSRS • RDL • XML • Business Intelligence (BI) • OLTP • OLAP • OLEDB • .Net
Reporting Lifecycle • Authoring to create .rdl file • Management • Security • Deployment • Delivery • Pull • Report Manager • Custom App • Push • Email • Custom App
Reporting Services Scenarios • Internal • External • Embedded – online portal • Standard vs. Ad-hoc Reports
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
SSRS Architechture • Report Server • http://localhost/reportserver • Report Manager • http://localhost/reports
SSRS Components • Application layer • Report Authoring • Standard • Ad Hoc • Report Server Management • Server Layer • Report Server • Report Processor • Data Layer • Any OLEDB Datasource
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
Management Components • SSRS Configuration Tool • Report Manager • Command Line • Rsconfig • Rs • Rskeymgmt • SSRS API
Report Delivery Components • Report Manager • Report Processor • Scheduling and Delivery Processor
Server Extensions • SSRS fully exposed through .net libraries • Extensibility • Authentication • Data Processing • Report Processing • Rendering • Delivery
Installation • Prerequisites • Windows Server (2003/2008) • IIS • SQL Server – local to machine or otherwise • Versions • Workgroup(Express) • Standard • Enterprise • Trial
Server Configuration • IIS • Security Accounts • SSRS Config Manager • SSRS Services and accounts • Windows Service • Web Service • Impersonation
SSRS Quick Tour • Demonstration of basic reporting: • Create BI Project • Add Datasource • Add Dataset • Design Report • Deploy Report
Parameters • Replace items in where clause with sql named parameters • Use a query to populate a dropdown for the parameter
Formatting • Use .Net formatting rules in the “format” property: • 0N = number: 100.99 • 0C = money: $100.99
Filters • Blanket reduction of rows returned from the dataset
Data Regions • Areas on report with repeated data: • Table: fixed columns • Matrix: variable columns • List • Subreports
Actions • Link a report item to data stored elsewhere • Report • Url (.aspx, .php, .htm)
Visibility • Use “Hidden” property to hide fields, rows, etc. • Use the ToggleItem property for interactive control of visibility
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”)
IIS Session • Report execution is stored by default in user session • Cached instances share the report across multiple sessions(multiple visitors)
Working with a shared schedule • Create a master schedule used by a series of reports for updating cached information
Snapshot Reports • Report is executed on a scheduled basis and delivered to a folder
Report parameters • Parameters used as a “filter” can be used against the snapshot data
Calculation Load • Structure of your query defines the load point of running the report: • SQL Backend • Reporting Services Engine
Manual Caching • Running an SQL job to create a table with the results of a report • Pre-aggregating
Publishing Content • Report Designer • Report Manager • SQL Server Management Studio • Rs.exe and .rss file • .Net libraries
Enabling User Reports • Enable a My Reports folder for each user
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
Reverse-Scripting Objects • Visual Studio Script Generation • .rss script file can be used with the rs.exe utility
Data Sources • Updates needed through time • Report Manager • Visual Studio .Net 2005
Snapshot Report History • Viewing • Configuring
Report Subscriptions • Supports the push model of reporting