Creating Charts

Application Builder includes built-in wizards for generating HTML, Scalable Vector Graphics (SVG), and Flash charts. Oracle Application Express supports only those three types of graphical charts.

Topics in this section include:

See Tutorial:

"How to Create a Stacked Bar Chart" in Oracle Application Express Advanced Tutorials

About Supported Chart Types

Oracle Application Express supports three types of graphical charts: HTML, Scalable Vector Graphics (SVG), and Flash.

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 8 or later. Flash charts used in interactive reports require Flash Player 9 or later. For more information about Anychart, go to

http://www.anychart.com

About SVG Plug-in Support

SVG is an XML-based language for Web graphics from the World Wide Web Consortium (W3C). SVG charts are defined using an embed tag. When evaluating whether an SVG chart is the appropriate chart type for your application, remember that:

  • Some Web browsers do not support SVG charts.

  • Most Web browsers that support SVG charts require that users download an SVG plug-in.

The Adobe SVG plug-in can handle data encoded in UTF-8, UTF-16, ISO-8859-1, and US-ASCII. Encoding of an SVG chart is determined by the database access descriptor (DAD) database character set. If the DAD character set is not UTF8, AL32UTF8, AL16UTF16, WE8ISO8859P1, or US7ASCII, SVG charts may not render properly in the Adobe SVG plug-in.

See Also:

"Creating a SVG Chart" and "About Migrating SVG Charts to Flash"

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

Note:

Do not change the type of an existing chart. Instead, delete the existing chart and then re-create it.

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

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 in this section include:

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 a Page Definition".

  2. Under Regions, 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

      • 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:

      • Page Number

      • Page Name

      • Region Template

      • Region Column

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

  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 SVG Chart

Oracle Application Express supports a number of different SVG charts. To see a complete listing, see "About SVG Chart Types".

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

Topics in this section include:

See Also:

"About SVG Plug-in Support" and "About Migrating SVG Charts to Flash"

Adding a SVG Chart to an Existing Page

To add a SVG chart to an existing page:

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

  2. Under Regions, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. For Region, select SVG Chart.

  5. For Region, select a chart type and click Next. See "About SVG Chart Types".

  6. For Display Attributes

    1. Specify the following:

      • Title

      • Region Template

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

  8. Click Create Region.

Adding a SVG Chart to a New Page

To create a SVG 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 SVG Chart.

  6. Select a chart type and click Next. See "About SVG Chart Types".

  7. For Page Attributes:

    1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Chart Color Theme

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

  10. Click Finish.

About SVG Chart Types

Table 7-7 describes the SVG chart types available in Application Builder.

Table 7-7 Available SVG Chart Types

Chart Type Description

Bar, Horizontal

Single series-based bar chart oriented horizontally with each data point in the series represented by a bar.

SVG-based. Requires an SVG plug-in.

Bar, Vertical

Single series-based bar chart oriented vertically with each data point in series represented by a bar.

SVG-based. Requires an SVG plug-in.

Cluster Bar, Horizontal

Multiple series-based bar chart oriented horizontally and clustered by a common variable with each data point in the series represented by a bar (for example, Department sales total clustered by month of year).

SVG-based. Requires an SVG plug-in.

Cluster Bar, Vertical

Multiple series-based bar chart oriented vertically clustered by a common variable with each data point in series represented by a bar (for example, Department sales total clustered by month of year).

SVG-based. Requires an SVG plug-in.

Dial - Sweep

Also known as an angular gauge; this chart shows either percentage of maximum value or absolute value compared to a maximum value represented as a solid area.

SVG-based. Requires an SVG plug-in.

Dial

Also known as angular gauge; this chart shows either percentage of maximum value or absolute value compared to maximum value represented as a line.

SVG-based. Requires an SVG plug-in.

Line

Multiple series-based line chart oriented with each line representing all data points in the series.

SVG-based. Requires an SVG plug-in.

Pie

Single series-based pie chart with each slice representing a data point in the series.

SVG-based. Requires an SVG plug-in.

Stacked Bar, Horizontal

Multiple series-based bar chart oriented horizontally with each data point being an absolute value in the series representing a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Bar, Vertical

Multiple series-based bar chart oriented vertically with each data point being an absolute value in the series representing a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Percentage Bar, Horizontal

Multiple series-based bar chart oriented horizontally with each data point being a percentage of 100% of the series represented by a segment of a single bar.

SVG-based. Requires an SVG plug-in.

Stacked Percentage Bar, Vertical

Multiple series-based bar chart oriented vertically with each data point being a percentage of 100% of the series represented by a segment of a single bar

SVG-based. Requires an SVG plug-in.

Understanding SVG Chart Cascading Style Sheet Classes

When you create a new 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 7-8 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

Table 7-8 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. Create a chart. See "Creating a SVG Chart".

  3. Navigate to the Page Definition. See "Accessing a Page Definition".

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

    The Chart Attributes page appears.

  5. Scroll down to the CSS section.

  6. From Use Custom CSS, select Yes.

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

      /**/

  8. 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 "Creating a SVG Chart".

  2. Navigate to the Page Definition. See "Accessing a 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.

Creating a Flash Chart

How you create a flash chart depends upon whether you are adding the chart to an existing page, or adding a chart on a new page.

Tip:

Note that in order to view Flash charts, you must install Flash Player 8 or later. Flash charts used in interactive reports require Flash Player 9 or later.

Topics in this section include:

See Also:

"About Flash Charts" and "About Migrating SVG Charts to Flash"

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 a Page Definition".

  2. Under Regions, click the Create icon.

    The Create Region Wizard appears.

  3. Select Chart and click Next.

  4. For Region, select Flash Chart.

  5. For Display Attributes

    1. Specify the following:

      • Title

      • Region Template

      • 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. On Chart Preview, configure the chart attributes. Click Update to refresh the preview image.

  7. Click Next.

  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.

  6. For Page 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.

  7. Specify whether to include tabs and click Next.

  8. For Chart Preview, configure the chart attributes. Click Update to refresh the preview image.

  9. Click Next.

  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.

About Migrating SVG Charts to Flash

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

About SVG Chart Migration Restrictions

Note that SVG charts are migrated 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.

Migrating a SVG Chart to Flash

To migrate a SVG chart to Flash:

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

  2. Under Regions, click region name. The region name displays to the left of SVG Chart.

    The Region Definition appears.

  3. From the Tasks list, click Migrate SVG Chart to Flash Chart.

  4. Click Migrate.

Migrating all SVG Charts in a Application to Flash

To migrate all SVG Charts to Flash charts:

  1. Navigate to the Workspace home page.

  2. Click the Application Builder icon.

  3. Select an application.

    The Application home page appears.

  4. On the Tasks list, click Application Reports.

  5. Click Page Components.

  6. Under Regions, click Migrate SVG to Flash Charts.

    The Migrate SVG to Flash Charts page appears.

  7. To migrate specific charts, select the charts to be migrated and click Migrate Checked.

  8. To migrate all charts, click Migrate All.

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 a Page Definition".

  2. Under Regions, click the chart type (Chart, SVG Chart, or Flash Chart).

    The Chart Attributes page appears.

  3. Edit the appropriate attributes.

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

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

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

You can create SVG and Flash charts that 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. Create an SVG or Flash chart. See "Creating a SVG Chart" or "Creating a Flash Chart".

  2. Navigate to the Page Definition. See "Accessing a 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;}

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;}