Using Globals in Oracle Reports (Doc ID 34751.1)

Applies to:

Oracle Reports Developer Information  in this document applies to any platform.

Purpose

 This article explains how to use Global variables within Reports.

Details

What are Global variables?

Global variables(Globals) are Oracle Reports variables that persist for the duration of a session.  They can hold character strings or numbers.

What are the advantages of using Globals?

Globals persist for the duration of a session, and hence, each Global's value is shareable. This data can be shared across transactions without having it stored in the database.

Global variables perform faster than parameters, and they are not restricted as placeholders are, in the sense that a formula column that sets a placeholder column must be in the same group as the placeholder column.  Globals are not placed in any group. 

Globals are very useful in overcoming 'frequency' problems. For example, it may be necessary for one field to reference a value in another field at a lower frequency.  An example is illustrated below.

How can Globals be used in Oracle reports?

To use Globals in Oracle Reports, it is necessary to create a package spec and declare each Global within this package spec.

NOTE:  It is important to be aware that this differs significantly from the usage of Oracle Forms Globals, that are automatically created when they are initialized with a value within a trigger.  Also, globals cannot be passed between a report and a form.

Example of using Globals in a report:

The following is a report that demonstrates the use of globals, based on the Oracle DEPT and EMP demonstration tables.

The output we wish to achieve is a master detail report that lists employees for each department.  However, in addition to this information, we want a field printed at the top of each page that holds a reference number whose value is determined by the value of DEPTNO on the current page.  This reference number is the department number with three zeroes appended for departments 10 and 20, but 99999 for any other departments.  We want this reference number to print at the top of every page (including departments where the data goes over one page).

Sample output ('>>' indicates page break):

10000
Deptno 10
Empno     Ename     Sal -----   -------     --- 7782    CLARK       2450 7839    KING        5000 7934    MILLER      1300
>>
20000
Deptno 20
Empno    Ename     Sal -----    -----     --- 7566     JONES     2975 7788     SCOTT     3000
>>
99999
Deptno 30
Empno    Ename     Sal -----    -----     --- 7499     ALLEN     1600 7521     WARD      1250 7654     MARTIN    1250 7698     BLAKE     285 >>                  99999
Deptno 30
Empno  Ename      Sal -----  -----      ---   7844   TURNER     1500 7900   JAMES      950
>>

Steps to create the report :

1. In the Data Model, use the SQL tool to create the following query, Q_Emp:

select empno,ename,sal,deptno from emp order by deptno

2. In the Data Model, drag the DEPTNO column into its own group above the other columns.  Rename the group G_Dept.  This makes DEPTNO the break column for the report.

3. Choose Default Layout, Master-Detail.

4. In the Layout Editor, set Maximum Records per Page to 1 on the R_Dept repeating frame.  This ensures that we do not get more than one department printed on a page.

5. In the Data Model, create a Summary column at report level with the following properties:

Name: D_Ref, Source: deptno, Type: Number, Function:Sum, Reset At:Page

6. In the Layout Editor, create a field F_Ref in the Top Margin area of the report that is sourced on the summary column D_Ref.

The next stage is to write code to set the value of F_Def based on the value of DEPTNO on the current page.  The problem is that we cannot directly reference the value in DEPTNO in the trigger code for the F_Def field without receiving REP-1314 ('format trigger 'F_Def' references column 'DEPTNO' at wrong frequency).  The only way around this is to use globals, as in steps 7-9 below.

7. Create a package spec, using the Oracle Reports Program Unit Editor:

package GLOBAL is  

deptvar number ;

end GLOBAL;

This is the dummy package that we have created just to get the Global variable deptvar that we will be using later on in the PLSQL portion.

8. Create a format trigger on the DEPTNO field (F_Deptno) that copies the value in the field into the Global:

Function F_deptnoFormatTrigger

return Boolean is

begin  

global.deptvar:=:deptno;  

return(true);

end ;

9. Create a Format Trigger on the F_Ref field that checks the value in the global and writes out the appropriate reference number.

For example:

Function F_RefFormatTrigger return Boolean is  

/* Declare refnum to hold calculation results */  

refnum number;

begin  

/* Calculate the reference number for departments 10 and 20 */  

if global.deptvar in (10,20) then    

refnum := global.deptvar*1000 ;  

else    

/* Calculate the reference number for any other department */    

refnum := 99999;  

end if ;  

/* Write this reference number into the margin field */    

srw.set_field_num(0,refnum);    

return(true);

end ;

 10. Run the report to see the desired output.