What are reports?

Reports display data from tables and views. You can base a report on multiple tables or views using a JOIN condition.

You can build the following types of reports in Oracle Portal:

Report from Query Wizard

The wizard guides you through all steps for creating a report, including creating the SQL query that selects the data displayed in the report. If you are unfamiliar with SQL or are new to Oracle Portal, you may want to create your report using this wizard.

Report from SQL Query

You can write your own SQL query that selects the data displayed in the report. After you write the query, the wizard allows you to set the same display, text, customization form, and PL/SQL options that you can set in the Report from Query Wizard.

Query by Example Report

Allows you to create a report for querying, inserting, updating, and deleting data from the table or view on which the report is based. In the QBE report build wizard, you choose which data to display in the report. Or, you can allow end users to make their own queries in the QBE report's customization form.

Oracle Portal allows you to highlight report rows, columns, or data in a single cell that satisfy conditions you specify. For example, you could display in bold red text the records of all employees in the SCOTT.EMP table who have been employed less than two years.

You can create hypertext links from values displayed in the report to other Oracle Portal database portlets or URLs. The above example contains links from the employee names to another database portlet; for example, a form that allows an end user to update the employee's salary.

The wizards allow you to specify other options that:

Limit data displayed in the report.
Sum column values
Use logical operators to select data within the columns
Format data in the report.
Format the report's customization form.
Specify PL/SQL code that executes at various points in the report.

Examples

Example of SQL-based report query with a link to another database portlet:

The following query creates a report that displays all employees from the SCOTT.EMP table. Values in the employee ID number column (EMPNO) link to Example_Form in the SCOTT schema. Note that the link is specified within an HTML anchor and that the anchor precedes the EMPNO column in the SQL query.

Hint: You can create a link using the Oracle Portal link wizard and copy it to your SQL query, as shown in the example below.

select
'<A HREF="'||'SCOTT.EXAMPLE_FORM.show?p_arg_names=EMPNO&
p_arg_values='||empno||'"> '||empno||'</A>'
empno, ename, deptno, mgr, sal
from SCOTT.EMP

This is the report created by the above query.

Example SQL query that uses a bind variable to create an entry field in a customization form for the report

The following SQL query creates a simple customization form for a report. :JOBS is a bind variable that adds an entry field to the form. End users of the form can choose a value from the SAL column of SCOTT.EMP for displaying the chart. The customization form and the report are shown below.

select deptno, ename, hiredate, job, sal, comm
from scott.emp
where job in (:jobs) or :jobs is null

After you code the SQL query, you can specify other options using pages in the Report from SQL Query wizard; for example, a List of Values for the Deptno entry field on the customization form.

Report from Query Wizard	The wizard guides you through all steps for creating a report, including creating the SQL query that selects the data displayed in the report. If you are unfamiliar with SQL or are new to Oracle Portal, you may want to create your report using this wizard.
Report from SQL Query	You can write your own SQL query that selects the data displayed in the report. After you write the query, the wizard allows you to set the same display, text, customization form, and PL/SQL options that you can set in the Report from Query Wizard.
Query by Example Report	Allows you to create a report for querying, inserting, updating, and deleting data from the table or view on which the report is based. In the QBE report build wizard, you choose which data to display in the report. Or, you can allow end users to make their own queries in the QBE report's customization form.