Transcript Microsoft SQL Server Reporting Services

Microsoft SQL Server
Reporting Services
Dave Henson
[email protected]
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\ReportingServi
cesService.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();
}