microsoft sql server reporting services dave henson [email protected]
TRANSCRIPT
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
• SSRS• RDL• XML• Business Intelligence (BI)• OLTP• OLAP• OLEDB• .Net
•IIS•SQL•Visual Studio .Net 2005
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
Module 2: Authoring Basic Reports
Visual Studio 2008 – New BI Project
Data Source Setup
Adding the dataset
Structuring Data in the Report
Report Layout and Presentation
Publishing the Report
Managing the Report
Accessing the Report
Module 3: Enhanced Reporting
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”)
Module 4: Managing Datasets
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
Module 5: Managing Content
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
Configuring Snapshot Reporting
Snapshot Report History
• Viewing
• Configuring
Report Subscriptions
• Supports the push model of reporting
Cross Platform Reporting
• See web for fixes on your platform
• You must test all os/browser platforms
• Example: Firefox– Width: single pixel clear gif, width of 6.5 in– Height: modify reportingservices.css
• .DocMapAndReportFrame{min-height: 860px;}
Module 6: Administering SSRS
Reporting Services Files and Folders
• C:\program files\Microsoft SQL Server\MSSQL.3\Reporting Services
• C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config
Reporting Services Databases
• ReportServer.dbo.ExecutionLog
Trace Log Files
• C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles
• Configure logging level in the ReportinServices.exe.config file
SSRS Windows Performance Counters
• MSRS 2005 Web Service Counters
• MSRS 2005 Windows Service Counters
Timeouts
• Connection:– Connection Timeout– Command Timeout– Lock Timeout
• Report Execution Timeout
Suspending Jobs
• Subscriptions
• Shared Schedules
SSRS Database Administration
• Role of the database
• Backup Schedule
Security: Authorization Model
• SQL Server Logins– Trusted – Nontrusted
• Default Settings – trusted only– Need to modify for snapshot execution
Assigning Roles
• Windows Accounts
• Using “runas” for testing– Ctrl – right click internet explorer– Choose runas– Supply another windows login for execution
Item-Level Role Definitions
• Browser
• Content Manager
• My Reports
• Publisher
• Report Builder
Folder Security
Site Security
• System Administrator
• System User
Module 7: Programming SSRS
SSRS Extensibility
• Client Reporting Interface
• Management/Administration
• Custom Reporting Objects
Tools
• Reporting Services Command Prompt– Rs.exe– Rsconfig.exe
• Compilers:–Csc.exe
–Vbc.exe
HTTP Protocol
• HTTP Post
• HTTP Get
• WebForms
Custom Web Reporting Portal
• Add Parameters to url:– &rc:ParmName=ParmValue
• To Hide parameters– &rc:Parameters=false– &rc:Toolbar=false
• Other control features– &rc:Zoom=75– &rc:Zoom=Whole Page– &rc:Extension=.TXT– &rs:format=EXCEL
Reporting Services API
• .Net Libraries
• Web Services
Example Client Code public Form1() { InitializeComponent();
//add reference to Microsoft.ReportViewer.WinForms
//add namespaces System.Data.SqlClient and Microsoft.Reporting.WinForms;
this.ClientSize = new System.Drawing.Size(950, 600);
ReportViewer reportViewer = new ReportViewer();
// Set Processing Mode reportViewer.ProcessingMode =
ProcessingMode.Local;…
Example Contd.… // Set RDL file reportViewer.LocalReport.ReportPath = @"c:\tmp\
productlist.rdl";
// Supply a DataTable corresponding to each report data source
reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", DBUtil.GetProducts()));
// Add the reportviewer to the form reportViewer.Dock = DockStyle.Fill; this.Controls.Add(reportViewer);
// Process and render the report reportViewer.RefreshReport(); }