Microsoft sql server reporting services
1 / 44

Microsoft SQL Server Reporting Services - PowerPoint PPT Presentation

  • Updated On :

Microsoft SQL Server Reporting Services. Eric Nelson Application Architect Microsoft (SQL Dev and UK ISV). Reporting Services 101. SQL Server BI Platform. Reporting Services. Development Tools. Management Tools. Analysis Services OLAP & Data Mining.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Microsoft SQL Server Reporting Services' - zenobia

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Microsoft sql server reporting services l.jpg

Microsoft SQL Server Reporting Services

Eric Nelson

Application Architect

Microsoft (SQL Dev and UK ISV)

Sql server bi platform l.jpg
SQL Server BI Platform

Reporting Services

Development Tools

Management Tools

Analysis Services

OLAP & Data Mining




SQL Server

Relational Engine

What is sql server reporting services l.jpg
What is SQL Server Reporting Services?

  • Reporting platform for both traditional and interactive reports

  • Scalable and manageable server infrastructure

  • Integrated with SharePoint, Office applications, browser and other familiar tools

  • Single platform and tools for all types of structured data (relational, hierarchical, multidimensional)

Reporting lifecycle l.jpg




Reporting Lifecycle

Reporting Services is an open and extensible platform supporting the authoring, management and delivery of rich, interactive reports to the entire enterprise.

Report authoring l.jpg
Report Authoring

  • Reports are defined in Report Definition Language (RDL), a documented XML schema

  • Use Microsoft or 3rd party tools that support RDL

  • Create single reports from multiple data sources(SQL, OLE DB, ODBC, Oracle, and .NET data providers)

More info on the RDL spec:

Report management l.jpg
Report Management

  • Report definitions, folders, and resources are published and managed in a reporting web service

  • Managed reports can be executed either on-demand or via schedule and can be cached for consistency and performance

  • Scalable & Extensible server architecture

Report delivery l.jpg
Report Delivery

  • Traditional (paper) and interactive (web) reports

  • On-demand (“pull”) or event-based (“push”) delivery

  • Choose from multiple formats (HTML, Excel, PDF, XML)

  • Deliver reports to many devices (e-mail, file share, etc.)

Release roadmap l.jpg
Release Roadmap

  • SQL Server 2000 Reporting Services SP1

    • Bug Fixes, Scalability, Excel 2000 support

  • SQL Server 2005 Beta 2

    • Parity with SQL2K SP1 version

    • Cross SQL Server (AS, Management) integration

  • SQL Server 2000 Reporting Services SP2

    • Web Parts, Client Printing

  • SQL Server 2005 April, June, Sept CTP

    • New Features, 64 Bit support, Report Builder

  • SQL Server 2005 RTM – very soon…

Themes l.jpg

  • Core Product Enhancements

    • Extend features based on feedback from SQL2K version

    • Incremental enhancements, performance and scalability

  • Tighter Integration

    • Strengthen SQL Server end-to-end BI scenarios

    • Tighter integration with Office and SharePoint

  • Developer Focus

    • Integration for the Visual Studio developer

    • Freely redistributable Windows and ASP.NET controls

  • Empowering End Users

    • Report Builder enables end-user reporting scenarios

    • Increased interactivity

What s new in 2005 l.jpg

Printing from IE (SP2)

Expression builder enhancements

Multi-valued parameters

Parameter Date Picker

Interactive Sort

Floating headers

MDX Query builder

Custom Report Item

Management Studio Integration

Configuration Tool

Web Parts (SP2)

VS Integration

VS Controls

64 bit Support

Multi-instance Support

Report Builder

What’s new in 2005

Rich client printing l.jpg
Rich Client Printing

  • Direct routing of print jobs without needing to export

  • Small ActiveX control downloaded via browser

  • Select page range, orientation, margins

  • Preview page output

  • Server enhancements for performance

Enhanced expression editor l.jpg
Enhanced Expression Editor

  • Available for all expressions in VS Report Designer

  • List of available VB.NET functions

  • Inline parameter information

  • Statement completion

  • Real-time syntax checking

Multi valued parameters l.jpg
Multi-Valued Parameters

  • Report developer can specify any parameter is multi-valued

  • Report processing constructs SQL or MDX syntax

  • Multiple default values also supported

  • Value sets can passed via URL or SOAP API

Date picker parameters l.jpg
Date Picker Parameters

  • Calendar control for choosing dates

  • Localized and Globalized

  • Windows and Web support

Interactive sort l.jpg
Interactive Sort

  • Report author can add ability for end user to resort data within data regions

  • Resorting happens without re-querying the database

  • Support for multi column and for nested / grouped sort

Floating headers l.jpg
Floating Headers

  • New type of interactivity similar to freeze panes in Excel

  • Works Horizontally and Vertically

  • Table header

  • Matrix column and row headers

  • Table columns

    • Must be continuous columns anchored on the right or left

Mdx query builder l.jpg
MDX Query Builder

  • MDX and data mining query builders

  • MDX parameter support

  • Member extended properties

  • Support for server aggregates

Custom report item l.jpg
Custom Report Item

  • Enables ISVs and Developers to add custom visualizations to a report

  • Primary scenario is enhanced charting

    • Additional chart types

    • Maps

    • Gauges

  • Supports interactivity

    • Actions

    • Sort

  • Supports all data region features

    • Filter

    • Grouping

    • Group Filters

    • Etc…

  • The Report Designer supports

    a conversion API

  • Currently limited to images

    • Future plans for all report item types

Management studio integration l.jpg
Management Studio Integration

  • Single point of management for all SQL Server components

  • Superset of Report Manager functions

  • Script generation from property dialogs

Server configuration tool l.jpg
Server Configuration Tool

  • Graphical configuration tool for Report Server

    • Database Settings

    • Virtual Directories

    • E-Mail

    • Key Management

Sharepoint web parts l.jpg
SharePoint Web Parts

  • Report Explorer provides browsing of server namespace and subscription

  • Report Viewer used to view reports

  • Parts can be connected or used standalone

  • Works in both SPS and WSS

Visual studio integration l.jpg
Visual Studio Integration

  • Report design completely integrated with Visual Studio language projects

  • Natural extension of VS data functionality

  • Included in VS Pro and above

Report controls l.jpg
Report Controls

  • Controls make it easy to embed reporting functionality into applications

  • Windows Forms (rich client) and Web Forms (ASP.NET) control

  • Local processing mode (no server) or connected server mode

Items not in 2005 sorry l.jpg
Items Not in 2005 – sorry

  • Rich Text support

  • RTF/Doc rendering

  • PPT rendering

  • Shared Styles (Styles sheets)

  • Templates

  • Font embedding in PDF

  • Many Chart enhancements

  • Heterogeneous Join

  • Edit in Preview

Report builder l.jpg
Report Builder

  • A new ad-hoc report design tool for Reporting Services

  • Targeted at business users who want to find and share answers to interesting questions

  • Driven from a business model of the data so users do not need to understand the underlying data structures

  • Fully integrated with Reporting Services and delivered in SQL Server 2005

Why report builder l.jpg

Report Consumers

Business Users

Power UsersDevelopers

Why Report Builder?

Report Viewer

Report Builder

Report Designer

What report builder is not l.jpg
What Report Builder is not…

  • A replacement for Pivot Tables

    • Can be used for some data slice and dice but not specifically designed for this capability

  • An analytical client

    • No support for advanced financial calculations, what ifs, writebacks, financial or business modeling, etc.

  • A developer-focused design tool

    • No nested data regions, freeform layout, multiple queries, conditional formatting, or custom code

  • An HTML-based design tool

    • Report Builder is a WinForms (.NET Framework) application

Report builder architecture l.jpg
Report Builder Architecture

SQL Mgmt








Report Builder


Web Service / URL interface

Report Server

Drill through report generation

Query generation

Data Sources

(SQL Server,

Analysis Services)

SQL Server Catalog

Report builder client l.jpg
Report Builder Client

  • Built on top of familiar Microsoft Office paradigms (Excel, PowerPoint)

  • Reports built via report templates (table, matrix, chart, combinations)

  • “Click once” application deployed from the Report Server

  • Users download and launch the client from Report Manager (new or existing report)

  • Finished reports can be saved on the server

Report builder reports l.jpg
Report Builder Reports

  • Report Builder reports are just like any other Reporting Services report…

    • Report Builder reports are defined in RDL

    • Managed by the same APIs

    • Secured by the same APIs

  • Except: Report Builder reports contain semantic model metadata

    • When an Report Builder report is requested by report processing, the metadata is used to generate a query in the source language

    • A new component on the Report Server handles the query generation

Infinite drill l.jpg
Infinite Drill

  • Automatic generation of new drillthrough reports allows users to explore data along model navigation paths

  • Queries for navigation reports are generated on the fly, passing the context of the user’s current “location”

  • Users can keep drilling as long as there is a path to follow from the current item

  • Custom reports can be specified for model items

  • Generated navigation reports are not saved unless the user explicitly saves them

Report builder model designer l.jpg
Report Builder Model Designer

  • Hosted in Visual Studio (new project type)

  • For editing models used by Report Builder

  • Auto-generation of models from data sources

Model design l.jpg
Model Design

  • Model Designer is interface for designing/editing a model

    • New project type in BI Development Studio

    • Define fields and roles

    • Define calculated fields

    • Organize model

  • Models can be auto-generated in Model Designer or on the Report Server

    • Generates entities, fields and roles based on source metadata

    • Auto-generation against SQL Server and Analysis Services databases

Report models l.jpg
Report Models

  • Models define entities, fields and roles available for Report Builder

  • Models reference a data source (either SQL Server or Analysis Services)

  • Models are stored in the Report Server as a new item type

    • Includes new management APIs

  • Published models can be secured

    • Items within models (entities, fields, roles) can also be secured

    • Users only see items they have permissions to see

  • Model security and management through SQL Server Management Studio

Wrap up l.jpg

  • Reporting Services in SQL Server 2005

    • We have listened to feedback – smoothed off the rough edges

  • Report Builder is…

    • a new ad hoc report design tool for SQL Server Reporting Services

    • targeted at business users who want to find and share answers to interesting questions

    • driven from a business model of the data so users do not need to understand the underlying data structures

    • is fully integrated with SQL Server Reporting Services and delivered in SQL Server 2005

Resources l.jpg

  • Product site


  • How to license


  • Articles:

    • Intro to Report Builder

  • Webcasts:

    • Report Builder on MSDN TV

    • Report Builder linked from

    • Reporting Services

  • Books

    • Hitchhikers Guide to SQL Server 2000 Reporting Services – excellent!

  • Pubs and NorthWind

Slide44 l.jpg

© 2004 Microsoft Corporation. All rights reserved.

This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.