Use of ranking in report Builder

The column types in report Builder include formula column, summary column, and percentage column. Generally, many formula columns and summary columns are used. The following describes the usage of ranking.

In the previous application, 20 formula columns are required for the value, and each formula column uses the same SQL statement of the from and where clauses. The only difference is that the obtained fields are different. This requires 20 database read operations, which is slow. At the beginning, we considered using another query, but there was no clear connection relationship, which could not be achieved.

Finally, I decided to study the ranking. I will not go into details about the specific process and introduce the implementation methods.

1. First, create a formula column with 19 ranking items named CF_1, cp_2, cp_3 ......, Cp_20.

2. In the PL/SQL statement of the formula column, all values are retrieved once:

 

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;

 

In the ranking list, no PL/SQL statements are written.

3. When editing the report page, the ranking column is used as the data source of the domain.

Advantage: Only one database read operation is required, which is faster. Because the main logic is put in the formula column CF_1, it is easy to maintain.

Note the column order. It is best to place the CF_1 formula column before the percentage column. In fact, the ranking is used as a variable to facilitate programming and maintenance.