12.3 Creating Charts

12.3.1.1 About HTML5 Charts

HTML5 chart support in Oracle Application Express is based on the AnyChart HTML5 Chart Component. AnyChart is a flexible Flash and JavaScript (HTML5) based solution that enables developers to create animated and compact interactive charts. HTML5 charts use a JavaScript chart engine, rendering the chart in SVG format. Flash cannot be rendered on most of the modern mobile devices, however you can now take advantage of our new HTML5 charting solution to incorporate charts in your mobile applications. HTML5 charts are compatible with popular browsers for the following mobile platforms:

• Android: Versions 3.1, 3.2, 4.0, 4.0.3, and 4.1

• IOS (iPhone, iPad, iPod Touch): Safari 3.2 and higher is required

To learn more see:

http://6.anychart.com/products/anychart/docs/

12.3.1.2 About Flash Charts

Flash chart support in Oracle Application Express is based on the AnyChart Flash Chart Component. AnyChart is a flexible Flash and JavaScript-based solution that enables developers to create animated, compact, interactive charts. Flash charts are rendered by a browser and require Flash player 9 or later. With AnyChart 6.0, Flash charts are rendered using the FLASH_PREFERRED rendering type. If Flash Player is not available on the device displaying the chart (for example, pages displayed on an iPhone), AnyChart automatically switches to the HTML5 chart engine and displays an SVG-based chart.

12.3.2.1 Standard Chart Syntax

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

12.3.2.2 Dial Chart Syntax (Flash and HTML5)

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

12.3.2.3 Multiple Series Syntax (Flash and HTML5)

For column charts and line 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 chart, use the following syntax:

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

12.3.2.4 Range Chart Syntax (Flash and HTML5)

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

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

12.3.2.5 Scatter Chart Syntax (Flash and HTML5)

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

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

12.3.2.6 Candlestick Chart Syntax (Flash and HTML5)

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

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

12.3.2.7 Gantt Chart Syntax (Flash Only)

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

To represent parent-child hierarchical data on a Resource Gantt chart, provide a SQL query using START WITH..CONNECT BY syntax:

SELECT link,
           resource_id,
           resource_name,
           resource_parent_id,
           actual_start_date,
           actual_end_date,
FROM ...
START WITH resource_parent_id IS NULL
CONNECT BY PRIOR resource_id = resource_parent_id
ORDER SIBLINGS BY resource_name

12.3.4.1 Adding a Chart in Page Designer

To add a chart to an existing page in Page Designer:

1. View the page to contain the item in Page Designer. See "Viewing a Page in Page Designer."

2. From the Gallery select the Chart region and drag it to the appropriate location in Grid Layout.

   Tip:

   You also right-click Tree region, select Add To, and select the appropriate location.

   Page Designer indicates what actions are required next.

   The Messages tab displays a red or yellow badge indicating messages you need to address. The Message tab displays two types of messages:

   • Errors - Error messages display in red. Selecting an error message displays the associated attribute in red in the Property Editor. You must address errors before a page can be saved.

   • Warnings - Warning messages display in yellow. Selecting a warning message displays the associated attribute in yellow in the Property Editor. You must address errors before a page can be saved. Note you can save a page without addressing warning messages.

3. View all attributes in the Property Editor. Select Show All from the Property Editor Menu.

4. In Property Editor - Region, edit the required attributes.

   Required attributes display a red triangle in the upper left corner next above attribute label.

   Tip:

   To learn more about an attribute, select the attribute in the Property Editor and click the Help tab in the center pane.

5. Edit the chart attributes:

   1. In the Rendering tree, select the Attributes node.

      Property Editor - Attributes appears.

   2. Edit the required chart attributes.

      Required attributes display a red triangle in the upper left corner next above attribute label.

   3. Click Next.

6. Edit the series:

   1. In the Rendering tree, select the Series node.

      Property Editor - Series appears.

   2. Edit the required chart attributes.

   3. Click Next.

7. Click Save.

12.3.4.2 Editing Chart Attributes in Page Designer

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. View the page to contain the chart in Page Designer. See "Viewing a Page in Page Designer."

2. In the Rendering tree, select the Attributes node.

   Property Editor - Attributes appears.

3. View all attributes in the Property Editor. Select Show All from the Property Editor Menu.

4. Edit the chart attributes.

   Required attributes display a red triangle in the upper left corner next above attribute label.

5. Click Save.

12.3.4.3 Switching an HTML5 Chart to a Flash Chart

Once you create an HTML5 chart, you can switch its rendering to Flash by editing chart attributes on the Chart Attributes page.

To switch an HTML5 chart to a Flash Chart:

1. View the page to contain the chart in Page Designer. See "Viewing a Page in Page Designer."

2. In the Rendering tree, select the Attributes node.

   Property Editor - Attributes appears.

3. Expand the Appearance section.

4. For Appearance, Rendering, select Flash Chart.

   To learn more about an attribute, see field-level Help. See "Viewing Field-Level Help."

5. Click Save.

12.3.4.4 Enabling Asynchronous Updates

SVG, Flash, and HTML5 charts can monitor information by enabling the Asynchronous Update attribute on the Chart attributes page or using a dynamic action with the Refresh action.

• Enabling the Asynchronous Update Attribute

• Creating a Refresh Dynamic Action

12.3.5.1 Adding an Chart in Component View

To add a chart to an existing page in Component View:

1. Navigate to the page. See "Viewing a Page in Component View".

2. Create a new region. Under Region, click the Create icon.

   The Create Region Wizard appears.

3. On Create Region, select Chart and click Next.

4. For Chart Type:

   1. Chart Rendering - Select a chart type. See "About Chart Types."

   2. Chart Type - Specify a chart type. Depending upon the chart type, you may be prompted to make additional selections.

   3. Click Next.

5. For Display Attributes:

   1. Title - Enter a title for the region. This title displays if the region template you choose displays the region title.

   2. Region Template - Enter a title for the region. This title displays if the region template you choose displays the region title.

   3. Parent Region - Defines the parent region to which the new region belongs to.

   4. Display Point - Identify a display point for this region. To learn more, see field-level Help.

      To learn more, see field-level Help. See "Viewing Field-Level Help."

   5. Click Next.

6. For Chart Attributes, specify the appropriate attributes and click Next.

   To learn more about an attribute, see field-level Help.

7. For Source:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "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 about an attribute, see field-level Help.

8. For Conditional Display:

   1. Condition Type - Select a condition type from the list that must be met in order for this component to be rendered processed. See "Understanding Conditional Rendering and Processing."

   2. Authorization Scheme - Optionally select an authorization scheme which must evaluate to TRUE in order for this component to be rendered or otherwise processed. See "Providing Security Through Authorization."

   3. Click Create Region.

12.3.5.2 Switching an HTML5 Chart to a Flash Chart

Once you create an HTML5 chart, you can switch its rendering to Flash by editing chart attributes on the Chart Attributes page.

To switch an HTML5 chart to a Flash Chart:

1. Navigate to the page. See "Viewing a Page in Component View".

2. Access the Chart Attributes page:

   1. Under Region, select the region name.

      The Region Definition appears.

   2. Click Chart Attributes.

3. For Chart Type:

   1. Chart Rendering - Select Flash Chart.

   2. Chart Type - Specify a chart type. Note that 3D chart types are only supported for Flash charts, so you may want to change the chart type to use one of the 3D options.

      To learn more about an attribute, see field-level Help. See "Viewing Field-Level Help."

4. Click Apply Changes.

12.3.5.3 Editing Chart Attributes in Component View

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. See "Viewing a Page in Component View."

2. Access the Chart Attributes page. Under Region, select the region name.

3. Edit the appropriate attributes.

4. To learn more about an attribute, see field-level Help. See "Viewing Field-Level Help."

5. Click Apply Changes.

12.3.5.4 Enabling Asynchronous Updates

SVG, Flash, and HTML5 charts can monitor information by enabling the Asynchronous Update attribute on the Chart attributes page or using a dynamic action with the Refresh action.

• Enabling the Asynchronous Update Attribute

• Creating a Refresh Dynamic Action

12.3.6.1 Upgrading SVG Charts to HTML5

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

• About SVG Chart Upgrade Restrictions

• Upgrading an SVG Chart to HTML5

• Upgrading all SVG Charts in an Application to HTML5

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

12.3.6.2 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 12-1 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

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

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

rect.chartholderbackground(display:none;)

"#timestamp{display:none;}" 

12.3.6.3 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 "About Uploading Cascading Style Sheets".

2. Navigate to the page containing the chart. See "Viewing a Page in Component View".

3. Access the Chart Attributes page:

   1. Under Region, select the region name.

      The Region Definition appears.

   2. Click Chart Attributes.

4. On the Chart Attributes page, 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.

12.3.6.4 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 "Understanding SVG Chart Support."

2. Navigate to the page. See "Viewing a Page in Component View."

3. Access the Chart Attributes page:

   1. Under Region, select the region name.

      The Region Definition appears.

   2. Click Chart Attributes.

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.

12.3.6.5 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 page. See "Viewing a Page in Component View."

2. Access the Chart Attributes page:

   1. Under Region, select the region name.

      The Region Definition appears.

   2. Click Chart Attributes.

3. Scroll down to CSS.

4. From Use Custom CSS, select Yes.

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

12.3.7.1 Upgrading an HTML Chart to HTML5

To upgrade an HTML chart to HTML5:

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. Navigate to the page. See "Viewing a Page in Component View".

4. Access the Chart Attributes page:

   1. Under Region, select the region name.

      The Region Definition appears.

   2. Click Chart Attributes.

5. From the Tasks list, click Upgrade to HTML5 Chart.

6. Click Upgrade.

12.3.7.2 Upgrading All HTML Charts to HTML5

To upgrade all HTML charts to HTML5:

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 click Upgrade Application.

   The Upgrade Application Summary report appears.

4. For Upgrade HTML Charts to HTML5 Charts, click the number of candidate objects.

5. Select the objects to upgrade and click Upgrade.