Transcript Reporting with Reporting Services Western Carolina University Stanley W. Hammer Questions Needing Answers • What is Reporting Services? • Why Reporting Services? How is RS.

Reporting with
Reporting Services
Western Carolina University
Stanley W. Hammer
Questions Needing Answers
• What is Reporting Services?
• Why Reporting Services? How is RS licensed? What
does RS cost?
• How does RS work with Oracle/Banner?
• Hardware and Software Requirements and
Configurations
• How do you Create and Deploy reports?
• Difficulties? Issues?
• Banner Specific Issues?
Tiers of Functionality
Reporting
Focus
of
Presentation
Data Exploration
& Ad hoc Query
Data Warehousing
GOALS
• Focus on entire University needs, not just
reporting services or Banner
– IT needs
– Functional needs
• Keep Simple and Understandable
– Report Design
– Report Usage
– Security
• Portal
• Database
Not Yet Focused on. . .
• Data Exploration or Adhoc Query Tool;
• Data Warehouse or OLAP
However, SQL Server 2005 Reporting Services will
provide self service, end-user ad hoc report creation,
enhanced query development for online analytical
processing (OLAP) environments and enhancements
in scale allowing for rich and easy-to-maintain
enterprise reporting environments
Why Reporting Services?
•
•
•
•
•
Distributed processing - Portal
Uses Active Directory
Easy to install and administer
Integrated report development
Uses standard Internet technology
– (HTTP & XML)
• Works with Oracle and more
The Real Reason
• Needed Reporting Tool Now!
• Least expensive solution (No budget for
Reporting Tool)
• Already owned software SQL Server
• Based on products already used by WCU
• Works with Oracle/Banner
• Installed RS ourselves
• Very Good Product Help available
GOOD HELP
• Online
• Microsoft and Non-Microsoft sites
• Books and Magazines
Good Help = Do it all ourselves without help from vendor
Reporting Services
Agility
Cost
Security
Performance &
Administration
Pricing –
MS Campus Agreement
Enterprise Edition
•Much less than Web
Focus or Oracle
Standard Edition
3x less than
SQL Enterprise Edition
Already Owned
Enterprise Edition
+
No Annual Maintenance
= $ 0.0
SQL Server License
•
•
•
•
•
•
•
RDBMS;
OLAP/Data warehouse;
Management Tools;
Integrated Security;
Reporting Services;
Email Notification Services;
More…
• License
includes all
Features.
• Features are
not priced
separately
Requirements
• Microsoft Windows
– 2000, 2003, & XP
• SQL Server
• IIS (Web Server)
Reporting Services is
Clusterable and
Scalable (Web Farm)
– ASP.NET
Reporting Services is an ASP.NET application that
uses SQL Server for its data storage
Reporting Lifecycle
Phase
Description
Authoring
Creation of the report definition, via an
authoring tool, containing:
• Connection
• Query
• Layout
The published report definition is saved
on a report server, and managed
with Report Manager by the report
server administrator.
The generated report is viewed via an
application, or is routed to a delivery
target where it is accessed by
consumers.
Management
Access and Delivery
Full Reporting Life Cycle Support
•Authoring: Report developers can create reports to be
published to the Report Server using Microsoft or third-party
design tools that use Report Definition Language (RDL), an
XML-based industry standard used to define reports
•Management. Report definitions, folders, and resources are
published and managed as a Web service. Managed reports
can be executed either on demand or on a specified schedule,
and are cached for consistency and performance
•Delivery. Supports both on-demand (pull) and event-based
(push) delivery of reports. Users can view reports in a Webbased format or in e-mail
Reporting Services
Link on Banner Page
Provide Detailed
Descriptions
PORTAL – Only Authorized Reports/Folders are Shown
Portal Security
• Uses WCU’s Directory Service
• Report Authentication & Authorization via
by Active Directory
• Can Utilize Web Server & other security
technology
– SSL, IPSec, Firewall, etc
Portal Security
Active Directory
Security Group
Or Single User
Define Portal Security
Example - Banner Report
Oracle/Banner
Active Directory
(Account +
Authorization)
Reporting Services
•Browser Based
•IE and Netscape
•Enterprise Level Integration
Users
Example – Non-Banner Report
Not Oracle/Banner
Active Directory
(Account +
Authorization)
• Identical Experience;
• Identical Capabilities;
• Identical
Development
Reporting Services
•Browser Based
•IE and Netscape
•Enterprise Level Integration
Users
Reports use a Data Source
(database and database login)
Reports
Use
Data
Source
Database
+
Login
Access to data used by a report is defined and stored in the Portal
Database
Login
Information
Database
Connection
Information
Not just Oracle!
•MS Access/Excel
•SQL Server
•DB2/SYBASE
•ODBC
•Many more
Oracle/Banner Connectivity
Uses Oracle’s .NET Provider
• Free Download from
Oracle;
• Standard Oracle
Database Access;
• Required only on
Reporting Services
Server;
---------------------------------------Oracle Data Provider for .NET 10.1.0.2.0
---------------------------------------Copyright (C) Oracle Corporation 2004
This document provides information that
supplements the Oracle Data Provider for
.NET (ODP.NET) documentation.
IMPORTANT INSTALLATION INFORMATION
==================================
ODP.NET 10.1.0.2.0 depends on Oracle
Client 10.1.0.2.0 that needs to be
installed
in the same Oracle Home.
Oracle/Banner Security
• Uses Database Roles
– Common Tables &
Views
– Alumni
– Finance
– Student, etc.
Alumni Report User
– OR –
Alumni Power User
Common
Alumni
Common
Tables & Views
Alumni
Tables & Views
Roles are independent
from Reporting Services!
= REUSE
EXAMPLE REPORT
1984
Banner Issues
• #1 - Knowing the data, reporting
tool is irrelevant
• Oracle - SQL Based
• Sharing Reports?
• Training
– SQL training
– Relational Design
– Tool use
Issues II
• Non-Technical
– Change
• Performance
– database, Reporting server not stressed
• Security
– Utilize Database roles & Active Directory
groups
– No integrated security with Banner
Multiple Reporting Needs
Enterprise - WCU needs the ability to provide timely information to widespread
employees. Solution should be fast and cost-effective to deploy, and must
leverage existing systems and expertise. Users in these organizations want upto-date information — when they need it — without learning new client tools or
requesting special reports from IT staff. IT organizations need enterprise-class
has great
needs
bothof report subscriptions
manageabilityWCU
and performance
to manage
largefor
numbers
and to ensureenterprise
that report distribution
is consistent with corporate security
and
embedded
requirements
reporting
Embedded – WCU’s IT Applications and Independent software vendors need a
reporting solution that can integrate seamlessly into their packaged solutions,
without adding cost or complexity for the end user. Customers should be able to
make minor modifications to packaged reports without special development
support from the ISV. Real business value from reporting solution, not the
mechanics of reporting
Report Uses
To access reports directly by URL as follows:
http://<server>/ReportServer?/<folder name>/<report name>
http://itareporting/ReportServer?/Advancement/Alums+By+Year
Use reports from our applications (web or non-web, passing in parameters)
...
{
// show user’s account information
ReportingServices.ShowReport(ReportURL,userid);
}
Report Styles
Report Layout
Description
Tabular
Fixed number of columns
Matrix
The number of columns depends
on the query results
Chart
Data represented graphically (pie,
line or bar chart)
List (free-form)
Data arranged free-form on the
page; useful for creating forms
Interactive Reports
Feature
Description
Parameterized
Parameterized reports filter data to provide only that
which is appropriate for the user
Hidden or drilldown
Hidden items on a report can toggle in and out of view
based on user actions
Hyperlinks
Hyperlinks embedded in reports direct users to Web
pages
Drill-through links Drill-through links open another report from within a
report, such as a detailed report from a summary. The
originating report typically passes parameters to the
drill-through report
Bookmark links
Bookmark links direct users to another area of the
current report
Document maps
In HTML Viewer, a document map appears as a table of
contents next to the report. Users can click an item in
the table, and the browser jumps to that item in the
report
Design and Deployment
MS Visual Studio
Reports are authored and deployed
With MS Visual Studio
Add Report to Project
GUI Report Designer
Very similar to MS Access, Crystal Reports,
or any standard reporting tool
Deployment
•
•
•
•
Very Simple
Point and Click
GUI Portal Based
Distributed
Deployment is via Visual Studio or Portal
Report Format is XML
Data Access Demos
• SIS and SQL Server based Admissions data
Report
Uses
SIS
SQL
Server
Access to SIS via Connx
Admissions
Joined with SIS
Cost = $0
Data Access Demos
• SIS and Banner/Oracle
Report
Uses
SIS
Banner/Oracle
Joined with SIS
Cost = $0
Oracle
Access to SIS via Connx