oracle report ppt

8/12/2019 Oracle Report PPT

1/35

OracleAS Reports Services


2/35

Problem Statement

To simplify the process of managing, creatingand execution of Oracle Reports.


3/35

What is a Report?

A document which dynamically retrieves,formats and distributes database information.


4/35

A General Reporting System


5/35

Oracle Report ServiceArchitecture


6/35

Limitations of Oracle Solution

Embedded Parameter FormLack of report categorizationLack of report access descriptionLimited report parameter descriptionInability to build reports from the webComplete Dependence on Oracle ReportsService to view reportsComplete dependence on Oracle ReportBuilder to create reports


7/35

Improvements in theDeveloped System

Single Dynamic Parameter Form Additional METADATA stored in oracle database tohandle

Report CategorizationUser Access

Multiple Parameter LookupsNONESQL QueryDateLOV from Help Table


8/35

Improvements in theDeveloped System (contd)

Ability to Create Reports from the webHandled by XML Reports compliant with oraclereports DTD

Complete independence from Oracle ReportsBuilder and Service

Handled by the ADHOC reporting subsystem


9/35

Report Added Value


10/35

Report Types

RDFLegacy Systems with existing oracle reportsCreated using Oracle Reports BuilderPublished using Oracle reports ServiceIndividual Parameter forms not required


11/35

Report Types

XMLNo dependence on Oracle Reports BuilderReport is created on the fly using the systemBased on the Metadata provided

Adheres to the oracle reports DTDReport is automatically deployed.


12/35

Report Types

SCRIPTS(ADHOC)No dependence on Oracle reports builder orOracle Reports Service.Based on a simple parameterized query.Example

Select * from emp where emp.salary > P_1 andemp.age < P_2


13/35

Modules

Report Maintenance ADHOC Report Module

XML Report Generator

Report SchedulerReport Execution & Viewer


14/35

Report Maintenance

Module used to prepare and create reports. A simple user interface provides thisfunctionality.Modules stores the metadata required forevery report in the database.


15/35

Report MaintenanceParameter Metadata

The metadata required for a report is stored in achild table referencing the Report TableProvides the ability to specify Parameter Name,

Description, Lookup Type and SequenceTypes of Lookup

NoneSQLDateLOV from Help Table


16/35

XML Report Generator

A module to build reports based on reportmetadata stored.Output XML file created and is deployed.Creates the following sections which arerequired to publish a report

DataLayout


17/35

Logic Flow XML ReportGenerator

Parameterized SQL QueryStored in the databasealong with reportMETADATA

XML Generator parses the SQL query toidentify parameters and columns

For each column identified, respective

elements are created

If the report has parameters then the

parameter name is included within the element

The generator then creates a headersection based on information stored in

the database

An XML report is created and isdeployed to a location specified bythe REPORTS_PATH env variable

The report is ready and can viewed orscheduled


18/35

ADHOC Report Module

An independent reporting engine whichsimulates the Oracle Reports Service.Provides Maximum uptime.Implemented as stored procedures.

AdvantageComplete independence from Oracle ReportsBuilder and Oracle Reports Service


19/35

ADHOC Report Logic Flow

Parameterized SQL QueryStored in the database

Report Viewer Builds a dynamicParameter form based on the metadataavailable for the report

User selected variables are passed to

the stored procedure variable as a CSVReport execution stored procedureidentifies an ADHOC report

The Parameterized SQL query isretrieved from the database as a string

The string and the CSV are parsedand a dynamic sql query built

At this stage Select * from Employee wheresalary > P_1 and age = P_2 select *from employee where salary > 100000 andage= 45

This is used as a referencecursor and data is retrieved

The mime type decides theoutput format


20/35

Report Scheduler

Module Used to Schedule ReportsRDF/XML Reports scheduled on OracleReports Service.

ADHOC reports scheduled within the oracledatabase.Reports can be scheduled daily, weekly or

monthly.Scheduled metadata extracted and stored inthe database table.


21/35

RW_SERVER_JOB_QUEUE

The reports service assigns a jobid to everyreport which is run against it.Table stores information of every jobsubmitted to the reports service.

A web interface queries the table to display alist of scheduled jobs

Ability to cancel scheduled jobs.


22/35

Report Scheduler Logic Flow

User Initiates a schedulealong with list of emailaddress.

Report type metadata is extracted fromthe database

The DESTYPE parameter isautomatically set to PDF

If report_type = RDF or XML, then thereport is scheduled on the oraclereports serviceIf report_type= ADHOC, thenDBMS_JOB is used to schedule thereport

RW_SERVER_QUEUE contains thesubmitted job information. and isdisplayed on the interface


23/35

Report Execution & Viewer

Report Selection A user interface allows users to select reportsReports are displayed as a hierarchical structure based on

the report category metadataOnly user specific Reports visible based on metadata.

Report executionDynamic Parameter Form displayed based on the

metadataRDF/XML/Adhoc reports can be scheduled or viewedinstantaneously


24/35

Dynamic Report ParameterForm Logic Flow

Report parameter metadatais stored in the database

The report name is passed to the dynamicparameter interface

Also, based on the lookup type a userinput is created, which could be a resultof a SQL query

Based on the report name , theparameter Descriptions, andSequence are displayed on thescreen

The user inputs the variableparameters along with the outputformat and scheduling preference

The report is executed by passingthe dynamic query string created tothe reports service or ADHOC engine

Hence creating a Dynamic Parameter form, overcoming the limitations ofembedding them within the report


25/35

Security

Access Provided based on Database RolesRoles

USER_ROLEREAD_ONLY_ROLEREPORT_USER_ROLEREPORT_SCHEDULER_ROLE


26/35

Advantages

No Knowledge of Report Builder Required toBuild reportsNo licensing costs involved (XML,ADHOC)Batch update reports easily (XML)Supports various types of reports RDF,XML,SCRIPTSIndependence from Oracle Reports Builder orReports Service depending on type of report


27/35

Technology Used

Oracle Reports Service 10gOracle Developer Suite 10g

Oracle Database 10gPL/SQLJavaScript

HTMLPL/SQL web toolkit for interface developmentand server side scripting.


28/35

Future Work

Representation of reports metadata in theform of XMLBatch processing of XML metadata.Providing a complete web interface for layoutdescription


29/35

Screenshots


30/35


31/35


32/35


33/35


34/35

References

Oracle Documentationhttp://download.oracle.com/docs/cd/B10464_ 05/bi.904/b13673/pbr_arch.htm


35/35

End of lecture

Questions?

oracle report ppt

Documents