TNT1105

1
TNT1-105
2
Microsoft SQL Server 2000 Reporting Services
Microsoft Corporation
3
What we will cover

• Architecture
• Creating reports
• Importing reports
• Publishing reports
• Viewing reports
• Subscriptions

4
Prerequisite Knowledge

• Experience administering SQL Server
• Experience administering IIS
• Experience generating database reports
• Familiarity with Visual Studio.NET

Level 300
5
Agenda

• Architecture and Deployment
• Creating Reports
• Managing Reports

6
What is SQL Server Reporting Services?

• Reporting platform for both traditional and
  interactive reports
• Scalable, manageable and embeddable 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)

7
SQL Server BI Platform
Reporting Services
Management Tools
Development Tools
Analysis Services OLAP Data Mining
Data Transformation Services ETL
SQL Server Relational Engine
8
Reporting Lifecycle
Reporting Services is an open and extensible
platform supporting the authoring, management and
delivery of rich, interactive reports to the
entire enterprise.
9
Architecture and DeploymentIntroduction to
Reporting Services

10
Architecture and DeploymentArchitecture and
Components
11
Architecture and DeploymentReport Processing
12
Architecture and DeploymentReporting Services
Client Components

• Report Designer
• Integrated with Visual Studio.NET 2003
• Web Browser
• View and Manage Reports
• Third Party Tools
• Management
• Authoring
• Supported through open interfaces or Web Services
  API

13
Architecture and DeploymentReport Authoring
Report Definition
Report resources published/managed as Web Service
Report resources published/managed as Web Service
Create Reports with RDL
Create Reports with RDL
Supports pull and push delivery of reports
14
Architecture and DeploymentSmall Deployment
15
Architecture and DeploymentMedium Deployment
16
Architecture and DeploymentLarge Deployment
17
Agenda

• Architecture and Deployment
• Creating Reports
• Managing Reports

18
Creating ReportsVisual Studio .NET Integration
19
Creating ReportsReport Definition Language (RDL)

• Report Definition Language XML schema contains
• Data source information
• Layout
• Report Properties
• Definitions stored in Report Server Database
• Can create tools that use RDL

20
Creating ReportsReport Creation Process

• Connect to the data source
• SQL Server, Analysis Services, Oracle, ODBC and
  OLE DB
• Specify the query
• Arrange the report items on the page
• Page header, page footer and body
• Preview the results
• Publish the report

21
Report Items

• Textbox
• Image
• Line
• Rectangle
• Subreport
• Data Regions (List, Table, Matrix, Chart)
• Custom Report Items coming in SQL Server 2005!

100,000
22
Item Styles

• All Report Items
• Background Color
• Background Image
• Border Color
• Border Style
• Border Width
• Padding
• Charts
• Background Gradient

• Textboxes
• Color
• Font Family
• Font Size
• Font Style
• Font Weight
• Format
• Line Height
• Text Align
• Text Decoration
• Vertical Align

23
Lists

• Repeating regions with freeform layout
• Similar to bands in other report writers
• But multiple lists can sit side-by-side!
• Lists inside lists to get multiple levels of
  grouping
• Use for complex repeating areas

Brian
10,000
Jason
20,000
Steve
30,000
24
Tables

• Like lists with columns
• Fixed columns, repeating rows
• Table cells can span columns
• Multiple table rows per record
• Table cells can contain any report item
• Faster and smaller than lists
• Easier to align items
• Use for most reports

Sales
Region
Person
10,000
North
Brian
20,000
North
Dan
30,000
North
Total
50,000
East
Steve
50,000
East
Total
80,000
Grand Total
25
Matrices

• Repeats in row and column directions
• Also known as pivot table or cross tab
• Arbitrary formatting in headers
• Different formatting for subtotals
• Matrix cells can contain any report item
• Use when column headings are records in your data

2002
2001
2000
Sales
120
110
100
Brian
300
250
200
Jason
300
400
300
Steve
720
760
600
Total
26
Charts

• Data from fields in the data set
• or from expressions
• Full set of chart properties and types
• Style enhancementsin SP1
• Same data functionality as Matrix

27
Creating ReportsPreviewing and Publishing Reports

• Debug Preview
• View reports in local preview window
• Production Publish
• Publish reports to a production report server

28
demonstration
Creating Reports
29
Agenda

• Architecture and Deployment
• Creating Reports
• Managing Reports

30
Managing ReportsReport Manager

• View, Search and Subscribe to Reports
• Create and manage
• Folders
• Linked reports
• Report history
• Schedules
• Data source connections
• Subscriptions
• Set properties and report parameters
• Manage role definitions and assignments

31
Managing ReportsReport Manager
32
Managing ReportsViewing Reports

• Reports can be rendered in
• HTML
• HTML with Office Web Components
• XML
• CSV
• Excel
• Image such as TIFF
• Acrobat PDF file

33
Managing ReportsViewing Reports

• Reports can be rendered in
• HTML
• HTML with Office Web Components
• XML
• CSV
• Excel
• Image such as TIFF
• Acrobat PDF file

34
Managing ReportsReport Subscriptions
35
Managing ReportsReport Execution Processing

• On Demand
• On Demand From Cache
• Removed from cache at defined intervals
• From Snapshots
• Query is separate from render

36
Managing ReportsSecuring the Report Server and
Reports

• Reporting Server Security
• Uses IIS security
• Windows security
• User Permissions
• Windows security
• Role-based authorization

37
URL Parameter Directives

• Syntax
• http//server/virtualroot? /reportpath
  prefixparam
• Report Server Parameters (rs)
• http//localhost/reportserver?/SampleReportsrsCo
  mmandListChildren
• http//localhost/reportserver?/SampleReports/Emplo
  yeeSalesSummaryrsCommandRenderEmpID26
• http//localhost/reportserver?/SampleReports/Prod
  uctLineSalesrsFormatExcelrsCommandRender
• Rendering Device Info (rc)
• Name/Values pairs specifying information about
  the format being rendered to (i.e., FindString,
  Section, Zoom, etc.)
• Example find 7th page
• http//localhost/ReportServer?/SampleReports/Prod
  uctCatalogrcSection7rcHTMLFragmenttrue

38
demonstration
Managing Reports
39
Reporting Services Summary

• Supports the full reporting life cycle
• Highly scalable
• You do not need to know RDL to create a report
  definition
• Supports several data sources
• Reports can be rendered in a variety of formats
• Reports can be executed on demand or cached for
  quicker access

40
For More Information

• Main TechNet Web site at www.microsoft.com/technet
  

41
For More Information

• Microsoft SQL 2000 Reporting Services
• www.microsoft.com/sql/reporting
• Reporting Services Tech Overview
• www.microsoft.com/sql/reporting/techinfo

42

• Developer Productivity
• .NET Framework hosting
• Native XML technology
• Integrated web services
• Distributed application framework

• Business Intelligence
• Comprehensive ETL platform
• Real time analytics
• Accessible, easy data mining
• Rich, integrated reporting

• Enterprise Data Management
• Flexible, manageable, scalable
• Improved serviceability
• Self optimization and tuning
• Fast recovery and restore

• Secure, Quality Database
• 3 years in development
• Multiple security reviews
• Secure by default
• Large private beta for early quality

43
MS PressInside information for IT Professionals
To find the latest titles, visit www.microsoft.com
/learning/books/
44
3rd Party PublicationsSupplementary publications
for IT Pros
These books can be found and purchased at all
major book stores and online retailers
45
Microsoft LearningTraining Resources for IT
Professionals

• Creating Reporting Solutions using Microsoft SQL
  Server 2000 Reporting Services
• Course Number 2030
• Available Now
• Detailed Syllabus www.microsoft.com/learning

To locate a training provider, please
accesswww.microsoft.com/learning
46
Assess your ReadinessMicrosoft Skills Assessment

• What is Microsoft Skills Assessment?
• Self-study learning tool to evaluate readiness
  for product and technology solutions, instead of
  job-roles (certification)
• Windows Server 2003, Exchange Server 2003,
  Windows Storage Server 2003, Visual Studio .NET,
  Office 2003
• Free, online, unproctored, and available to
  anyone
• Answers the question Am I ready?
• Determines skills gaps and provides learning
  plans with Microsoft Official Curriculum courses
• Post your High Score to see how you stack up
• visit www.microsoft.com/assessment

47
Become a Microsoft Certified Systems
Administrator (MCSA)

• What is the MCSA certification?
• For IT professionals who manage and maintain
  networks and systems based on Microsoft Windows
  Server
• How do I become an MCSA on Microsoft Windows
  Server 2003?
• Pass 3 core exams
• Pass 1 elective exam or 2 CompTIA certifications
• Where do I get more information?
• www.microsoft.com/mcsa

48
Become A Microsoft Certified Systems Engineer
(MCSE)

• What is the MCSE certification?
• Premier certification for IT pros who analyze the
  requirements, design, plan, and implement the
  infrastructure for business solutions based on
  the Microsoft Windows Server System
• How do I become an MCSE on Microsoft Windows
  2003?
• Pass 6 core exams
• Pass 1 elective exams from a comprehensive list
• Where do I get more information?
• www.microsoft.com/mcse

49
Demonstrate Your Security or Messaging
Specialization

• What are MCSA/MCSE specializations?
• Allows IT professionals to highlight specific
  expertisewithin their job role
• Which specializations are available?
• MCSA Security ? MCSA Messaging
• MCSE Security ? MCSE Messaging
• Where do I get more information?
• www.microsoft.com/mcsa or www.microsoft.com/mcse

50
What is TechNet?

• Put the right answers at your fingertips
• The comprehensive collection of resources to help
  IT prosplan, deploy and manage Microsoft
  products successfully

• Monthly updates delivered on DVD or CD
• The definitive resource to help you evaluate,
  deploy and maintain Microsoft products

TechNet Subscription

• Accessible at www.microsoft.com/technet
• Online resources and community
• Subscriber-only Online Services

TechNet Web Site

• Biweekly e-newsletter
• Security updates, new resources, and special
  offers

TechNet Flash

• Briefings on the latest Microsoft products and
  technologies
• Hands-on, how to information

TechNet Events and Webcasts

• User Groups
• Managed Newsgroups

TechNet Communities
51
Where Can I Get TechNet?

• Visit TechNet online atwww.microsoft.com/technet
• Register for the TechNet Flash /technet/abouttn/s
  ubscriptions/flash_register.mspx
• Join the TechNet online forum at
  www.microsoft.com/technet/community
• Become a TechNet subscriber at www.microsoft.com/t
  echnet/abouttn/Subscriptions
• Attend more TechNet events or view
  onlinewww.microsoft.com/technet/community/events

52
(No Transcript)
53
Session Credits

• Author Kristin Clark
• Producer/Editor Alan Le Marquand
• Technical Specialists
• Aaron Clutter
• Bob Carver
• Microsoft Reviewers/Editors
• Heather Perry