Reporting Services

1
Reporting Services

• Presented Eric Phan

2
Admin Stuff

• Attendance
• You initial sheet
• Hands On Lab
• You get me to initial sheet
• Certificate
• At end of 10 sessions
• If I say if you have completed successfully ?

3
Course Overview

• The 5 Sessions (Part B)
• SSIS and Creating a Data Warehouse
• Creating a Cube and Cube Issues
• Reporting Services
• Other Cube Browsers
• RS Report Builder
• Excel 2007
• Proclarity
• Thin Slicer
• zzCrystal
• Data Mining
• http//www.ssw.com.au/ssw/events/2008SQL

4
Session 3 Tonights Agenda

• What is SQL Reporting Services?
• Steps in Creating a Report
• Demo Creating a Report
• Hands on Lab

5
SQL Server BI Platform
6
What is SQL Server Reporting?

• 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)

7
Reporting Lifecycle

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

8
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 www.microsoft.com/sql/r
eporting
9
Report Authoring - Controls

• Data regions
• Tablix (New in SQL 2008)
• Table
• List (like Access)
• Matrix
• Chart
• Gauge
• Subreports
• Images
• Scope (lab)
• Custom Controls

10
Tablix

• Generalized layout report item
• Grouped and Detail data

11
Tablix - Table

• Use a table to display detail data
• Organize the data in row groups, or both.
• The Table template contains three columns with a
  table header row and a details row for data.

12
Tablix - Matrix

• Use a matrix to display aggregated data summaries
• Grouped in rows and columns, similar to a
  PivotTable or crosstab.
• The number of rows and columns for groups is
  determined by the number of unique values for
  each row and column groups

13
Tablix - List

• Use a list to create a free-form layout. You are
  not limited to a grid layout, but can place
  fields freely inside the list.
• You can use a list to design a form for
  displaying many dataset fields or as a container
  to display multiple data regions side by side for
  grouped data.

14
Charts
15
Charts
16
Charts
17
Gauge
18
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

19
Report Manager
20
Configuration Manager
21
Report Delivery

• Traditional (paper) and interactive (web) reports
• On-demand (pull) or event-based (push)
  delivery
• Choose from multiple formats (HTML, Excel, PDF,
  XML, Word)
• Deliver reports to many devices (e-mail, file
  share, etc.)
• Ad-hoc Reporting

22
SSRS and SSAS Working Together
Standard reports Ad hoc reports
Detail reports
SSAS
SSRS

• Intuitive reporting
• Interactive analysis
• High performance
• Powerful calculations

• Detail reporting
• Standard reporting
• Ad hoc reporting
• Flexible delivery

23
SSRS Extensibility

• Reference .NET assemblies
• Data Processing Extensions
• Rendering Extensions
• Delivery Extensions
• Integrate into your applications
• Using the Web Service
• Hitachis Crystal -gt RDL tool

24
SSAS and Reporting Services
Data Layer
SSAS
Presentation Layer
UnifiedDimensionalModel
ETL
DB1
Interactive reporting
UDM
DB2
DW
Standard reporting
DB3
multiple data sources supported
Ad hoc reporting

• Dimensional schema intuitive reporting
• SSAS for better performance and business metrics
• UDM serves many reporting needs

25
Report Builder
26
Report Builder v2
27
Its fantastic but.

• It doesnt ship with SQL 2008
• It will be part of SP1
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyIDcc9acbbd-570c-4712-b74d-85f537a5a75fDispla
  yLangenfilelist

28
Report Builder Drill-through Reports

• Start in Report Builder
• Get the data you want
• In report properties choose Allow users to drill
  to this report option
• Optionally customize in Report Designer

29
Analysis Services Actions

• UDM extensibility feature
• Browser-specific implementation
• Action types
• Regular actions multi-purposed commands
• Drillthrough actions get details behind cell
• Reporting actions launch SSRS report

30
Summary

• What is Reporting Services?
• Steps in Creating a Report
• Demo Creating a Report
• Hands on Lab

31
2 Things

• JustinKing_at_ssw.com.au

32

• Thank You!
• Gateway Court Suite 10 81 - 91 Military Road
  Neutral Bay, Sydney NSW 2089 AUSTRALIA
• ABN 21 069 371 900
• Phone [phone] Fax [phone]
• info_at_ssw.com.auwww.ssw.com.au