Skip Headers
Oracle® Application Express Application Builder User's Guide
Release 4.1

Part Number E21674-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Creating Charts

Application Builder includes built-in wizards for generating HTML and Flash charts, and supports the editing of SVG charts. Oracle Application Express supports only those three types of graphical charts.

Topics:

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   ...

Creating an HTML Chart

How you create a chart depends upon whether you are adding the chart to an existing page, or adding a chart on a page. This chart type creates a bar chart showing one data series with each data point represented by a bar.

Topics:

Adding an HTML Chart to an Existing Page

To add an HTML chart to an existing page:

  1. Navigate to the Page Definition. See "Accessing the Page Definition".

  2. On the Page Definition, create a new region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. For Region, select HTML Chart and click Next.

  5. For Display Attributes:

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

    2. Click Next.

  6. For Source:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

  7. Click Create Region.

Adding an HTML Chart to a New Page

To create an HTML chart on a new page:

  1. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

  3. Click Create Page.

  4. Select Chart and click Next.

  5. For Region, select HTML Chart and click Next.

  6. For Page Attributes:

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

    2. Click Next.

  7. Specify whether to include tabs and click Next.

  8. For Chart Definition:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

    3. Click Next.

  9. Click Finish.

Creating a Flash Chart

Flash chart support in Oracle Application Express is based on the AnyChart Flash Chart Component.

Topics:

About Flash Charts

Flash chart support in Oracle Application Express is based on the AnyChart Flash Chart Component. AnyChart is a flexible Macromedia Flash-based solution that enables developers to create animated, compact, interactive flash charts. Flash charts are rendered by a browser and require Flash player 9 or later.

http://www.anychart.com

Adding a Flash Chart to an Existing Page

To add a flash chart to an existing page:

  1. Navigate to the Page Definition. See "Accessing the Page Definition".

  2. On the Page Definition, create a new region:

    • Tree view- Under Page Rendering, right-click Regions and select Create.

    • Component view - Under Region, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. Select Flash Chart and click Next.

  5. For Chart Type:

    1. Select a chart style (for example, Column) and click Next.

    2. Select a specific chart type (for example, 3D Column) and click Next.

  6. For Display Attributes

    1. Specify the following:

      • Title

      • Region Template

      • Parent Region

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

    2. Click Next.

  7. For Display Attributes, select the appropriate attributes and click Next.

    To learn more about a specific attribute, click the item label.

  8. For Source:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify relevant chart attributes. To learn more, click the item label.

  9. Click Create Region.

Adding a Flash Chart to a New Page

To create a flash chart on a new page:

  1. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

  3. Click Create Page.

  4. Select Chart and click Next.

  5. Select Flash Chart and click Next.

  6. For Chart Type:

    1. Select a chart style (for example, Column) and click Next.

    2. Select a specific chart type (for example, 3D Column) and click Next.

  7. For Display Attributes

    1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Breadcrumb

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

    2. Click Next.

  8. Specify whether to include tabs and click Next.

  9. For Display Attributes, select the appropriate attributes and click Next.

    To learn more about a specific attribute, click the item label.

  10. For Query:

    1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify the remaining attributes. To learn more, click the item label.

    3. Click Next.

  11. Click Finish.

Upgrading all Flash 3 Charts to Flash 5

To upgrade all Flash 3 charts in an application to Flash 5:

  1. On the Workspace home page, click the Application Builder icon.

    The Application Builder home page appears.

  2. Select an application.

    The Application home page appears.

  3. Click Utilities and then click Upgrade Application.

    The Upgrade Application Summary report appears.

  4. For Upgrade Flash Charts to Flash Chart 5, click the number of candidate objects.

  5. Select the objects to upgrade and click Upgrade.

Upgrading a Flash 3 Chart to Flash 5

To upgrade a Flash 3 charts Flash 5:

  1. Navigate to the Page Definition. See "Accessing the Page Definition".

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. From the Tasks list, click Upgrade to New Flash Chart.

  4. Click Upgrade.

About SVG Charts

Previous releases of Oracle Application Express supported the creation of SVG charts. Although existing SVG charts are still supported, Oracle Application Express no longer supports the creation of new SVG charts.

Topics:

About Upgrading SVG Charts to Flash

This section describes restrictions that apply when upgrading SVG charts to Flash and also describes the instructions for upgrading.

Topics:

You can automatically upgrade single or multiple SVG charts to Flash.

About SVG Chart Upgrade Restrictions

Note that SVG charts can be upgraded with the following restrictions:

  • Only number formats defined in axis format strings will be migrated. Date and time formats will be ignored.

  • Number format elements containing the following will be migrated:

    0,9,D,G,, (comma),. (period),$,C,L,FM
    
  • The label for each series in the Flash chart will be derived from each series' column alias. This differs from SVG charts, where the label for each series was derived from the Series Name attribute.

  • Flash Dial charts display actual values instead of percentages.

  • In SVG charts, only the labels for the first series are used for the x-axis. In Flash charts, this has been enhanced so that all data appears, even if the data's label does not occur in the first series.

Upgrading a SVG Chart to Flash

To upgrade a SVG chart to Flash:

  1. Navigate to the Page Definition. See "Accessing the Page Definition".

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. From the Tasks list, click Upgrade to New Flash Chart.

  4. Click Upgrade.

Upgrading all SVG Charts in an Application to Flash

To upgrade all SVG Charts to Flash charts:

  1. On the Workspace home page, click the Application Builder icon.

    The Application Builder home page appears.

  2. Select an application.

    The Application home page appears.

  3. Click Utilities and then click Upgrade Application.

    The Upgrade Application Summary report appears.

  4. For Upgrade SVG Charts to Flash Chart 5, click the number of candidate objects.

  5. Select the objects to upgrade and click Upgrade.

Understanding SVG Chart Cascading Style Sheet Classes

When you create a SVG chart, Oracle Application Express renders it based on cascading style sheet (CSS) classes associated with the current theme. You can change the appearance of a chart by referencing another CSS or by overriding individual classes in the CSS section of the Edit Attributes page

The following sample contains the CSS classes for the dial chart in Sample Application. This example contains all the available CSS classes. Class names appear in boldface.

text{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
tspan{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
text.title{font-weight:bold;font-size:14;fill:#000000;}
text.moredatafound{font-size:12;}
rect.legend{fill:#EEEEEE;stroke:#000000;stroke-width:1;}
text.legend{font-size:10;}
#background{fill:#FFFFFF;stroke:none;}
rect.chartholderbackground{fill:#ffffff;stroke:#000000;stroke-width:1;}
#timestamp{text-anchor:start;font-size:9;}
text.tic{stroke:none;fill:#000000;font-size:12}
line.tic{stroke:#000000;stroke-width:1px;fill:none;}
#dial{stroke:#336699;stroke-width:2px;fill:#336699;fill-opacity:.5;}
#dial.alert{fill:#FF0000;fill-opacity:.5;}
#dialbackground{stroke:#000000;stroke-width:none;fill:none;filter:url(#MyFilter);}
#dialcenter{stroke:none;fill:#111111;filter:url(#MyFilter);}
#dialbackground-border{stroke:#DDDDDD;stroke-width:2px;fill:none;filter:url
(#MyFilter);}#low{stroke-width:3;stroke:#336699;}
#high{stroke-width:3;stroke:#FF0000;}
#XAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;}
#YAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;writing-mode:tb;}
.XAxisValue{font-size:8;fill:#000000;}
.YAxisValue{font-size:8;fill:#000000;text-anchor:end;}
.nodatafound{stroke:#000000;stroke-width:1;font-size:12;}
.AxisLine{stroke:#000000;stroke-width:2;fill:#FFFFFF;}
.GridLine{stroke:#000000;stroke-width:0.3;stroke-dasharray:2,4;fill:none;}
g.dataholder rect{stroke:#000000;stroke-width:0.5;}
.legenditem rect{stroke:#000000;stroke-width:0.5;}

Table 10-4 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

Table 10-4 Available SVG Chart CSS Classes

Class Description

text

Defines the appearance of text that displays in a chart.

tspan

Defines the appearance of text that displays in a chart. tspan should match the definition of text.

text.title

Overrides the default chart text. Use this class for title text.

text.moredatafound

Defines the appearance of more datafound text.

rect.legend

Creates the rectangular box that holds the chart legend.

To remove the legend border, change rect.legend to the following:

rect.legend{fill:#CCCC99;stroke:none;} 

text.legend

Defines the text that appears in the chart legend.

#background

Creates the entire background for the SVG plug-in.

For a solid white background with no border, change #background to the following:

#background{fill:#FFFFFF;stroke:#FFFFFF;stroke-width:2;}

rect.chartholderbackground

Not applicable to pie and dial charts. Creates the background of the rectangle that holds the chart data.

For a clear background, change rect.chartholderbackground to the following:

rect.chartholderbackground(display:none;)

#timestamp

Only applicable if the Asynchronous Update chart attribute is set to Yes. Controls the appearance of the update timestamp test.

To disable the display of the timestamp, use defines #timestamp as follows in the Custom CSS, Inline attribute.

"#timestamp{display:none;}" 

See Also: "Enabling Asynchronous Updates"

text.tic

Dial charts only. Defines the numbers on a dial chart.

line.tic

Dial charts only. Defines the graduation mark that displays directly beneath the number on a dial chart.

#dial

Dial charts only. Defines the value that displays on the dial chart.

#dial.alert

Dial charts only. Defines a value (called an alert value) that renders in a dial chart using a different display.

#dialbackground

Dial charts only. Creates the background of a dial chart.

#dialcenter

Dial charts only. Creates the center of the dial on a dial chart.

#dialbackground-border

Dial charts only. Works with #dialbackground to create specific graphic effect.

#low

Dial charts only. Defines the historical low watermark of the data being displayed on a chart.

#high

Dial charts only. Defines the historical high watermark of the data being displayed on a chart.

#XAxisTitle

Defines the title that appears on the x-axis

#YAxisTitle

Defines the title that appears on the y-axis.

.XAxisValue

Defines the value that appears on the x-axis.

.YAxisValue

Defines the value that appears on the y-axis.

.AxisLabel

Similar to the axis value.

.nodatafound

Defines the text element that displays if no information is available.

.AxisLine

Indicates zero on charts that have negative values.

.GridLine

Creates the horizontal and vertical lines on the chart.

g.dataholder rect

Applies a blanket style to all data that displays in the chart.

.legenditem rect

Applies a blanket style to all rectangular items in the legend.


Referencing a Custom SVG Chart Cascading Style Sheet

You can reference a custom cascading style sheet for a chart using the CSS section of the Chart Attributes page. When you reference an external CSS, you can reference it entirely or simply override specific styles.

To reference a custom chart CSS:

  1. Upload the CSS to Application Builder. See "Uploading Cascading Style Sheets".

  2. Navigate to the Page Definition containing the chart. See "Accessing the Page Definition".

  3. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  4. Scroll down to the CSS section.

  5. From Use Custom CSS, select Yes.

  6. To reference an external CSS exclusively:

    1. In Custom CSS, Link, enter a link to a custom CSS. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      
    2. Specify that the CSS should be used exclusively. In Custom CSS, Inline enter the following:

      /**/
      
  7. To reference a custom CSS and override specific styles:

    1. In Custom CSS, Link, enter a link to a custom style sheet. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      
    2. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Specifying Custom CSS Styles Inline for SVG Charts

You can override specific styles within the default CSS, using the Custom CSS, Inline attribute on the Chart Attributes page.

To override specific styles within the default CSS:

  1. Create a chart. See "About SVG Charts".

  2. Navigate to the Page Definition. See "Accessing the Page Definition".

  3. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  4. Scroll down to CSS.

  5. From Use Custom CSS, select Yes.

  6. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Displaying SVG Charts in Other Languages

To display an SVG chart in another language, you edit the text and tspan classes to reflect the correct language.

To display an SVG chart in another language:

  1. Navigate to the Chart Attributes page. See "Editing Chart Attributes".

  2. Scroll down to CSS.

  3. From Use Custom CSS, select Yes.

  4. In Custom CSS, Inline, edit the text and tspan classes to reflect the correct language. The following example demonstrates how to change a chart to Korean:

    text{font-family:Batang;fill:#000000;}
    tspan{font-family:Batang;fill:#000000;}
    

Editing Chart Attributes

Once you have created a chart, you can alter its display by editing chart attributes on the Chart Attributes page.

To access the Chart Attributes page:

  1. Navigate to the Page Definition. See "Accessing the Page Definition".

  2. Access the Chart Attributes page:

    • Tree view- Under Page Rendering, double-click the region name.

    • Component view - Under Region, select the region name.

      The Region Definition appears.

  3. Click Chart Attributes.

  4. Edit the appropriate attributes.

  5. To learn more about a specific item on a page, click the item label.

    When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

  6. Click Apply Changes.

Tip:

Removing the chart title of an SVG chart (that is, the Chart Title attribute) may negatively impact the location and display of the chart legend.

About Navigation Alternatives

The Chart Attributes page is divided into sections. You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

About Saving Flash Charts

To support the saving of an image, AnyChart requires the use of a server-side script that resides on http://www.anychart.com. When you attempt to save an image, the data is sent to the server in Base64 encoding. It is then decoded on the server and the decoded response is returned.

If you wish to support the saving of charts locally, you need to host the decoding script on your local server. The Flash chart would also require you use of the AnyChart JavaScript function getPNG. By default, the chart region uses embedded HTML tags and does not use any AnyChart JavaScript calls. To make use of the AnyChart JavaScript call, you need to modify the region source of your Flash charts.

Seel Also:

"Saving chart as image or vector file" in AnyChart User's Guide for more information on adding support to save Flashcharts locally.

Using Custom XML with Flash Charts

There are additional chart settings that cannot be controlled using the standard attributes on the Chart attributes page. To further control the look and feel of a chart, you can use custom XML.

To use custom XML:

  1. Navigate to the Chart Attributes page:

    1. Navigate to the Page Definition. See "Accessing the Page Definition".

    2. Under Regions, click the chart type, Flash Chart.

      The Chart Attributes page appears.

  2. Scroll down to Chart XML.

  3. From Use Custom XML, select Yes.

    Note that when you select to use custom XML, regions with attributes that no longer apply are hidden. To display these regions again, select No for Use Custom XML.

  4. Edit the XML.

  5. Click Apply Changes.

Tip:

For more information on supported XML format for charts, see the Online XML Reference at:
http://www.anychart.com

Enabling Asynchronous Updates

SVG and Flash charts can monitor information by enabling the Asynchronous Update attribute on the Chart attributes page. Enabling this attribute updates the chart to reflect changes in the underlying data within a specified time interval.

To enable asynchronous updates:

  1. Edit an existing SVG or Flash chart. See "About SVG Charts".

  2. Navigate to the Page Definition. See "Accessing the Page Definition".

  3. Under Regions, click Chart next to the region name.

    The Chart Attributes page appears.

  4. Scroll down to Refresh.

  5. From Asynchronous Update, select Yes.

  6. In Update Interval (Seconds), enter the interval in seconds between chart updates. For optimal performance, select an interval that is greater than 2 seconds.

When Asynchronous Update is enabled for an SVG chart, a timestamp displays on the chart indicating the last update.

To disable the Asynchronous Update timestamp:

  1. Navigate to the Chart Attributes page.

  2. Locate the CSS section.

  3. From Use Custom CSS, select Yes.

  4. In Custom CSS, Inline edit #timestamp as follows:

    #timestamp{display:none;}