4.16 Administer Oracle Reports Builder

This section provides procedures for the following tasks that you may perform as you administer Oracle Reports Builder:

Setting a database role
Converting from one format to another
Improving performance using SQL statements
Improving performance using WHERE clauses

4.16.1 Setting a database role

Before beginning this procedure, verify that the database administrator has created the role, granted privileges to the role, and granted the role to approved end users. Refer to your Oracle Application Developer's Guide for more information.

To set a database role for a report:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report node, set the Role Name property as defined by the database administrator in the database.
Optionally, to set a Role Password, double-click the button in the Role Name value field to display the Set Role dialog box.

Usage notes

The role settings in the report are overridden if you specify a role using the command line with Reports Runtime (rwrun).
You can set only one role for a report. To set multiple roles, you need to set a role to which the other roles have been granted.

Caution:
Do not attempt to set the role in a PL/SQL trigger. The PL/SQL will not compile.

4.16.2 Converting from one format to another

To convert one or more report definitions or PL/SQL libraries from one storage format to another:

In Oracle Reports Builder, choose Tools > File Conversion to display the Convert dialog box.
On the command line on Windows, type %ORACLE_HOME%\bin\rwconverter, followed by the report name and desired arguments.

4.16.3 Improving performance using SQL statements

Performing operations in SQL is faster than performing them in Oracle Reports Builder or PL/SQL. The following are the most common cases where using SQL would improve performance:

Use a WHERE clause instead of a group filter or format trigger to exclude records.
Use the SUBSTR function to truncate character strings instead of truncating in Oracle Reports Builder.
Perform calculations directly in your query rather than in a formula or summary.

Rationale: SQL can perform calculations more quickly than a summary or formula. WHERE and SUBSTR can reduce unnecessary fetching because they operate on the data during rather than after data retrieval. Improvements in performance become more noticeable when retrieving thousands of records versus a few records.

4.16.4 Improving performance using WHERE clauses

Consider adding a WHERE clause for the matrix cell query in a multiquery data model. If you are using a multiquery data model and your dimension queries are restricted by a WHERE clause, adding a WHERE clause to the matrix cell query ensures that you do not retrieve more records than are necessary. For example, suppose that you had the following queries for your dimensions:

Q_Dept

SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100

Q_Job

SELECT DISTINCT JOB FROM EMP
WHERE DEPTNO < 100

Q_Filler

To ensure that your cell query only retrieves the records that are necessary, you would write the following SELECT statement:

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100) AND JOB IN (SELECT
DISTINCT JOB FROM EMP WHERE DEPTNO < 100)
GROUP BY DEPTNO, JOB

If you did not add the WHERE clause to this query, all rows would be retrieved from the database, regardless of what you selected in Q_Dept and Q_Job.

Note:

If you added a WHERE clause that did not use the subqueries (for example, WHERE EMP.DEPTNO = DEPT.DEPTNO), the query would be executed once for each combination of values in the cross-product. This can lead to excessive execution of the filler query, if the cross-product has a lot of combinations.