The column types in the Report Builder include formula columns, summarizes, placeholders, and the like. The general formula column, the summary is used, and it is no longer detailed here. The following mainly leaps a use method of using a bit.
A few days ago, 20 formula columns are required, while each formula column uses the same from, the WHERE clause s SQL statement, the only difference is that the fields are different. This requires 20 read database operations, and the speed is slow. At first, consider using a Query to do, but there is no clear connection relationship and cannot achieve the purpose.
Finally, I decided to study the sequence, the specific process is no longer described, introducing the implementation method.
1. First establish a formula column, 19 placeholder columns, named CF_1, CP_2, CP_3 ..., CP_20, respectively.
2. Take all values in the PL / SQL in the formula column:
SELECT COL1, COL2, COL3 ... COL20
INTO V_COL1,: CP_2,: CP_3 ...: CP_20
From table1
WHERE T_DATE = SYSDATE AND T_DEPT =: p_1;
Return v_col1;
No PL / SQL is written in the plain column.
3. When editing the report page, these placeholders are used as the domain data source.
Advantages: only one database read operation is required, the speed is faster. And because of the main logic in the formula column of CF_1, it is convenient for maintenance.
It is necessary to pay attention to the order of the column, and it is best to place the CF_1 formula in front of the occupying column. In fact, it is to use the placeholder column as a variable, which is convenient for programming and maintenance.
