1 / 35

OracleAS Reports Services

OracleAS Reports Services. Problem Statement. To simplify the process of managing, creating and execution of Oracle Reports. What is a Report?. A document which dynamically retrieves, formats and distributes database information. A General Reporting System.

laurie
Download Presentation

OracleAS Reports Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OracleAS Reports Services

  2. Problem Statement To simplify the process of managing, creating and execution of Oracle Reports.

  3. What is a Report? A document which dynamically retrieves, formats and distributes database information.

  4. A General Reporting System

  5. Oracle Report Service Architecture

  6. Limitations of Oracle Solution • Embedded Parameter Form • Lack of report categorization • Lack of report access description • Limited report parameter description • Inability to build reports from the web • Complete Dependence on Oracle Reports Service to view reports • Complete dependence on Oracle Report Builder to create reports

  7. Improvements in the Developed System • Single Dynamic Parameter Form • Additional METADATA stored in oracle database to handle • Report Categorization • User Access • Multiple Parameter Lookups • NONE • SQL Query • Date • LOV from Help Table

  8. Improvements in the Developed System (contd) • Ability to Create Reports from the web • Handled by XML Reports compliant with oracle reports DTD • Complete independence from Oracle Reports Builder and Service • Handled by the ADHOC reporting subsystem

  9. Report Added Value

  10. Report Types • RDF • Legacy Systems with existing oracle reports • Created using Oracle Reports Builder • Published using Oracle reports Service • Individual Parameter forms not required

  11. Report Types • XML • No dependence on Oracle Reports Builder • Report is created on the fly using the system • Based on the Metadata provided • Adheres to the oracle reports DTD • Report is automatically deployed.

  12. Report Types • SCRIPTS(ADHOC) • No dependence on Oracle reports builder or Oracle Reports Service. • Based on a simple parameterized query. • Example • Select * from emp where emp.salary > ‘P_1’ and emp.age < ‘P_2’

  13. Modules Report Maintenance ADHOC Report Module XML Report Generator Report Scheduler Report Execution & Viewer

  14. Report Maintenance • Module used to prepare and create reports. • A simple user interface provides this functionality. • Modules stores the metadata required for every report in the database.

  15. Report MaintenanceParameter Metadata • The metadata required for a report is stored in a child table referencing the Report Table • Provides the ability to specify Parameter Name, Description, Lookup Type and Sequence • Types of Lookup • None • SQL • Date • LOV from Help Table

  16. XML Report Generator • A module to build reports based on report metadata stored. • Output XML file created and is deployed. • Creates the following sections which are required to publish a report • Data • Layout

  17. Logic Flow – XML Report Generator Parameterized SQL Query Stored in the database along with report METADATA XML Generator parses the SQL query to identify parameters and columns For each column identified, respective <field> elements are created If the report has parameters then the parameter name is included within the <userParameter> element An XML report is created and is deployed to a location specified by the REPORTS_PATH env variable The generator then creates a header section based on information stored in the database The report is ready and can viewed or scheduled

  18. ADHOC Report Module • An independent reporting engine which simulates the Oracle Reports Service. • Provides Maximum uptime. • Implemented as stored procedures. • Advantage • Complete independence from Oracle Reports Builder and Oracle Reports Service

  19. ADHOC Report Logic Flow Report Viewer Builds a dynamic Parameter form based on the metadata available for the report Parameterized SQL Query Stored in the database User selected variables are passed to the stored procedure variable as a CSV Report execution stored procedure identifies an ADHOC report The string and the CSV are parsed and a dynamic sql query built The Parameterized SQL query is retrieved from the database as a string At this stage “Select * from Employee where salary > ‘P_1’ and age = ‘P_2’  select * from employee where salary > 100000 and age= 45 This is used as a reference cursor and data is retrieved The mime type decides the output format

  20. Report Scheduler • Module Used to Schedule Reports • RDF/XML Reports scheduled on Oracle Reports Service. • ADHOC reports scheduled within the oracle database. • Reports can be scheduled daily, weekly or monthly. • Scheduled metadata extracted and stored in the database table.

  21. RW_SERVER_JOB_QUEUE • The reports service assigns a jobid to every report which is run against it. • Table stores information of every job submitted to the reports service. • A web interface queries the table to display a list of scheduled jobs • Ability to cancel scheduled jobs.

  22. Report Scheduler Logic Flow User Initiates a schedule along with list of email address. Report type metadata is extracted from the database The DESTYPE parameter is automatically set to PDF If report_type = RDF or XML, then the report is scheduled on the oracle reports service If report_type= ADHOC, then DBMS_JOB is used to schedule the report RW_SERVER_QUEUE contains the submitted job information. and is displayed on the interface

  23. Report Execution & Viewer • Report Selection • A user interface allows users to select reports • Reports are displayed as a hierarchical structure based on the report category metadata • Only user specific Reports visible based on metadata. • Report execution • Dynamic Parameter Form displayed based on the metadata • RDF/XML/Adhoc reports can be scheduled or viewed instantaneously

  24. Dynamic Report Parameter Form Logic Flow Report parameter metadata is stored in the database The report name is passed to the dynamic parameter interface Also, based on the lookup type a user input is created, which could be a result of a SQL query Based on the report name , the parameter Descriptions, and Sequence are displayed on the screen The user inputs the variable parameters along with the output format and scheduling preference The report is executed by passing the dynamic query string created to the reports service or ADHOC engine Hence creating a Dynamic Parameter form, overcoming the limitations of embedding them within the report

  25. Security • Access Provided based on Database Roles • Roles • USER_ROLE • READ_ONLY_ROLE • REPORT_USER_ROLE • REPORT_SCHEDULER_ROLE

  26. Advantages • No Knowledge of Report Builder Required to Build reports • No licensing costs involved (XML,ADHOC) • Batch update reports easily (XML) • Supports various types of reports – RDF,XML,SCRIPTS • Independence from Oracle Reports Builder or Reports Service depending on type of report

  27. Technology Used • Oracle Reports Service 10g • Oracle Developer Suite 10g • Oracle Database 10g • PL/SQL • JavaScript • HTML • PL/SQL web toolkit for interface development and server side scripting.

  28. Future Work • Representation of reports metadata in the form of XML • Batch processing of XML metadata. • Providing a complete web interface for layout description

  29. Screenshots

  30. References • Oracle Documentation http://download.oracle.com/docs/cd/B10464_05/bi.904/b13673/pbr_arch.htm

  31. End of lecture Questions?

More Related