Microsoft SQL Server Reporting Services - Dave Henson

Microsoft SQL Server Reporting Services - Dave Henson

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



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; 600);

this.ClientSize = new System.Drawing.Size(950, 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(); }

Recently Viewed Presentations

  • Intercultural Communication Competence

    Intercultural Communication Competence

    Intercultural Communication Competence "The degree to which you effectively adapt your verbal and nonverbal messages to the appropriate cultural context" (Neuliep, 2006, p. 441) Intercultural Competence Qualities Verbal and Nonverbal Appropriateness Communicating with an understanding of the rules, norms, and...
  • Presentation Title - PNSQC

    Presentation Title - PNSQC

    Add your second bullet point here. Add your third bullet point here. SOFTWAREQUALITYCONFERENCE. PACIFIC NW. PNSQC ™ Title of Slide. SOFTWAREQUALITYCONFERENCE. PACIFIC NW. Slide Content Here ….. Copy this slide as many times as needed for presentation content = =...
  • World Bank - GPRBA

    World Bank - GPRBA

    Arial Palatino Linotype Wingdings Worldwide design template Feuille de calcul Microsoft Office Excel Morocco: OBA Subsidies to Water & Sanitation Connections in Poor Peri-urban Areas Background: Lydec Slide 3 Types of low income settlements The GPOBA pilot Value added of...
  • Managing the Implementation Process: Diagnosing ...

    Managing the Implementation Process: Diagnosing ...

    Managing the Implementation Process: Diagnosing Organizational Performance Problems Professor Robert Bontempo The Graduate School of Business Columbia University Our agenda for today How we usually diagnose problems What is a systems problem? ... e.g. research vs. technical service job shop...
  • sdfsdf - Tirian

    sdfsdf - Tirian

    T H I R D D I M E N S I O N™ PROGRAM "Developing people & processes to improve organizational effectiveness"
  • Costa Concordia tragedy  ship displacement monitoring system On

    Costa Concordia tragedy ship displacement monitoring system On

    "Costa Concordia" tragedy - ship displacement monitoring system On 13/01/2012 at Giglio island a 400 long ship "Costa Concordia" having about 4000 people on board shipwrecked. Since then, it has been constantly moving downward the sea bottom.


    Dental update 2001. the restorative state of the tooth. presence and condition of the other teeth. If any are absent, extraction of the first permanent molar in that quadrant should be avoided. ... Seven children were or should be subjected...
  • Case for Change: Overhead Lines

    Case for Change: Overhead Lines

    Over 380 personnel have been trained in Well Engineering. Personal Action Plans are followed up and verified onsite by Delivery Team Leads. SAFETY LEADERSHIP FOR FRONTLINE SUPERVISORS. 1. 2. 4 new PPE contracts have been awarded to support the growing...