What are charts?

Definition

Charts display data from database tables or views as bar charts. The example below shows a chart based on the SCOTT.EMP table that displays employee names as labels and chart bars representing each employee's salary.

Charts are based on at least two table or view columns:

Values in the Label column identify the bars on the chart; for example, ALLEN, JAMES, MARTIN, etc.
Those in the Value column calculate the size of the bars on the chart relative to one another. Value columns must always contain numeric data.

You can also specify a Link column. Values in this column create hypertext links from the chart's labels to other OracleAS Portal database portlets or URLs. The above example contains links from the employee name labels to another database portlet; for example, a form that allows an end user to update the employee's salary.

Building charts

You can build a chart using one of three build wizards:

A Chart from Query Wizard guides you through all steps for creating a chart, including creating the SQL query that selects the data displayed in the chart. If you are unfamiliar with SQL or are new to OracleAS Portal, you may want to create your chart using this wizard.
A Chart from SQL Query allows you to write your own SQL query that selects the data displayed in the chart. 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 Chart from Query Wizard.
An Image Chart from Query Wizard (Beta level) guides you through all steps for creating a Java-based chart.

Using Group By functions in charts

Charts are an effective way of displaying aggregate data. You can use Group By and Summary Options in the wizard to sum the column values returned by your query; for example, the minimum, maximum, or average value; or the number of values in a column.

Setting other options

You can set options to control the size and color of chart bars, label fonts, and background colors. The final chart can be displayed on a web page or downloaded in SYLK format to Excel. You can also provide end users of the chart with the option to execute charts based on large amounts of data in batch mode rather than real-time.

Examples

Example chart SQL query using an expression (sysdate - hiredate)

This chart shows the number of days from the employee's date of hire to today.

select
  null the_link,
  ENAME the_name,
  sysdate-hiredate the_data,
from SCOTT.EMP
group by JOB

Example SQL query using GROUP by expression

The chart created by this query is shown below.

select
  null the_link,
  JOB the_name,
  avg(SAL) the_data,
from SCOTT.EMP
group by JOB

Example group by chart

Example Chart SQL query with link to another database portlet

Syntax for link specification:

<portal schema name>.wwv_user_utilities.get_url(
'<app name>.<link name>',
'<link parameter name 1>', <link parameter value 1>,
'<link parameter name 2>', <link parameter value 2>,
...
'<link parameter name n>', <link parameter value n>)

The maximum value for n is 30.
<link parameter name> is the parameter name of the link target database portlet.
If the <link parameter value> is static, it must be quoted; if the <link parameter value> is from a column, specify the column name.

The following example provides the SQL for a chart that links to a form. The example assumes that a link (LINK_TO_FORM) has been created that links to a form based on SCOTT.EMP.

select portal30.wwv_user_utilities.get_url(
'APP_1.LINK_TO_FORM',
'empno', EMPNO,
'_empno_cond', '=') the_link,
ENAME the_name,
SAL the_data
from SCOTT.EMP
order by SAL DESC

The value for parameter 'empno' is from column EMPNO.
The value for parameter '_empno_cond' is a static value of '='.
If you link to a form, you MUST specify the condition. The condition name for a parameter is _<parameter>_cond.

Example Chart SQL query with link to a URL

The following example provides the SQL query to use a link that has been built based on a URL:

select
'http://www.oracle.com' the_link,
ENAME the_name,
SAL the_data
from SCOTT.EMP
order by SAL desc

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

The following SQL query creates a simple customization form for a chart. :SALARY 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 is shown below.

select
null the_link,
ENAME the_name,
SAL the_data,
from SCOTT.EMP
where SAL > :SALARY
order by SAL desc

Additional Information

On Portal Center:

For more information go to the documentation section of Portal Center.