SQL Server Reporting Services: - PowerPoint Presentation

Slide1

SQL Server Reporting Services: Develop & Deploy Reports

Anil Desai

http://AnilDesai.netSlide2

Speaker InformationAnil Desai

Independent Consultant (Austin, TX)

Author of several SQL Server books

CertificationTrainingInstructor, “Implementing and Managing SQL Server 2005” (Keystone Learning)Info: http://AnilDesai.net or Anil@AnilDesai.net Slide3

Administering Reporting Services

Administration Overview

Reporting Services Architecture

Installing and Configuring Reporting ServicesAdministering Reporting ServicesManaging Reports and Data Sources

Advanced AdministrationConfiguring Report Execution and Caching

Creating Snapshots and Report HistoryManaging SubscriptionsManaging Report SecuritySlide4

Reporting Services ArchitectureFeatures and components of Reporting ServicesSlide5

Reporting Life CycleSlide6

Part of the SQL Server PlatformXML-based Report Files (.rdl)Report Development

Visual report design

Business Intelligence Development Studio (BIDS)

Report Builder 2.0 / 3.0Report FeaturesGroupingSortingFilteringDrill-Down and Drill-ThroughCharting

Reporting Services FeaturesSlide7

Report TypesTable, Matrix, Charts, etc.

Report

output:

Report Viewer (web site)Page-based (HTML, TIFF, PDF)Application integration (Web / Windows Forms)Exports: Microsoft Excel

Text files (CSV, TSV)Adobe PDFXMLReporting Services FeaturesSlide8

Application Programming Interface (API)Report Viewer control for Windows FormsReport Viewer control for

ASP.NET

Web

Services API / SOAP SupportCustom Application DevelopmentWeb and Windows Forms Report Viewer controlsSSRS 2008+ uses its own web server (no IIS)Deployment Methods:Native mode

SharePoint-integrated modeServer farm (distributed) configurationReporting Services

FeaturesSlide9

From SQL Server Books Online

Reporting Services ArchitectureSlide10

Report Part GalleryShared data sourcesText rotation (for long column headers)Mapping and spatial data visualization

New Platform Features / Tools

Self-Service Business Intelligence

Master Data ManagementSharePoint 2010 SupportPowerPivot for Excel 2010SSRS 2008 R2: New FeaturesSlide11

Reporting Services Architecture

From

www.microsoft.com/sql

Slide12

SQL Server Reporting Services ServiceReport Manager Web SiteReporting Creation

SQL Report Builder 2.0

Visual Studio 2008 Report Designer

Databases:ReportServer: Report definitions, security settings, etc.ReportServerTempDB:Cached data and user session information

Reporting Services ComponentsSlide13

Part of the SQL Server Setup ProcessDeployment ModesNative modeSharePoint Integrated mode

Native Mode with SharePoint Web Parts

Verifying the installation

Event Viewer: Application LogOptions in RSReportServer.config fileInstalling Reporting ServicesSlide14

Configuring Reporting ServicesSlide15

SQL Server Management StudioServer Type: “Reporting Services”Microsoft Visual Studio 2008 SP1

Can deploy reports and data sources

Can choose server and folder names for deployment

Command-line optionsRS.exeRSConfig.exe

Administration MethodsSlide16

Scale-Out DeploymentsSlide17

Managing ReportsWorking with report items and defining data access methodsSlide18

Primary administration methodConfigure site settingsManage reports and data sourcesSecurity configuration

View reports

Connecting to the Report Manager Web Site

Requires a DHTML-compatible browserDefault: http://ComputerName/reports

Report Manager Web SiteSlide19

Report Definition Language (.rdl)XML-based report files

Contains report layout and other details

Data sources

Queries / stored procedure callsParametersReports can be deployed or uploadedCan be organized in foldersUnderstanding ReportsSlide20

Using Visual StudioDeploy a single report or data sourceDeploy the entire project

Project Deployment options:

OverwriteDataSources

TargetDataSourceFolderTargetReportFolderTargetServerURLUploading Reports.RDL files can be uploaded through the web siteCan overwrite a current report to retain all settings

Deploying ReportsSlide21

Developing SSRS ReportsReview of modules and resources for more informationSlide22

Report Wizard Goals:Provides a quick way to create basic reportsDefines a data connection and queryIncludes formatting and grouping options

Creates a new RDL file

Launching the Report Wizard:

New Project  Report Server Project WizardAdd Item  Report Wizard

Using the Report WizardSlide23

Report Wizard StepsSlide24

Creating Data SourcesAccess data sources using Reporting ServicesSlide25

Specifies connection information for reporting dataSupported Data Sources:Any OLEDB / ODBC-compliant data sourceRelational

SQL Server

Oracle

MS AccessOLAP / Multi-DimensionalSQL Server Analysis ServicesXML, Excel, CSV, TSV, etc.Understanding Data SourcesSlide26

Data Source DetailsData source typeConnection options

Security credentials

Private Data Sources (Report-specific)

Stored within the report (.RDL) fileShared Data SourcesDefined at the Project / Server levelCan be used across multiple reportsUseful for development/production environments

Creating Data SourcesSlide27

Creating DatasetsSpecifying information to be included in a reportSlide28

Identifies data to be used for report generationCan have many different datasets per reportRequires a data source (shared or embedded)Fields are available for use in reports

Dataset Options

Query (Text or Stored Procedure)

FieldsData OptionsParametersFiltersDataset DetailsSlide29

Query Designer FeaturesVisual creation of joinsCan access tables, views, and functionsColumn names and aliases

Query sorting and filtering options

Query results

Screen sectionsDiagram PaneGrid PaneSQL PaneResult PaneQuery DesignerSlide30

Query Designer ExampleSlide31

Report Requirements:AdventureWorks Products by Category

Report

Retrieve information about Categories, Subcategories, and Products

Tables:Production.ProductCategoryProduction.ProductSubcategoryProduction.ProductCreating a DatasetSlide32

Report Design: LayoutCreating and laying out new reportsSlide33

ReportPage HeaderPage FooterBody (Report Area)

Table Regions

Header

DetailFooterGroupsPage breaksSummaries / TotalsReport LayoutSlide34

Report Items (Toolbox)Slide35

Report Requirements:Show a list of all products by Category / SubcategoryDrill-down, sorting, and grouping are not required

Report Components:

Page Header

Report TitlePage NumberReport Data (Table)Report Layout: DemonstrationSlide36

Deploying and Viewing ReportsPublishing reports to the Reporting Services web siteSlide37

Project Properties:OverwriteDataSourcesTargetDataSourceFolder

TargetReportFolder

TargetServerURL

Deployment OptionsEntire ProjectSingle report / data source itemPublishing ReportsSlide38

Interacting with ReportsExporting Data

Viewing ReportsSlide39

Report Design: Adding Interactivity

Sorting, Grouping, and Drill-DownSlide40

Query SortingUseful for setting a “default” sort orderUse an ORDER BY clause in the dataset queryTable-Level Sorting

Default sort order specified in the “Sorting” tab

Interactive Sorting

Data is sorted during report generationSorted values are used for report outputCan use a field or complex sort expressionMay be dependent on grouping scopeInteractive SortingSlide41

GroupingHelps to logically organize dataCan create sub-totals in group footer

Drill-Down

Group visibility can be dynamically-controlled by other columns/values

Report exports are based on the current viewGrouping and Drill-DownSlide42

Grouping ExampleSlide43

Statements used to specify valuesCan be used in table cellsExpression EditorSupports

Intellisense

Uses Visual Basic-style syntax

Examples:Globals!ReportNameGlobals!PageNumber

Sum(Fields!SalesTotal.Value, “Sales")

CountDistinct(Fields!ProductCategory

)Fields!Employee.LastName + “,” +

Fields!Employee.FirstName +

Understanding ExpressionsSlide44

Expression OptionsSlide45

Expression Options (cont’d.)Slide46

Filtering Report DataUsing Parameters to filter reporting dataSlide47

Dataset / Query LevelUses parameter variables to restrict data returnedCan also use stored procedure variablesReport Parameters

Determined at report run-time

Useful when users will be frequently changing settings

Object FilteringFilter options for tables, charts, etc.Filtering OptionsSlide48

Can improve performance by minimizing data returnedBest used when filtering details are known before report generationImplemented using query parametersVariables:

@

StartDate

, @EndDateQuery:

SELECT * FROM Sales WHERE TransactionDate

BETWEEN @

StartDate AND

@EndDate

Dataset FilteringSlide49

Evaluated at report run-timeReport Parameter Options:Data TypesPrompt Options

Allow blank / null; Multi-value

Available Values

Non-Queried or From QueryDefault values:Non-Queried or From QueryCascading ParametersReporting ParametersSlide50

Report Design: Adding Charts

Adding data visualization through Chart objectsSlide51

Understanding ChartsCan be based on any datasetDisplay and options are based on chart type

Chart Features

X- and Y-Axis Labels

Legends3-D EffectsFiltersChart TypesSlide52

Chart TypesSlide53

Designing Charts:Data FieldsSeries FieldsCategory Fields

Chart Example:

AdventureWorks

Sales DataRequirement: Show sales by region and date in a variety of different waysDesigning ChartsSlide54

Report Design: Using Subreports

Accessing related data with

SubreportsSlide55

Embedded ReportsMay be related to the “parent” reportPurposes

Master / Detail view of data

Flexible layout and display options

DashboardsDrill-Through (using hyperlinks)Complex ReportingUnderstanding SubreportsSlide56

Advanced Report AdministrationSlide57

Configuring Report Execution and CachingSpecifying how and when reports are runSlide58

Report Execution ProcessSlide59

Always run this report with the most recent dataEnable cachingExpired based on number of minutesExpired based on a schedule

Render report from a snapshot

Report Execution timeouts

System DefaultSpecified number of secondsNoneReport Execution OptionsSlide60

Cache is created when a report is first runStores a copy of data in ReportServerTempDBCan reduce impact on production performance

Data may be out-of-date

Expires after a pre-defined amount of time

Data source security settings must be configuredUnderstanding Report CachingSlide61

Query ParametersEach combination of parameter values results in a separate stored databaseCan use a large amount of disk space

Report Parameters

Creates a single cached instance of the report

Caching and Report ParametersSlide62

Events are executed by SQL Server Agent serviceSchedule Types

Report-Specific Schedules

Shared Schedules

Defined at the system levelTips:Keep track of time zonesUse shared schedules whenever possible to allow centralized managementDistribute reporting processing workload over timeUnderstanding SchedulesSlide63

Creating Snapshots and Report HistoryCreating point-in-time views of data and storing them for later reviewSlide64

Point-in-time view of the contents of a reportData never changesReport parameters must be defined before running the snapshotUsually created on a schedule

End-of-month or end-of-year reports

Scheduling

Report-specific scheduleShared scheduleUnderstanding SnapshotsSlide65

Used to maintain snapshot copies over timeOften used for auditing or historical referenceScheduling:Store all snapshots

Use a report-specific schedule

Use a shared schedule

Options:Keep an unlimited number of snapshotsLimit the number of copies of report historyReport HistorySlide66

Managing SubscriptionsGetting data to users when and how they want itSlide67

E-MailUses SMTP server defined in Reporting Services Configuration toolCan send report as attachmentCan send a link to the report

File Share

Stores the output of a report to a file share

Requires a shared folder accessible via UNCExample: \\ReportServer\MarketingReportsReport Delivery OptionsSlide68

Output file typesXMLComma-separated values (CSV) – text fileTIFF image files

Web Archive

Adobe Acrobat (PDF)

Microsoft Excel (XLS)File Share OnlyWeb Page (HTML)Web ArchiveReport Delivery OptionsSlide69

Snapshot-Based SubscriptionsNotification is sent whenever a snapshot is createdSchedule-Based SubscriptionsUses a custom schedule (e.g., daily, monthly, etc.)

Can have start and stop dates

Data-Driven Subscriptions

Report recipients are defined by a queryTable and query must be created manuallyUseful when managing large or very dynamic lists of recipientsSubscription TypesSlide70

Managing Report SecurityConfiguring system-level and report-level permissionsSlide71

Hierarchical Security ModelFolders can be used for logical organizationItems inherit permissions

Security Layers

System-Level Role Definitions

Site-wide SecurityItem-Level Role DefinitionsReporting Services SecuritySlide72

Role-Based systemRoles are sets of permissions/capabilitiesUsers can be assigned to multiple roles

Based on Windows Authentication

Provides for centralized security management

May use Active Directory users and groupsOther authentication can be developedManaging SecuritySlide73

Roles include collections of tasksPre-Defined Roles:BrowserContent Manager

My Reports

Publisher

Report BuilderSecurity RolesSlide74

Available Tasks:Consume ReportsCreate linked reports

Manage all subscriptions

Manage data sources

Manage foldersManage individual subscriptionsManage modelsManage report historyManage reportsManage resourcesSet security for individual itemsView data sources

View foldersView modelsView reportsView resources

Creating Custom RolesSlide75

Creates a “virtual report” Uses the same report definition (.rdl) as the parent report, but with independent settings

Purpose / Benefits

Can setup different sets of permissions

Can setup different sets of parametersLinked ReportsSlide76

Give users minimal permissionsImplement “defense-in-depth”

Regularly review permissions

Delegate security review responsibilities

Make security reviews a part of your overall processEnsure that Windows groups and users are properly definedReporting Services Security Best PracticesSlide77

Course SummaryResources for more informationSlide78

AnilDesai.netPresentation slidesSQL Server-focused articles

Sample code from presentations

ReportingServicesGuru.com

Course: “Administering Reporting Services”Online forums and newsMicrosoft Resources:SQL Server Web Site: www.microsoft.com/sqlReporting Site: http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

Microsoft Developer Network: msdn.microsoft.comMicrosoft TechNet: technet.microsoft.com

SQL Server 2008 R2 Reporting Services ForumsSQL Server Product Samples: http://msftrsprodsamples.codeplex.com/

For Further Information