Reporting Services - Western Carolina University

guideflannelΔιακομιστές

4 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

75 εμφανίσεις

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?


Focus

of

Presentation

Data Warehousing

Data Exploration

& Ad hoc Query

Reporting

Tiers of Functionality

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

Security

Cost

Agility

Performance &

Administration

Pricing



MS Campus Agreement

Enterprise Edition


Much less than Web
Focus or Oracle

No Annual Maintenance

Already Owned

Enterprise Edition

= $ 0.0

+

Standard Edition


3x less than

SQL Enterprise Edition

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)


ASP.NET


Reporting Services is an ASP.NET application that
uses SQL Server for its data storage

Reporting Services is

Clusterable and

Scalable (Web Farm)

Reporting Lifecycle

Phase




Description

Authoring




Creation of the report definition, via an
authoring tool, containing:


Connection


Query


Layout

Management




The published report definition is saved
on a report server, and managed
with Report Manager by the report
server administrator.

Access and Delivery




The generated report is viewed via an
application, or is routed to a delivery
target where it is accessed by
consumers.

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 Web
-
based 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

Oracle/Banner

Active Directory

(Account +
Authorization)

Users

Example
-

Banner Report

Reporting Service
s


Browser Based


IE and Netscape


Enterprise Level Integration

Not Oracle/Banner

Active Directory

(Account +
Authorization)

Users

Example


Non
-
Banner Report

Reporting Service
s


Browser Based


IE and Netscape


Enterprise Level Integration



Identical Experience;



Identical Capabilities;



Identical Development

Reports

Use

Data

Source

Database

+

Login

Reports use a
Data Source


(database and database login)

Access to data used by a report is defined and stored in the Portal

Database

Connection

Information

Login

Information

Database

Not just Oracle!



MS Access/Excel


SQL Server


DB2/SYBASE


ODBC


Many more

Oracle/Banner Connectivity


Uses Oracle’s .NET Provider

----------------------------------------

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.



Free Download from
Oracle;




Standard Oracle

Database Access;




Required only on
Reporting Services
Server;

Oracle/Banner Security


Uses Database Roles


Common Tables &
Views


Alumni


Finance


Student, etc.

Common

Tables & Views

Common


Alumni

Tables & Views

Alumni

Alumni Report User



OR



Alumni Power User

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 up
-
to
-
date information


when they need it


without learning new client tools or
requesting special reports from IT staff. IT organizations need enterprise
-
class
manageability and performance to manage large numbers of report subscriptions
and to ensure that report distribution is consistent with corporate security
requirements


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

WCU has great needs for both
enterprise and embedded
reporting

Report Uses

http://itareporting/ReportServer?/Advancement/Alums+By+Year

To access reports directly by URL as follows:

http://<server>/ReportServer?/<folder name>/<report name>

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 drill
-
down

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

Admissions

Joined with SIS

Cost = $0

Access to SIS via Connx

Data Access Demos


SIS and Banner/Oracle

Report

Uses

SIS

Oracle

Banner/Oracle

Joined with SIS

Cost = $0

Access to SIS via Connx