|Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)
Part Number B32122-01
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.
The role settings in the report are overridden if you specify a role using the command line with Reports Runtime (
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.
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.
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.
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:
SELECT DEPTNO FROM DEPT WHERE DEPTNO < 100
SELECT DISTINCT JOB FROM EMP WHERE DEPTNO < 100
To ensure that your cell query only retrieves the records that are necessary, you would write the following
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.