sql reporting services from a developers perspective adam calderon principal engineer interknowlogy...

SQL Reporting ServicesSQL Reporting Services

From a Developers From a Developers PerspectivePerspective

Adam Calderon

Principal Engineer

Interknowlogy LLC

adamc@interknowlogy.com

http://blogs.interknowlogy.com/adamcalderon/

AgendaAgenda

Introducing Reporting ServicesIntroducing Reporting Services

2005 Enhancements2005 Enhancements

Reporting Services Component OverviewReporting Services Component Overview

Anatomy of a ReportAnatomy of a Report

Designing and Creating ReportsDesigning and Creating Reports

Integrating Reports with ApplicationsIntegrating Reports with Applications

Advance ReportsAdvance Reports

What is Reporting Services ?What is Reporting Services ?

Sever based reporting platformSever based reporting platform

Complete set of tools to create, manage Complete set of tools to create, manage and view reportsand view reports

Variety of output formats HTML, PDF, Variety of output formats HTML, PDF, TIFF, Excel, CSV and customTIFF, Excel, CSV and custom

Reports can be based on relational and Reports can be based on relational and multidimensional datamultidimensional data

Exposed APIExposed API

Reporting Services ScenariosReporting Services Scenarios

Business ReportingBusiness Reporting

Ad hoc ReportingAd hoc Reporting

Integrating Reporting Services in Custom Integrating Reporting Services in Custom ApplicationsApplications

Data ReportingData Reporting

Intranet ReportingIntranet Reporting

Building Custom Report Design and Building Custom Report Design and Management ToolsManagement Tools

Extending Reporting Services FunctionalityExtending Reporting Services Functionality

Report Functionality EnhancementsReport Functionality Enhancements

Interactive Sorting in ReportsInteractive Sorting in Reports

Printing ReportsPrinting Reports

Creating Ad Hoc ReportsCreating Ad Hoc Reports

Mutivalued ParametersMutivalued Parameters

SharePoint Web PartsSharePoint Web Parts

Design-Time EnhancementsDesign-Time Enhancements

New Model DesignerNew Model Designer

Report Designer EnhancementsReport Designer Enhancements– Expression Editor EnhancementsExpression Editor Enhancements– Expression-based Data SourcesExpression-based Data Sources– New Analysis Services Query DesignerNew Analysis Services Query Designer– Integration Services EnhancementsIntegration Services Enhancements

Report Definition EnhancementsReport Definition Enhancements

Reporting Services Reporting Services Programmability EnhancementsProgrammability Enhancements

New SOAP Endpoints for the Report New SOAP Endpoints for the Report Server Web ServiceServer Web Service– Management EndpointManagement Endpoint– Execution EndpointExecution Endpoint

New Report Viewer Controls in Visual New Report Viewer Controls in Visual Studio 2005Studio 2005

Reporting Services ComponentsReporting Services Components

Report ServerReport Server– Report ProcessorReport Processor– Scheduling and Delivery ProcessorScheduling and Delivery Processor– Report Server DatabaseReport Server Database– ExtensionsExtensions

Report ManagerReport Manager

Report BuilderReport Builder

Report DesignerReport Designer

Model DesignerModel Designer

Configuration ToolConfiguration Tool

Component ArchitectureComponent Architecture

Report Server DatabaseReport Server Database

Report ServerReport Server

Report Processor

Programmatic InterfacesProgrammatic Interfaces

Data RetrievalData Retrieval RenderingRendering

DeliveryDelivery SecuritySecurity

Schedule and

Delivery Processor

BrowserBrowser ReportReport Builder Builder

(new)(new)Report Report

ManagerManager

ReportReportDesignerDesigner

ModelModelDesignerDesigner

(new)(new)

ReportingReportingServicesServices

ConfigurationConfigurationToolTool(new)(new)

Third-PartyThird-PartyToolsTools

Quick Tour of Reporting ServicesQuick Tour of Reporting Services

Components of a ReportComponents of a Report

Data SourcesData Sources

DataSetsDataSets

FieldsFields

Data RegionsData Regions

Report ItemsReport Items

Report Definition Language (RDL)Report Definition Language (RDL)

Report DataSourcesReport DataSources

SQL Server Data Processing ExtensionSQL Server Data Processing Extension

Analysis Services Data Processing ExtensionAnalysis Services Data Processing Extension

Oracle Data Processing ExtensionOracle Data Processing Extension

OLE DB Data Processing ExtensionOLE DB Data Processing Extension

ODBC Data Processing ExtensionODBC Data Processing Extension

XML Data Processing Extension XML Data Processing Extension (New to 2005)(New to 2005)

Data Processing Extensions retrieve the data that is used in reports. The following are build-in extensions.

Report Data RegionsReport Data Regions

TableTable

MatrixMatrix

ListList

ChartChart

Nested Data RegionsNested Data Regions

Report ItemsReport Items

Text BoxText Box

ImageImage

LineLine

RectangleRectangle

SubreportSubreport

Item PropertiesItem Properties

Report Definition Language (RDL)Report Definition Language (RDL)

Set of instructions that describe layout and Set of instructions that describe layout and query information for a reportquery information for a report

Composed of XMLComposed of XML

Extensible and open schema that supports Extensible and open schema that supports additional namespace and custom additional namespace and custom elementselements

Creating Reports Creating Reports

Report Control (Remote Mode)Report Control (Remote Mode)

Report Server DatabaseReport Server Database

Report ServerReport Server

Report Processor

Programmatic InterfacesProgrammatic Interfaces

Data RetrievalData Retrieval RenderingRendering

DeliveryDelivery SecuritySecurity

Schedule and

Delivery Processor

Report ControlReport Control

Custom Application

Report Control (Local Mode)Report Control (Local Mode)

SQL DatabaseSQL Database

Custom ApplicationCustom Application

Report Processor

Report Control

Resource File (rdl)

DataSet

Integrating Reports with ApplicationsIntegrating Reports with Applications

Advanced ReportsAdvanced Reports