Oracle Reports Introduction

Data Model

Definition,Queries, Column, Group

Datalink

Datamodel

Layout Model

Frame, Object

Formula Column, Summary Column

List Of Values (LOV)

Trigger

How to build a Report

Parameter

SRW Package

What is a Report ?

        Report is a mean/medium through which we extract existing data and display them as    per our business requirement.

Why do we need a Report ?

Ø To Check the Correctness of Data

Ø For Business Analysis

Ø For Statutory Requirement

Different Ways to generate a report in Oracle

Ø Report Developer (Reports 6i/10g)

Ø SQL*Reports

Ø Writing into a file through PL/SQL

What is Reports Developer?

   Reports Developer is a powerful enterprise reporting tool used to build reports that dynamically retrieve, format, and distribute information stored in the database.

Advantages of Report Developer:

Ø Web publishes high quality reports.

Ø Perform unlimited data formatting.

Ø Take advantage of application server based reporting.

Ø User Friendly

Understanding the Parts of a Report:

      A report is made up of objects, the building blocks that define the various parts of  the report. Some objects, like fields, are visible in the report and others, like queries, work behind the scenes.

Data Model:

       The data model is composed of objects that define the data to be included in a report. Data model objects include:

Queries

Columns

Groups

Parameters

Data links

 Queries:

      Queries select the data from a standard data source such as Oracle, DB2, or SQL/DS, using    SQL SELECT or PL/SQL statements.

 Columns:

     Columns represent the columns in the datasource that are selected by the query. You can  also define columns based on computed values such as summaries (also called totals) or values set conditionally.

Groups:

        Groups organize the data into sets and hierarchies. By default, one group is created for each query in the report. The group contains all the columns selected by the query.

Parameters:

      Parameters are variables, such as the printer name, to which users can assign value when the report is run.

Data links:

      Data links are used to define a master/detail (parent-child) relationship between the data of    two queries.

      Structure of the Data Model

Layout Models:

The layout model is composed of objects that define the positioning and appearance of data and other objects in a report. This objects include:

Ø Repeating Frames

Ø Frames

Ø Fields

Ø Anchors

Ø Boilerplate Objects

Ø Buttons

Repeating Frames:

      Repeating frames are used to display the rows of data that are retrieved for a group. Thus, they "repeat" until all the data is retrieved.

Frames:

  Frames are used to keep layout objects together and can be used to protect layout objects from being overwritten by repeating frames at runtime.

Fields:

    Fields define how columns appear in a report, such as the format of currency amounts and dates.

Boilerplate Objects:

    Boilerplate objects are objects that appear in the report each time the report is run. Examples of boilerplate objects are field labels, graphics etc.

Buttons:

   Buttons are objects users can click in an online report to display videos, sounds, or images, or execute a PL/SQL program that performs some action, such as launching another report.

        Structure of the Layout Model

Formula Column:

  

       We use formula column to calculate some information dynamically using information based on the columns of the data model or from the concurrent program parameters. It is basically used to apply some custom logic on input data and return some value.

Ø Performs a user-defined computation

Ø Executes a PL/SQL function

Ø Must return a value

Ø Can be a Character, Number, Date

Ø Returned value must match datatype

              Function CF_SALCALCFormula return Number is

                      begin

                         return(my_function :salary)

                     end;

Summary Column:

  Summary columns are used for calculating summary information like sum, average etc,. on specific columns of a data group.  This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Ø Specific properties:

          - Function

          - Source

          - Reset At

          - Compute At

Ø Datatype depends on Source datatype.

Place Holder column

       Place holder column is an empty container at design time which is used to hold some value in run time, they are like local/global variables which can be used in the logic of a formula column or in report triggers. Value in the place holder column can be directly accessible in the report layout.

User Parameters handling in Oracle Reports

    User Parameters can be of two types:

      - Bind Parameters

           Bind references (or bind variables) are used to replace a single value in the replace  expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START  WITH  clauses of queries of the Report.

 You create a bind reference by entering a colon (:) followed immediately by the column or parameter name.  If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

- Lexical Parameters

      Lexical references are placeholders for text that you embed in a SELECT statement.  You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

           You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name.  A default definition is not provided for lexical references.

Therefore, you must do the following:

         Before you create your query, define a column or parameter in the data model for each lexical reference in the query.  For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value.  Report Builder uses these values to validate a query with a lexical reference.

Create your query containing lexical references

System Parameters Handling In Oracle Reports:

System Parameters are the built in parameters that are interpreted  by the system.

Some important ones are:

1. ORIENTATION: This parameter decides that the report will be printed 

         in Landscape or Portrait Mode.

2. MODE: This parameter decides that the report will be a text or pdf

       format report.

List of Values:

     Steps to create static LOVs

–         In the parameter property palette, choose the LOV property. The static values radio button is selected by default.

–         Enter a value in the value field and choose ADD

–         Repeat for each value you want in the list

–         Note: To remove a value, select the value in the list and choose REMOVE.

    Steps to create dynamic LOVs

–         In the parameter property palette, choose the LOV property.

–         Choose SELECT statement. The SQL query statement area displays.

–         Enter query to populate the list of values. You can include more than one column; the parameter takes its value from the first column in the list.

–         Set the restrict list to predetermined values property, as required.

Global Report Triggers:

         Report Builder has five global report triggers.

Ø Before Parameter Form

Ø After Parameter Form

Ø Between Pages 

Ø Before Report

Ø After Report

Before Parameter Form:

   Before Parameter Form Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns.

After Parameter Form:

      After Parameter Form Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the run-time Parameter Form. Columns from the data model are not accessible from this trigger.

Between Pages Trigger:

     Between Pages Fires before each page of the report is formatted, except the very first page.This trigger can be used for customized page formatting.

Before Report Trigger:

       Before Report Fires before the report is executed but after queries are parsed and data is fetched

After Report Trigger:

     After Report Fires after you exit the Previewer, or after report output is sent to a specified     destination,such as a file, a printer. This trigger can be used to clean up any initial processing that was done, such as deleting tables.

 Format Trigger

      Format trigger can be used to dynamically change the formatting attributes of the object.   Data type of format trigger is Boolean.

Validation Trigger        

    

              Validation triggers are PL/SQL functions that are executed when parameter values are specified on the parameter form. Data type of format trigger is Boolean.

Action Triggers

    Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

 Steps to be followed to design a report using report wizard:

Ø Select a report style and enter the title

Ø Build the query

Ø Group any field if required

Ø Select the fields to be displayed in the report

Ø Select totals to be displayed, if required

Ø Change the labels as required

Ø Select a template

Ø Click the finish button

Ø Save the module

Steps to be followed to design a report using manual method:

Ø Build the query in data model.

Ø Break the group if necessary

Ø Create formula columns, summary columns, if necessary

Ø Switch to layout model

Ø Create frames, repeating frames, fields, boiler plate objects as required.

Ø Compile and run the report

Ø Save the report module

SRW Package: Outputting Message:

SRW.MESSAGE : This procedure displays a message with the message number and  text that    you specify.  The message is displayed in the format below.  After the message is raised and  you accept it, the report execution will continue.

Exception

WHEN <exception> THEN

 SRW.MESSAGE(999, ‘Warning: report continues’);

 Anchors:

           Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.  The end of the anchor with a symbol on it is attached to the parent object.

           Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.  An anchor defines the relative position of an object to the object to which it is anchored.  Positioning is based on the size of the objects after the data has been fetched rather than on their size in the editor.  It should also be noted that the position of the object in the Layout editor effects the final position in the report output.  Any physical offset in the layout is incorporated into the percentage position specified in the Anchor property sheet.

Program Units:

    Program Units can be developed in the report builders itself. They can be a Procedure, function, package.

       The idea of writing a program Units may be reducing the database fetch in reports runtime. Since program units are written in the report builder itself, database won’t be hit upon their invocation until and unless you call a database object from within your program unit.