About Creating SQL Queries for Charts

You define a chart in Application Builder using a wizard. For most chart wizards, you select a chart type and provide a SQL query using the following syntax:

SELECT link, label, value
FROM   ...

Where:

link is a URL.
label is the text that displays in the bar.
value is the numeric column that defines the bar size.

For example:

SELECT null, last_name, salary
FROM   employees
WHERE  DEPARTMENT_ID = :P101_DEPARTMENT_ID

Dial Chart Syntax

To create a dial chart, select a dial chart type and provide a SQL query using the following syntax:

SELECT value , maximum_value [ ,low_value [ ,high_value] ]
FROM   ...

Where:

value is the starting point on the dial.
maximum_value is the possible highest point on the dial.
low_value and high_value are the historical low and high values.

For example:

SELECT dbms_random.value(500, 1200), 1300, dbms_random.value(100, 200)
FROM DUAL

Multiple Series Syntax (Flash only)

For column charts and line Flash charts, you can define multiple series in one SQL query. The series names for these chart types are derived from the corresponding column aliases in the query. To define a multiple series Flash chart, use the following syntax:

SELECT link, label, series_1_value [, series_2_value [, ...]]
FROM   ...

Range Chart Syntax (Flash only)

Range charts require two values for each bar. To create a range chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, low_value, high_value 
FROM   ...

Scatter Chart Syntax (Flash only)

Scatter charts require an x value and y value for each point. To create a range chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, x_value, y_value 
FROM   ...

Candlestick Chart Syntax (Flash only)

Candlestick charts require open, low, high, and close values for each candlestick. To create a candlestick chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, label, open, low, high, close
FROM   ...

Gantt Chart Syntax

Project Gantt charts require a task name, task id, parent task id, actual start date, actual_end_date, and progress value for each task. Two optional values for planned start date and planned end date can also be used. To create a Project gantt chart, create a Flash chart and provide a SQL query using one of the following syntax:

SELECT link, task_name, task_id, parent_task_id, actual_start_date, actual_end_date, progress
FROM   ...

Resource Gantt charts require a resource id, resource name, parent resource id, actual start date, and actual end date value for each task. To create a Resource gantt chart, create a Flash chart and provide a SQL query using the following syntax:

SELECT link, resource_id, resource_name, resource_parent_id, actual_start_date, actual_end_date
FROM   ...