Use of Oracle reports

We all know that the Oracle reports tool is used for Oracle Data Statistics and graphical reports. The Oracle reports tool provides six different report formats, the following describes a matrix Report of Oracle reports.

For matrix-style reports, you can use views to achieve fixed length and width output. The idea is: when the number of Row Records and Column records are less than the number of actual report rows and columns, all are supplemented with null records. The following is an example.

Assume that there is a monthly statistical report on the Consumption direction of materials and funds. This table shows the consumption direction of the current period and the vertical display of material categories. The monthly consumption direction and consumption of materials are not fixed, therefore, such a report is a matrix report. The report data is stored in the base table t-zjxhqx. Its data structure is as follows:

Field name meaning length type

Lbmc category name 20 c

Dwmc unit name 20 c

Je amount 14,2 n

Requirement: the monthly report is collected based on the fund consumption direction of the specified length and width, and x rows and y columns are output on each page (the number of rows and columns can be determined based on the actual situation ).

1. Create a base table t-kjl to store null records. The data structure is as follows:

Field name meaning length type

No. 2 n

After the data table is created, insert a record into it. The number of records depends on the actual situation. Generally, it is the maximum number of rows that can be printed on a page. Assume that there are z records, that is, the value of no is 1, 2, 3... Z.

2. Create a view. The process is as follows:

 
 

  
  
create view v—tjbb as  
  
  
select dwmc,lbmc,je from t—zjxhqx  
  
  
union  
  
  
select dwmc,null,to—number(null) from 
t—zjxhqx,t—kjl  
  
  
where no－(z－x)〉(select mod
(count(distinct lbmc)－1,x)＋1 from t—zjxhqx)  
  
  
union  
  
  
select null,lbmc,to—number(null) 
from t—zjxhqx,t—kjl  
  
  
where no－(z－y)〉(select mod
(count(distinct dwmc)－1,y)＋1 from t—zjxhqx);  
 
 

3. Start Oracle reports and directly reference view v-tjbb when creating a matrix REPORT query. In this way, each page of the report output during running is x row y column, which achieves fixed length and fixed width output.

The above content is an introduction to the matrix-style reports. I hope you will get some benefits.