by Dimitri Gielis

This chapter will cover the charting possibilities in Oracle Application Express 5.0 and explain in great detail how APEX charts work behind the scenes.

In my experience, a lot of people who have been developing in APEX for years don’t use charts much or haven’t really invested time in reviewing all the options APEX provides. This is why, although this is an expert book, I will start this chapter by explaining what APEX charts are, how they work, and what types of charts you can create with the built-in functionality of APEX. In the second half of the chapter, I will go up a level and discuss how to produce more advanced charts.

The release of APEX 5.0 didn’t bring a lot of changes in charting compared to APEX 4.2 (but it did compared to APEX 4.0). The built-in charts in APEX still rely on the AnyChart 6 engine.

By understanding the different components of a chart and how APEX handles them, you’ll be able to visually display your data in almost any way you like. From combined charts to charts with thresholds to dashboards, this chapter will explain it all step-by-step. You’ll also learn how to make charts more interactive and combine them with other elements on the page; for example, when you change your chart, a report on the same page changes automatically based on the change in your chart, and the other way around.

Finally, you will look both at the future of charts in APEX and at how you can already benefit from the latest and greatest technologies in the charting world. Charting in general has evolved over time, with more new charting engines becoming available; D3 charts are one of the more popular ones. Integrating these chart engines into APEX is surprisingly simple. There are even some APEX plugins available that do the integration for you. You’ll find them in the Sample Charts packaged application.

APEX 5.0 includes two kinds of charts: HTML5 charts and Flash charts. The funny thing is that in APEX 4.0, HTML charts were limited, so we focused on Flash charts. Today it’s the other way around: Flash charts are basically dead because the browser needs to have an up-to-date plugin to be able to show Flash content. If the plugin is outdated, many browsers will block the Flash content. Flash eats resources too and doesn’t have the greatest performance. Making Flash content responsive isn’t as straightforward, and Flash doesn’t even run on many tablets (such as the iPad). When you create a Flash chart in APEX but run the chart on a device that doesn’t have Flash installed, APEX will automatically show the chart in HTML5. As I don’t really see any advantage to creating Flash charts, I decided to not spend too much time on Flash charts in this chapter.

There are different types of charts natively available in APEX, but you can categorize them into three big groups: charts, Gantts, and maps. This chapter will discuss charts in great detail and will combine them with other components such as reports later. Gantts and maps are not covered because they are still built using the Flash technology.

HTML5 Charts

Usually when you wanted to do serious charting in APEX 4.x, you used Flash charts. Flash charts offered many chart types, different animations, and different ways to adapt the look and feel. The AnyChart engine was used to render the charts, and initially only Flash charts were supported. Because the industry doesn’t like Flash anymore (because of the reasons highlighted previously), AnyChart had to follow, so it made its charting engine available in HTML5 too. APEX 4.2 included the new AnyChart engine and introduced HTML5 charts at that time. The wizards in APEX stayed the same; you could switch your Flash chart to an HTML5 chart and AnyChart would take care of the rest. APEX 5.0 continues on this path; there’s one chart wizard that offers you the different options for the chart type, and depending the rendering type, either Flash or HTML5, the chart is rendered in that technology.

Background

Flash charts were introduced with the release of APEX 3.0 in 2007. In addition to HTML and SVG charts, you could now create Flash-based charts. The Oracle APEX development team also made it clear that Flash-based charts would become the preferred charting engine and SVG wouldn’t be developed further. The APEX development team didn’t build the Flash charts from scratch and instead opted for a third-party solution. Oracle made an agreement with AnyChart (www.anychart.com) to license its Flash charts. What Oracle still needed to do was to create native Oracle APEX wizards to include these charts easily in an APEX project.

I believe Oracle made a great decision here in not trying to build a Flash chart engine because charting is a whole area in itself and evolves quickly. There were many charting engines around, but going with AnyChart was not a bad choice because the company was committed to evolving its product along with the rest of the charting world.

That AnyChart wouldn’t stand still proved to be true over time. In Oracle APEX 3.0, version 3.3 of the AnyChart product was included, but a year later AnyChart 4 was already out, and soon after that, so was version 5. With the patch releases of APEX 3.x, Oracle included newer versions of AnyChart, but it was only in the interactive reports that AnyChart 4 and AnyChart 5 were used.

With the release of APEX 4.0, AnyChart 5 was completely supported in the wizards. In fact, all new charts you created would automatically use the AnyChart 5 engine. As Oracle wanted APEX to be compatible with older versions and needed to support existing applications using Flash charts, AnyChart 3.3 was also included in APEX 4.0. So if you ran your APEX 3.x application in APEX 4.0, it would still show AnyChart 3.3 charts. Also note that the version of AnyChart 5 was a special version compiled for Oracle and didn’t include all the available chart types that the regular AnyChart 5 supported.

Over time, AnyChart developed other charting components. In addition to the AnyChart charting solution, it introduced AnyGantt, AnyMap, and AnyStock. In APEX 4.0, Oracle decided to also license AnyGantt Gantt charts and AnyMap interactive maps. The Oracle APEX development team built wizards around these engines, so you could create Gantt and Map types declaratively too.

With the release of APEX 4.2, AnyChart 6 was introduced, which replaced AnyChart 5. AnyChart 6 was backward compatible, and the main improvement of this version was the support of HTML5 charts. The same engine could render either in Flash charts or in HTML5 charts. The APEX wizards were changed, so more options became available (based on the AnyChart 6 engine), and you could just flip a switch to show your chart in HTML5 instead of Flash.

APEX 5.0 didn’t introduce many new features compared to APEX 4.2 for charts; the latest release of AnyChart 6 and AnyGantt 4 were included, and some wizards were updated to make things easier to understand (especially with the Page Designer).

AnyChart already released version 7, but this version is not included in APEX because AnyChart hasn’t made it backward compatible. AnyChart 7 is also a completely new code base, which means the APEX development team would need to spend a lot of effort in updating its code base to generate the correct settings for the chart.

Table 3-1 gives an overview of the versions of APEX and the evolution of the chart engines.

Creating a Chart

Let’s say you want to create a page with a chart in your APEX application.

Hit the Create Page button in Application Builder and select as the page type Chart (Figure 3-1).

On the next page of the wizard, select HTML5 Chart for Chart Rendering and select the chart type you want to use (Figure 3-2).

The Create Chart Wizard categorizes the charts nicely. You first select the main chart type and then get the choice to select a subtype if that is available. For example, the Line chart type doesn’t have any subtypes, whereas the Column chart type has seven subtypes (Figure 3-3).

Clicking the subtype brings you to the Page and Region Attributes page, which is not different from any other region screen in APEX (Figure 3-4). By default the region template is Standard, but you can choose any you like. Now give the region a title and click Next.

On the next screen, you can specify the Navigation Menu setting; just go with the default.

The next screen defines the look and feel of the chart and which options you want to include (Figure 3-5). You can go with the defaults during creation and adapt them later; or, if you already know exactly what you want, you can make the changes immediately. For example, if you want to include a legend, select the position where you’d like to see it (Left, Right, Top, Bottom, Float).

Previously, you defined how the chart will look, but you didn’t define the source of the chart yet. What data does the chart need to show? You’ll need to add a SQL query that will be used to feed the chart with data (Figure 3-6). The SQL query syntax varies depending on the chart type you select.

Most of the charts have a query like this:

select link, label, value
from   table
order by label

where

• link is a URL.
• label is the text that displays in the chart.
• value is the numeric column that defines the size of the chart type.

As I said, the query of the chart depends on the type of chart you selected. At the bottom of the page you’ll find some examples for the chart type you selected. This is handy to see the format of the SELECT statement. Alternatively, you can use the Build Query button to create the SQL statement with a wizard. I’ll come back to the different select statements for the different chart types in the section “Understanding the Chart Attributes.”

The Result

Once the page with the chart region is created, it will appear on the page with a chart icon in the Page Designer (Figure 3-7). Clicking the attributes shows the settings of the chart; clicking Series 1 shows the series, which contains the SQL statement to provide the chart with data.

If you’re using the Component View to develop, you will see the result in Figure 3-8.

Before you adapt the chart and look at what is happening behind the scenes, just run the page to see what it looks like (Figure 3-9).

Page Designer vs. Component View

From APEX 5 onward, the Tree View of APEX 4.x is replaced by the Page Designer. The Page Designer allows you to access the different components (and make changes) a lot more quickly. Many features of the Tree View are incorporated in the tree on the left in the Page Designer. It takes a bit of time to get used to the Page Designer, and a large monitor is recommended, but I definitely recommend giving it a try.

The Component View has existed the longest, and some people still prefer that view to develop. When you’re new to developing charts, the wizard kind of interface of the Component View might be a bit easier. When adding a chart region, you’ll see the same wizard as when you add a new page with a chart (as shown previously). The wizard also contains examples for the queries, something that the Page Designer lacks when you add a chart region to an existing page.

With the Page Designer, you drag a chart region on your page and then customize the attributes. When you’re more experienced with charts, the Page Designer allows you to add multiple charts more quickly, and adapting the attributes of the charts will be faster. All functionalities for the charts are the same regardless of which view you develop in, but some labels differ or are grouped a bit differently.

In the first half of this chapter, I will explain the different possibilities of the charts. I’ll use the Component View to show the screenshots because that is a bit easier than with the Page Designer. When I create more advanced charts, I’ll use the Page Designer.

Understanding the Chart Region

When editing the chart region (click the region name), you might be surprised at first because there is no Region Source setting (unlike with a report). You find the chart settings in the Chart Attributes section and the data part in the Series section. Behind the scenes, APEX will generate some HTML objects to render the chart (which is actually the region source), but because you can’t change this rendering, I won’t go much deeper into this.

Understanding the Chart Attributes

The previous section covered the region definition, but more importantly, if you want to change the appearance and behavior of a chart, you can do this via the chart attributes (just below the region name in the tree or the Chart Attributes tab in the Component View).

Depending on the chart type chosen, you have different sections in the chart attributes. As highlighted previously, the Component View (Figure 3-10) displays the options a bit differently than the Page Designer (Figure 3-11).

Again, to discuss the different settings, I’ll use the Component View screenshots, but all settings are also available in the Page Designer.

The Chart Settings options allow you to change the chart type, the title, the size of the chart, and the look and feel. Figure 3-12 shows the options, which you can set as follows:

• Chart Rendering: Flash or HTML5 Chart. Unless the chart type is not available as HTML5, I recommend going with HTML5 so that on every device the look and feel is the same.

• Chart Title: Set a title on top of the chart.
• Chart Type: Change between chart types in a given family. For example, you can change between 3D Stacked Column, 3D Bar Chart, 3D Stacked Bar Chart, and so forth.
• Chart Width and Height: Specify the size of the region in which the chart is rendered (in pixels). Leave this blank for 100 percent.
• Chart Margin: Specify the amount of blank space surrounding the chart. Values are in pixels (Top, Bottom, Left, Right).
• Color Scheme: Select one of the built-in color schemes for your chart. Single-series charts use one color for each data point. Multiple-series charts use one color for each series. Look 7 will use the AnyChart default palette of colors, applying a different color to each data point in a single series. Select the Custom option to define your own color scheme.
• Color Level: Series or Point: Series will use one color per series (for example, all bars in the same series will use blue), while Point will use a different color for every point (for example, all bars will use a different color regardless of the series they are in). Look 7 will always use different colors for all points.
• Hatch Pattern: Toggle hatch patterns off and on. In a multiseries bar chart, for example, it will show one series as bars with lines in them, another series as bars with stripes, and so forth. You can change explicit control of which hatch pattern is used through adapting the XML code.

The Chart Series options hold the select queries that are used to feed the chart with data. You can have one or more series defined. To edit an existing series, you click the Edit icon, while to add a new series, you click the Add Series button (Figure 3-13). In the next section, “Adding Multiple Series and Combined Charts,” I’ll go into more detail about the series.

In the Display Settings area, you define the main look and feel of the chart and the different features you want to enable. Figure 3-14 shows the following settings that you can control:

• Animation controls the initial appearance of this chart. There are 30 different types of animation available, such as Side from Left, Scale Y Top, and so on (this option is available only when Flash Chart is selected for Chart Rendering).
• Style defines the visual appearance of the data element, which is most apparent in 2D bar and column charts. There are four styles available in APEX 5.0: Default, Aqua Dark, Aqua Light, and Silver.
• Background Type has three choices. Transparent makes the chart background transparent. Solid Color uses the color specified in Background Color 1 for the background of the chart. Gradient uses Background Color 1 and 2 and fades between them depending the gradient angle specified lower down.
• Include on Chart allows you to select the options you want to display on your chart. Depending on the chart type, some options might not be available. The following are the options:
  • Hints: Select this box if you want to see the label and value when you hover your cursor over a data point on the chart.
  • Values: Select this box if you want to show the values next to the data points on the chart.
  • Labels: Select this box if you want to see the labels along the chart axis.
  • Group by Series: Select this box if you want to see your series split. Instead of seeing your data grouped by column (Allen: SAL, COMM – Blake: SAL, COMM – Clark: SAL, COMM), you will see it grouped by series first and then by column (SAL: Allen, Blake, Clark... – COMM: Allen, Blake, Clark...).
  • Major Ticks: Select this box if you want to see the big tick marks in a gauge or dial chart.
  • Minor Ticks: Select this box if you want to see the small tick marks between the big tick marks on your gauge (or dial) chart.
  • Tick Labels: Select this box if you want to see the values corresponding to the tick marks.
  • Multiple Y-Axes: Select the box if you want to see an extra y-axis positioned opposite to the existing y-axis on the chart. On a multiseries chart, the extra y-axis will be associated with the second series of the chart. If you customize the XML, you get even more choices. If you need more control than APEX gives you, check the AnyChart documentation for the XML you can use.
  • Invert X-Axis Scale: Select this box if you want the sorting to be reversed. For example, Adams – Ward becomes Ward – Adams.
  • Invert Y-Axis Scale: Select this box if you want the y-axis scale in an inverted mode. For example, selecting the box will cause a bar chart to go down instead of up.
  • Invert Scale: Select this box if you want the numbers to start from high to low on a Gauge (or Dial) chart.
  • Overlay Y-Axis: Select this box if you want bars shown one over another with multiple series (and same label). Depending on whether you use 2D or 3D charts, the behavior is different. You can experiment to see what looks best in your case.
  • Sorted Overlay Y-Axis: Select this box if you want series sorted and displayed with lower values in front of higher values.
  • Smart Auto Calculation Mode: Select this box if you want the y-axis to scale automatically based on the number of decimal digits and avoid duplicate items appearing on the axis scale.
  • Show Scrollbars controls whether a scrollbar will be displayed on your chart. You can show a scrollbar on the x-axis, on the y-axis, or on both (available only in Flash rendering).
  • Show Grid controls whether a value grid will be displayed on your chart. You can show the grid for the x-axis, for the y-axis, or for both.
  • Gradient Angle defines the angle for the background type of Gradient. A value of 0 degrees results in a horizontal gradient with the first background color on the left and the second background color on the right. A value of 90 degrees results in a vertical gradient with the first background color at the top and the second background color at the bottom.
  • X-Axis Label, Y-Axis Label, and Values Rotation define the amount of rotation, in degrees, for the chart labels. Positive values indicate clockwise rotation. Negative values indicate counterclockwise rotation. The Font Face setting for labels does not apply to rotated text. If the Y Axis Title setting contains non-ASCII characters, make sure you don’t have a value specified in the Y-Axis Label Rotation setting.

In the Axes Settings area shown in Figure 3-15, you define the title of the axis, the interval, and the format of the values.

• X Axis Title and Y Axis Title are used to describe the labels along the horizontal and vertical axes of your chart.
• X Axis Min/Max and Y-Axis Min/Max define the smallest and largest data values you want to appear on the corresponding axis. You see these items depending on the chart type, such as a column chart.
• X Axis Prefix and Y Axis Prefix define text to display before values on the corresponding axes. This text prefix will appear before grid labels, value labels, and hint text. For example, you can enter a currency symbol as a prefix.
• X Axis Postfix and Y Axis Postfix define text to display after values on the corresponding axes. This text postfix will appear after grid labels, value labels, and hint text. For example, you can enter a percentage symbol as a postfix.
• X-Axis Minor/Major Interval and Y-Axis Minor/Major Interval settings control the minor and major scale steps used for the axis labels, the tick marks, and the grid on your chart. If these are not set, the steps are calculated automatically. Values entered must be positive. These settings will be used only when Show Grid in Display Settings is set.
• Decimal Places defines the number of decimal places to be used in the y-axis values.

In the Legend Settings area (see Figure 3-16) you specify whether you want a legend and where it should appear and what the look and feel of it is.

• Show Legend specifies whether a legend is displayed on your chart. Possible positions are Left, Right, Top, Bottom, and Float.
• The Legend Title is the title of the Legend. If no Legend Title is entered, the title will be empty.
• Legend Element Layout defines whether the items of the legend will appear next to each other or under each other. The Legend Element Layout is applicable only when Show Legend is set to Top or Bottom.
• Show Legend Background specifies whether the legend background (white) is visible on your chart or whether the legend is transparent.

In the Font Settings area, you define the font face, font size, and font color of the different labels, values, hints, legend, and titles (Figure 3-17).

The Chart XML page shows the XML that APEX will send to the AnyChart chart engine (more on that in the section “Behind the Scenes”). See Figure 3-18.

Based on the previous settings, APEX will generate XML, so any change you make in the chart settings will be translated into some XML. At any time you can overwrite the generated XML of APEX by setting the Use Custom XML setting to Yes. If you select to use custom XML, attributes under Display Settings, Axes Settings, Legend Settings, Font Settings, and Chart Title are not used and are made hidden. If you set Use Custom XML back to No, all the settings will appear again as they were last saved by the APEX screen, and your customizations in the XML itself will be lost.

For a complete reference of the XML that can be used, see the AnyChart web site at www.anychart.com/products/anychart/docs/xmlReference/index.html.

In the Refresh section, you can set Asynchronous Update to Yes to give the chart new data at an interval you specify (Figure 3-19). This is useful if you always have the same page open with a dashboard and want to see the latest data updated every few seconds without having to reload the page. You can enter the interval in seconds between chart updates, but updates intervals less than two seconds are discouraged because that would mean APEX has to constantly retrieve the data. The maximum value for this setting is 99999, which is just over a day.

You can now apply what you know and change some settings of the chart to include a pattern on the bars (hatch), get a smoother look (Aqua style), have a gradient background, have different colors and rotate the labels, play with the axes, and add a legend.

• Color Scheme: Look 7
• Hatch Pattern: Yes
• Style: Aqua Light
• Background Type: Gradient with Background colors: #CCCCFF and #CCFFCC
• Include on Chart: Hints, Values, Labels, Multiple Y-Axes, Invert Y-Axis Scale
• Show Grid = Y-Axis
• Gradient Angle: 0 Degrees
• X-Axis Label Rotation: 45 Degrees
• Values Rotation: 90 Degrees
• Y Axis: Min: 200
• Y Axis: Prefix: $
• Y-Axis Major Interval: 400
• Y-Axis Minor Interval: 200
• Decimal Places: 2
• Show Legend: Left
• Legend Title: Legend
• Legend Element Layout: Vertical
• Show Legend Background: Selected
• Font Settings: Different colors starting with #000011 and ending with #000088 such as X Axis Labels Color = #000011, Y-Axis Labels Font Color = #000022, and so on

Figure 3-20 shows the result.

Adding Multiple Series and Combined Charts

In Figure 3-13 in the previous section, you can see Series in the Chart Attributes. If you create a new series or edit an existing series, a new page will open called Chart Series (Figure 3-21).

The Series Attributes section lets you define a name, type, and sequence for the series of that chart type. In Series Name, you enter a name for this series. For scatter marker and range charts, the Series Name setting is used to identify the series in hint and label text. Depending on the chart type chosen, you may able to change the series types. There are three options: Bar, Line, and Marker. For example, if you want to combine a line chart with a bar chart, you could have a main chart type setting of 2D Line and then define the Series Type setting for one of the chart series to be Bar. I will cover combined charts in the next section. Lastly, the sequence determines the order of evaluation.

The next section in Chart Series holds the series query. The Query Source Type setting can be of type SQL Query or Function Returning SQL Query. Most of the time Query Source Type will be SQL Query, but if you need to run a different query depending some values on the page, the Function Returning SQL Query type will probably be the one to pick in that case.

In SQL, you enter the SQL statement or function that will return the data to display this chart’s series. Depending on the chart type you choose, a different query may be necessary. If you are unsure about which columns go first and which ones the chart type expects in the query, it’s good to use the Build Query button because that will go through a wizard to define the query (Figure 3-22). The Build Query button is not available in the Page Designer view. But with the Page Designer, it’s a lot quicker to add multiple series. Right-click Series in the left tree and click the Create Series link.

Once the query is defined, you can still adapt it to your needs.

For more experienced developers, it might be useful to create the SQL statement first in SQL Developer or a similar application. That allows you to see whether the query is returning the data you want and, in the case of a really complex statement, create a view or function first and base the SQL statement on that view or function.

The SQL query syntax for the various chart types is as follows:

• Most of the charts have a SQL query with the following syntax:

  select link, label, value
  from   ...

• In one query you can also define multiple values that will all be another series (also called multiple series syntax).

  select link, label, series_1_value [, series_2_value [, ...]]
  from   ...

• Dial charts have this syntax:

  select value, maximum_value [ ,low_value [ ,high_value] ]
  from   ...

• Range charts have this syntax:

  select link, label, low_value, high_value
  from   ...

• Scatter charts have this syntax:

  select link, label, x_value, y_value
  from   ...

• Candlestick charts have this syntax:

  select link, label, open, low, high, close
  from   ...

Maximum Rows contains the maximum number of rows you want to use to display the chart. For pie charts, you are restricted to displaying fewer than 50 rows; for the other charts, the number is unlimited, but the more rows you have, the longer it takes to render the chart.

Furthermore, in the series page, you have the possibility to specify a message when there’s no data found; you can make series conditional and specify which authorization scheme they belong to. Finally, there’s the possibility to add a build option to it.

Figure 3-23 shows an example of a multiple series chart that combines lines, markers, and bars. The chart shows the salary and commission for the employee as a column (bar), the minimum salary across employees as a marker, and a line with the average salary. It also has a legend to show what color corresponds with what value.

Looking a bit closer at the series that are defined (Figure 3-24), the first series calculates the minimum salary, has a series type of Marker, and contains a SQL query. The second series calculates the average salary and has a series type of Line, and its Query Source Type setting is Function Returning SQL Query. Editing the series shows the content a bit better (Figure 3-25). The last series contains the salary and commission and uses the multiple series syntax, so one query actually contains two series: salary and commission. This series is of type Bar.

Different Chart Types

APEX ships with many chart types. To use them in your application, you just select the chart type you like from the Create Chart Wizard. Oracle licenses these chart types from AnyChart, so you can use them freely anywhere in APEX. The following main and subchart types are natively available in APEX 5.0 by using the wizard:

• Column
  • 3D Column
  • 3D Stacked Column
  • 3D Stacked Column (Percent)
  • 2D Column
  • 2D Range Column
  • 2D Stacked Column
  • 2D Stacked Column (Percent)
• Horizontal Bar
  • 3D Bar Chart
  • 3D Stacked Bar Chart
  • 3D Stacked Bar Chart (Percent)
  • 2D Bar Chart
  • 2D Range Bar Chart
  • 2D Stacked Bar Chart
  • 2D Stacked Bar Chart (Percent)
• Pie and Doughnut
  • 3D Pie
  • 2D Pie
  • 2D Doughnut
• Scatter Marker
• Line
• Candlestick
• Gauges
  • Dial
  • Dial (Percent)
• Gantt (only Flash)
  • Project Gantt
  • Resource Gantt

If there is a chart type you want to use but you don’t see it in the previous list (for example, a bubble chart), then you can get a separate license from AnyChart. Figure 3-26 gives an overview of the chart types that come with APEX (most of them are available in 2D and 3D) on the left side and shows the extra chart types that are available with the full version of AnyChart (version 6.2) on the right side. To ease the integration of the other chart types, APEX R&D created an AnyChart plugin that you find at https://www.apexrnd.be.

AnyChart also built an entire new engine, AnyChart 7.x, which is based entirely on JavaScript and HTML5, but at the time of this writing, it’s not backward compatible, so APEX would need to recode a lot to be able to support the new version. It would also mean if people used Flash charts before, they would not work anymore, so for now in APEX 5.0 the latest version of AnyChart 6 is included.

Another alternative for other types of charts is to use other plugins. Later in this chapter you’ll find some examples such as D3 chart plugins.

Behind the Scenes

To understand what’s happening behind the scenes, you need to know how AnyChart works. The easiest way to understand AnyChart is to go to its web site and download a trial version. This is not related to APEX, but it shows the different components it needs to get a chart on an HTML page. It involves including the AnyChart JavaScript files, creating a div on your HTML page, and creating a small piece of JavaScript to create a new AnyChart chart and tell the engine to render the chart in the div.

This is exactly what the APEX engine is doing for you behind the scenes. The first thing it does is include the AnyChart libraries. AnyChart 6.2 comes with two libraries, AnyChart.js and AnyChartHTML5.js, which in APEX are located in /i/flashchart/anychart_6/js/. The next thing APEX is doing for you is creating a container for the chart, the div element. In APEX you’ll find a div element called R<number>_chart. Finally, it includes some JavaScript to define the chart, the settings, and the data. APEX created a widget that you can find in /i/libraries/apex/minified/widget.chart.min.js (Figure 3-27).

When you call a page with a chart, the page is rendered, and the JavaScript to create the chart is executed. APEX gets data from the database (where the definition is of the chart), formats it in a way AnyChart understands it, and passes it to the AnyChart object, which in turn renders the chart.

More graphically presented, the flow is as shown in Figure 3-28.

The following are the steps shown in Figure 3-28:

1. The user requests a page with a chart, so the browser sends a request to APEX to retrieve the page.
2. APEX handles the request and sends back the HTML for that page, which includes a container div (region) and some JavaScript to create the chart.
3. The browser shows the HTML page and creates the chart object that will make a request back to APEX to get the data and settings for the chart.
4. APEX retrieves the request and sends back the XML for the data of the chart.

   Looking at a real case when a user requests a page that holds a chart, the following happens:

5. The user clicks a tab that does a call with this URL: http://webserver/pls/apex/f?p=71450:3:103001531406712:::::.
6. APEX searches for Application 71450, Page 3 (and the session is 103001531406712). If you want more information about an Oracle APEX URL, click the Help in Oracle APEX and go to Home Application Builder Concepts Understanding URL Syntax. APEX will translate the source into HTML and some JavaScript to execute and then send it back to the browser (Figure 3-29).

   

   Figure 3-29. The HTML and JavaScript for the chart

7. The page is shown in the browser, and the AnyChart object will request the data it needs to display. While it’s getting the data, you will see a loading animation (Figure 3-30).

   

   Figure 3-30. Rendered HTML and call for data of the chart object

8. To verify the request, it’s best to use the developer tools of your browser or a developer extension for the browser such as Firebug for Firefox. When clicking the Network tab, you’ll see the requests that are done by the browser.

   The JavaScript will do an AJAX request (Figure 3-31). It will call the URL wwv_flow.show (as a POST request) and pass these parameters:

   • p_request: APXWGT (the APEX widget)
   • p_flow_id: 71450 (the application ID)
   • p_flow_step_id: 3 (the page ID)
   • p_instance: 8477258330126 (the session ID)
   • x01: 1549802992185414621 (the region ID)
   • p_widget_name: chart5 (so the APEX widget knows it’s about an HTML5 chart)

   

   Figure 3-31. AJAX request seen in the Developer Tools of the browser

The AJAX call will respond with the XML that defines the chart and that the AnyChart object understands. The XML it returns (Figure 3-32) is based on the settings you defined in the Chart Attributes area and the series you created (Figure 3-33 and Figure 3-34). Figure 3-35 shows the final chart.

All the tokens (#...#) you see in the Chart XML area in APEX were replaced by values defined in the Chart Attributes area, and the #DATA# token was replaced by the output of the select statement defined in the series.

Debug and Performance

Debugging charts is done differently than debugging other components in APEX. After reading how these charts work behind the scenes, you know that there are different components and requests going on for a single chart. There is the HTML for the region of the chart, the JavaScript to include the AnyChart object, the call for the data (XML) for the chart, and the generation of the chart.

When you run your page in Debug Mode, you’ll get two debug outputs (Figure 3-36). The first “show” (Path Info) is for the page, and the second “show APXWGT” (Path Info) is the AJAX request to get the settings and data for the chart.

The first “show” gives you an idea of how long it took to generate the page with the chart region. Drilling into the details of the debug output shows how long it took to render the chart region (Figure 3-37). Note that only the container of the chart is counted here. The chart object still needs to be called by another process, and the XML still has to be generated. Most performance issues are based on the actual generation of the XML (based on the query that is in the series). How long it took to generate the XML for all the series is shown in the “show APXWGT” debug details (Figure 3-38).

Running the page in Debug Mode has another component that is valuable. In the page itself, underneath the chart, a Show XML link will appear, so you see exactly what the chart object receives (Figure 3-39).

The Show XML link will show the XML output that was generated by APEX for the chart (Figure 3-40). Having this information is really important because the chart you see is defined by this XML. AnyChart has a complete XML reference in its documentation, which explains every node. You can find the URL to the AnyChart XML reference in the “Resources” section later in the chapter (Figure 3-41).

To see exactly what is happening in the browser, you need to use an external tool, such as Firebug. Other browsers also have developer tools, which can do the same.

The Net panel in Firefox is most important for seeing what’s going on behind the scenes (Figure 3-42). The main purpose of the Net panel is to monitor HTTP traffic initiated by a web page and simply present all collected and computed information to the user. Its content is composed of a list of entries where each entry represents one request/response round-trip made by the page. You can see the requests, the status, the size of what is returned, and the time it took.

The first request is the request for the APEX page. The next requests are to get some CSS and JavaScript files. The last request is the call to wwv_flow.show to retrieve the data (xml). The XHR tab will show you the AJAX request for the chart data too.

Hovering over the request shows you the complete URL, and clicking it slides open other options, such as Params, Headers, Post, Response, XML, Cache, and Cookies. Depending the call, you get different options to click. Just as in Figure 3-31 where you saw the developer tools, Firebug has this information too. Clicking the Post tab shows you more information about the call (Figure 3-43).

Clicking the Response or XML tab will show you the XML that Oracle APEX produced based on the settings in the Chart Attributes area for that chart.

If you have an issue with your chart, the first thing you should check is the response and the XML (Figure 3-44). In 90 percent of cases, there is something awkward going on in the output, which explains why something is not behaving in the chart as you expect. For example, a tag may not be closed correctly, some special characters are making the XML invalid, or the wrong syntax is being used in the XML. Referring to the AnyChart documentation is the next step to solve your problem. You will most likely not encounter these issues if you stay with the declarative charts in APEX; however, if you start to customize them, come back here to reread the possible issues.

If you find that some parts take a long time to load, you can investigate by hovering the cursor over the bar to get more information (Figure 3-45).

If you encounter performance issues and your charts become slow, the first thing you need to look at is the debug output. Finally, you can also check the Net panel in Firebug and check the Timeline. That will tell you exactly where you are losing most of the time. Usually you will find that the last step (the generation of the XML) takes most of the time.

The more series you have, the longer it may take to see the data in the chart. If you can combine multiple series into one, you will gain performance. If you need multiple series, it’s best to try them first in SQL Developer or SQL Workshop to see how fast the results arrive. If the query is slow in that environment, it will be slow in the chart too. You can then compare those results with the results shown in the debug output of the chart (show APXWGT details). (Tuning the SQL statements falls outside the boundaries of this chapter.)

Upgrading Oracle APEX 3.x/4.x Flash and SVG Charts

If you initially developed your application in an earlier version of APEX and you used SVG or Flash charts, you might want to upgrade them to the new charts in APEX 5.0. AnyChart 6 charts are integrated with APEX 5.0, which are nicer, are faster, and have more options than the earlier version of AnyChart charts that came with APEX 3.x and 4.x. The fastest way to upgrade all charts is to go to Utilities and then click Upgrade Application. This will show you which charts you can upgrade. Note that SVG charts can be upgraded with some restrictions.

Follow these steps to upgrade all existing Flash and SVG charts to the latest AnyChart charts engine:

1. Go to the Application home page.
2. Click Utilities and then click Upgrade Application (Figure 3-46). The Upgrade Application Summary report appears (Figure 3-47).

   

   Figure 3-46. Utilities section in APEX 5.0

   

   Figure 3-47. Upgrade Application Summary page—Candidates of charts to upgrade to HTML5 charts

3. Look for Upgrade SVG Charts to Flash Chart 5 and Upgrade Flash Charts to Flash Chart 5, or Upgrade Flash Charts to HTML5 Charts, and click the number of candidate objects.
4. Select the objects to upgrade and click Upgrade (Figure 3-48).

Note that in some cases the charts might look a bit different or the label of the axis are not showing. When you open the chart attributes again and hit Apply Changes, this will fix itself. This means that it will regenerate the XML it needs. If you developed charts in APEX 4.2, they will automatically use the latest version of the AnyChart engine.

Screen Reader Mode and Charts

APEX 5 allows you to run APEX itself and your own application in the Screen Reader Mode. The Screen Reader Mode improves the usability of Application Express applications with a screen reader. A screen reader is a software application that attempts to identify and interpret what is being displayed on the screen. This interpretation is then re-presented to the user with text-to-speech, sound icons, or a Braille output device.

You can enable/disable the Screen Reader Mode in your own application in three ways.

• Add the #SCREEN_READER_TOGGLE# substitution string to the footer of your page template. Doing so results in a link on your pages that viewers can use to toggle the Screen Reader Mode on and off.
• Use the screen-reader APIs documented in the Oracle Application Express API Reference. Using the APIs is more work than adding the substitution string to your page footer, but they do provide you with more control.
• Create links that enable and disable the Screen Reader Mode by executing f?p session requests. Here’s an example:

  <a href="f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:SET_SESSION_SCREEN_ READER_ON">Reader
   Mode On</a>
  
  <a href="f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:SET_SESSION_SCREEN_ READER_OFF">Reader
   Mode Off</a>

APEX charts are not currently accessible to screen readers; therefore, when running in the Screen Reader Mode, the user will get a report representation of the information conveyed in the chart. A separate report will be generated for each series of a multiple-series chart if the series were defined as separate series. If the multiple series were defined in a single query, only one report will be generated (Figure 3-49).

When running in Screen Reader Mode (Figure 3-50), these data tables contain descriptive text, in the following format:

• Summary Text: In Application Builder, a combination of the chart title and chart series title is used.
• Column Headers: In Application Builder, the column name/alias in the chart series query is used to identify the columns in the report.

Extending Charts

In the previous sections, you saw how to create charts through the APEX wizard and how these charts work behind the scenes. If you understand how the charts work and how they are implemented in APEX, you should be able to do anything you like. This section will give some examples of extending the existing charting possibilities of APEX by stepping outside the APEX wizards.

I will also switch the interface to use the Page Designer because it makes it easier to extend the charting possibilities. I also typically use the Show All attributes, instead of the most common view.

Customizing Charts by Using Custom XML

A manager wants to see how sales are going based on her forecast and targets she previously set. Showing the budget can easily be done with a column chart, but showing the forecast and target lines is something that is not natively available (through the wizards) in APEX (unless you create another series for the line, but that is not quite the same). The following are the steps to add a 2D column chart and two trendlines, one for the forecast and one for the target:

1. If you already have some experience with AnyChart XML and what each node is used for, go to the AnyChart reference documentation (you will find the URL in the “Resources” section).
2. If you are less experienced, go to the AnyChart Gallery and click the 2D Column charts examples. Luckily there is something similar to the previous example in the gallery called Trendlines and Axis Ranges Demo. Click that example, and a pop-up will appear with an example chart in one tab (Figure 3-51) and the XML code that was used in the other tab (Figure 3-52).

   

   Figure 3-51. AnyChart Gallery example of trendlines

   

   Figure 3-52. XML code behind the example in the AnyChart Gallery

3. If APEX does not support a feature through the wizard but you know AnyChart supports it (by finding an example or looking at the XML), you can adapt the XML for the chart in APEX to manually implement the feature. To customize the XML, follow these steps (in Page Designer):
   1. Create a normal 2D column chart.
   2. Open the chart attributes.
   3. Navigate to the bottom of the page (Custom XML region) and set the drop-down for Use Custom XML to Yes. That will make the Custom XML text area editable.
4. Note that the settings of the chart disappear, and you now have to manually adapt the XML to make changes to the chart (Figure 3-53).

   

   Figure 3-53. Use Custom XML in Chart Attributes in the APEX Page Designer

   1. Looking at the existing example of AnyChart or at the documentation, to add a trendline, you would add the Axis Markers node. As you want a line to go horizontally, add the marker to the y-axis. Locate the end of the y-axis node (<y_axis>), and just before that, add the following:

      <axis_markers>
       <lines>
          <line value="&P17_FORECAST." thickness="2" color="Rgb(200,30,30)" caps="Square">
            <label enabled="True" multi_line_align="Center">
              <font color="Rgb(200,30,30)" />
              <format>Forecast: ${%Value}{numDecimals:0} </format>
            </label>
          </line>
          <line value="&P17_TARGET." thickness="2" color="Green" caps="Square">
            <label enabled="True" multi_line_align="Center">
              <font color="Green" />
              <format>Target: ${%Value}{numDecimals:0}</format>
            </label>
          </line>
        </lines>
      </axis_markers>

   2. In the code, you use &P17_TARGET. and &P17_FORECAST., which are substitution strings for the respective page items. You can dynamically set these items through, for example, a computation, so you can calculate the line from a SELECT statement or PL/SQL procedure. If the values are always fixed, you can just replace the substitution strings with a numeric value.
5. Saving the chart and running the APEX page gives the result shown in Figure 3-54.

   

   Figure 3-54. Result of adding an axis marker manually to the chart in APEX

This example shows how you can customize the XML of a chart to add features that APEX doesn’t allow by using the wizard or chart attributes. It’s useful to read the AnyChart documentation and review the XML reference to know what is possible. The principle is always the same: once you know what XML to include, you change the XML by setting Use Custom XML to Yes in the Chart Attributes area and, presto, you have extended your chart!

Customizing Charts by Using Custom XML, Dynamic Actions, and JavaScript

Customizing the XML is one way to get more out of charts, but sometimes even that is not enough and you need to take an extra step. To illustrate this, you will look at the use case where the manager of the previous example now wants to see which employees brought in a lot of sales, who performed well, and who did not. She could just look at the chart and do the math in her head, but giving colors to the columns (good = green, normal = yellow, bad = red) would make the job easier (Figure 3-55). This means you will define thresholds: sales numbers below a certain number (red), sales numbers between certain numbers (yellow), and sales numbers over a certain number (green).

These are the steps to create a chart with thresholds:

1. Create a normal 2D column chart based on the following SQL statement:

   SELECT NULL LINK,
          ENAME LABEL,
          SAL VALUE
   FROM   EMP
   ORDER  BY ENAME

2. Click the Attributes entry under the chart’s region in the rendering side of the Page Designer.
3. Navigate to the bottom of the page (Custom XML region) and set the drop-down for Use Custom XML to Yes. That will make the Custom XML text area editable (Figure 3-53).
4. The AnyChart XML reference documentation (Figure 3-56) says you can use a threshold node (<threshold>). The following code needs to be added just after the </data_plot_settings> node (and before #DATA#):

   <thresholds>
     <threshold name="sales_threshold">
       <condition name="Bad" type="lessThan" value_1="{%Value}" value_2="1500" color="Red"/>
       <condition name="Normal" type="between" value_1="{%Value}" value_2="1500" value_3="2500"
   color="Yellow"/>
       <condition name="Good" type="greaterThan" value_1="{%Value}" value_2="2500" 
   color="Green"/>
     </threshold>
   </thresholds>

   

   Figure 3-56. AnyChart XML reference documentation—<threshold> node

5. If you run the page now with the custom XML, you don’t see any difference, which is because the data node also needs to take the threshold into account. Now this isn’t as straightforward as you might think. If you look at the XML, you will find a #DATA# token that gets replaced on the fly by Oracle APEX based on the series. The SQL statement of the series gets translated into the correct XML. APEX doesn’t support the threshold tag out of the box, so you need to generate the correct XML so the thresholds are included manually.
6. Create a Hidden Item on the page (right-click the chart region by setting Create Page Item to Hidden) and call it P18_CHART_XML (because you are on page 18). You will use this item to store the data part of the chart.
7. In the Custom XML, replace #DATA# with &P18_CHART_XML. (note the . at the end), where P18_CHART_XML is the hidden page item. That item you will fill with the correct XML.
8. To fill P18_CHART_XML with the XML, add a computation to the page that generates the XML. Use the XMLDB feature of the database, which lets you create a SELECT statement that generates XML. The computation looks like this:
   • Type: SQL Query (return single value)

     SELECT xmlelement("data", xmlattributes(’sales_threshold’ AS "threshold"), 
      xmlelement("series", xmlattributes(’Series 1’ AS "name"), xmlagg( 
      xmlelement("point",xmlattributes(ename AS "name", sal AS "y") ) ))) .getClobVal() as data
     FROM emp

9. If you want to add the legend of the thresholds, you have to make another small change to the XML in Use Custom XML. You have to tell the legend the source is now thresholds instead of the items. Search for legend (visible only when you defined Show Legend before you set Use Custom XML) and change it to the following code. If you don’t find the legend node, add it after the </axes> node.

   <legend enabled="true" position="Bottom" align="Near" elements_layout="Horizontal"
    ignore_auto_item="true" >
     <title enabled="true">
       <text>Legend</text>
       <font family="Arial" size="10" color="0x000000" />
     </title>
     <font family="Arial" size="10" color="0x000000" />
     <items><item source="thresholds"/></items>
   </legend>

Saving the chart and running the APEX page should give you the result shown in Figure 3-55.

Note that if you have Asynchronous Update set to Yes, you will also need to refresh the hidden item with the correct data.

Creating Charts Manually

When you want full control over everything, you can choose to create a chart completely manually. AnyChart allows you to add events to the chart to control every step, from rendering to moving the mouse and clicking parts of the chart. In the following example, you will create a multiseries chart with multiple axes and different tooltips per series. You will also make the width and height of the chart depend on what the user defines on the page. I will also discuss having null values in the resultset (see item 6 in this list).

Follow these steps to create a chart manually:

Create a page with an HTML region (in this example, page 7) and name the region Manual Chart.

In the region source of the HTML region, add an empty div, which will be filled with the chart by using JavaScript:

<div id="chartDiv"></div>

You will also create three page items.

• One text item (text field) that defines the width of the chart: P7_CHART_WIDTH with a static value of 600 as the source
• One text item that defines the height of the chart: P7_CHART_HEIGHT with a static value of 400 as the source
• One hidden item where you store the XML data for the chart: P7_CHART_XML

Because you will load the entire chart with JavaScript, you need to add the JavaScript package that comes with AnyChart. Edit the page, and under JavaScript – File URLs, put the following:

#IMAGE_PREFIX#flashchart/anychart_6/js/AnyChart.js
#IMAGE_PREFIX#flashchart/anychart_6/js/AnyChartHTML5.js

Because you want to change the chart dynamically, you need to make sure that the chart variable in JavaScript is accessible in the entire page. That is why you add in the Page Definition in Function and Global Variable Declaration area the following variable declaration:

var chart;

You now need to initialize the chart by calling a specific AnyChart function, generate the data, and give the data to the chart.

Create a new dynamic action to generate the XML. Set the following options:

• Name: Load Chart
• Event: Page Load

Make the action a true action, and specify the following:

• Action: Set Value
• Fire On Page Load: Yes
• Set Type: PL/SQL Function Body
• PL/SQL Function Body:

  declare
    l_chart varchar2(32767);
    l_xml   clob;
    l_data  varchar2(32767);
  begin
    l_chart := ’<anychart>
    <settings>
      <animation enabled="True" />
    </settings>
    <charts>
      <chart plot_type="CategorizedVertical">
        <chart_settings>
          <title enabled="true">
            <text>Multi-Series: Multiple Y-Axes</text>
          </title>
          <axes>
            <x_axis tickmarks_placement="Center">
              <title enabled="true">
                <text>Arguments</text>
              </title>
            </x_axis>
            <y_axis>
              <title enabled="true">
                <text>Primary Y-Axis</text>
                <font color="#135D8C" />
              </title>
              <labels align="Inside">
                <font color="#135D8C" />
              </labels>
            </y_axis>
            <extra>
              <y_axis name="extra_y_axis_1" position="Right">
                <minor_grid enabled="false" />
                <major_grid enabled="false" />
                <title enabled="true">
                  <text>Secondary Y-Axis</text>
                  <font color="#A4300B" />
                </title>
                <labels align="Inside">
                  <font color="#A4300B" />
                </labels>
              </y_axis>
            </extra>
          </axes>
        </chart_settings>
        <data_plot_settings default_series_type="Line">
          <line_series>
            <label_settings enabled="true">
              <background enabled="false" />
              <font color="Rgb(45,45,45)" bold="true" size="9">
                <effects enabled="true">
                  <glow enabled="true" color="White" opacity="1" blur_x="1.5" blur_y="1.5"
   strength="3" />
                </effects>
              </font>
              <format>{%YValue}{numDecimals:0}</format>
            </label_settings>
            <tooltip_settings enabled="true">
              <format>
  Value: {%YValue}{numDecimals:2}
  Argument: {%Name}
  </format>
              <background>
                <border type="Solid" color="DarkColor(%Color)" />
              </background>
              <font color="DarkColor(%Color)" />
            </tooltip_settings>
            <marker_settings enabled="true" />
            <line_style>
              <line thickness="3" />
            </line_style>
          </line_series>
        </data_plot_settings>
        <data>
        #DATA#
        </data>
      </chart>
    </charts>
  </anychart>’;
    SELECT xmlelement("series", xmlattributes(’Series 1’ AS "name"), xmlagg( xmlelement 
  ("point", xmlattributes(ename AS "name", sal AS "y") ) )).getClobVal()
    INTO l_xml
    FROM emp;
  
    l_data := l_data || wwv_flow_utilities.clob_to_varchar2(l_xml);
  
    SELECT xmlelement("series", xmlattributes(’Series 2’ AS "name", ’extra_y_axis_1’ AS 
   "y_axis"), xmlagg( xmlelement("point", xmlattributes(ename AS "name", nvl(comm,0) AS "y") 
   ) )).getClobVal()
    INTO l_xml
    FROM emp;
  
    l_data := l_data || wwv_flow_utilities.clob_to_varchar2(l_xml);
  
    l_chart := replace(l_chart, ’#DATA#’, l_data);
  
    return l_chart;
  end;

• Escape Special Characters: No
• Affected Elements – Selection Type: Item(s)
• Item(s): P7_CHART_XML

You use the same XML database features of the database to generate the data part. You also have a variable to store the XML definition of the chart. Note that the previous example works only with datasets that are less than 32KB because there is currently a limitation in APEX that you can assign a maximum of 32KB to a page item. If you need to work with big datasets, you need to slightly change the code. Later in this chapter, you will generate a dashboard and use the other technique, which supports XML bigger than 32KB. Also note that you used NVL(comm,0) for the value in the second series. If you don’t use NVL, the line will be incomplete because the y value in the XML will contain an empty string and your result might not be correct. So, either you use NVL, which gives empty strings the value of 0 so every record will show up in the chart as a point, or you define a WHERE clause where you specify comm is not null; that means not every record will be shown, but your line will be complete.

Create a second true action to load the chart, with the following settings:

• Action: Execute JavaScript Code
• Fire When Event Result Is: True
• Code:

  AnyChart.renderingType = anychart.RenderingType.SVG_ONLY;
  chart = new AnyChart;
  chart.width = $v(’P7_CHART_WIDTH’);
  chart.height = $v(’P7_CHART_HEIGHT’);
  chart.setData( $v(’P7_CHART_XML’) );
  chart.write(’chartDiv’);

The chart JavaScript object you defined on the page level. The previous code initiates a new AnyChart HTML5 chart. The object can have different properties and events; in this case, I used the width and height to define that at runtime based on the value in the page item. The setdata event gives the XML it finds in P7_CHART_XML to the chart, and finally the write event will write the chart to the div.

Running the page gives the result shown in Figure 3-57.

You also want to change the tooltip of Series 2 to have a custom message. To achieve that, set the dynamic action Load Chart to Set Value in the true action. Then replace the existing SQL statement of Series 2 with the following code:

  SELECT
    xmlelement("series",
      xmlattributes(’Series 2’ AS "name", ’extra_y_axis_1’ AS "y_axis"),
      xmlagg( xmlelement("point",
        xmlattributes(ename AS "name", nvl(comm,0) AS "y"), 
        xmlelement("tooltip", xmlattributes(’true’ as "enabled"), xmlelement("format", ’Job: ’
 || job)) ) )).getClobVal()
  INTO l_xml
  FROM emp;

The new SQL statement that you entered previously causes the APEX engine to add an XML tooltip to the generated XML (see that in the point node there’s a tooltip node). The new generated XML code is as follows:

<series name="Series 2" y_axis="extra_y_axis_1">
    <point name="SMITH" y="0">
        <tooltip enabled="true">
            <format>Job: CLERK</format>
        </tooltip>
    </point>
    <point name="ALLEN" y="300">
        <tooltip enabled="true">
            <format>Job: SALESMAN</format>
        </tooltip>
    </point>
    ...
    <point name="FORD" y="0">
        <tooltip enabled="true">
            <format>Job: ANALYST</format>
        </tooltip>
    </point>
    <point name="MILLER" y="0">
        <tooltip enabled="true">
            <format>Job: CLERK</format>
        </tooltip>
    </point>
</series>

The result looks like Figure 3-58.

To let the user define the width and height of the chart, you need to add another dynamic action that fires when the user changes the width and height text items. Here are the steps to do this:

Add a new advanced dynamic action with the name Change Chart Size and the following settings:

• Event: Change
• Selection Type: Item(s)
• Item(s): P7_CHART_WIDTH,P7_CHART_HEIGHT

Make the action a true action, and specify the following:

• Affected Elements: Selection Type = Region
• Affected Elements: Region = Manual Chart
• Action: Execute JavaScript Code
• Fire When Event Result is: True
• Fire On Page Load: No
• Code:

  chart.setSize($v(’P7_CHART_WIDTH’),$v(’P7_CHART_HEIGHT’));
  
  Now run the page and change the width and heights to see the results.

When you manually create a chart for the first time, it might be difficult to understand, but it comes down to going to the AnyChart Chart Gallery, finding an example you like, or looking into the documentation to know what XML and JavaScript you need to call. Next, you need to translate that logic into APEX components. Only by looking at the previous examples, trying things yourself, and gaining experience will this task become clear (if it is not already). After a while, the steps you have to take to create a chart completely manually will become trivial.

Drill-Down Charts, Dashboards, and Interactivity

In this section, you will look more at combining different charts on the same page and letting them work nicely together. You will create a page that will provide an instant snapshot of your business information, by combining different reports and charts on a dashboard. With drill-down capabilities, the charts on your dashboard can show different results based on the user interaction with the page. There are many different techniques to create dashboard pages in APEX, from simple examples using the built-in functionalities in APEX to complex dashboards using actions and events that come with the full license of AnyChart charts.

Simple Dashboard with Submit

In this first example, a manager wants to have an overview of the salaries he’s giving to the respective departments and employees. To fulfil the manager’s wish, you will create a page with one region and three subregions that hold the different charts so it looks like the charts are in one region (Figure 3-59).

• A pie chart with the salary by department
• A 2D column chart that shows the average, minimum, and maximum salaries by department
• Another 2D column with the salary by employee

Next, you want to allow the manager to select a department in the pie chart and automatically update the other charts to show information only for the selected department (drill-down). Figure 3-60 represents the expected result when the manager clicks the Sales (green) slice of the pie chart.

Behind the scenes, you used the wizard to create three different charts.

• The pie chart that shows the salary by department uses the following query:

  SELECT ’f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.::::P8_DEPTNO:’||d.deptno LINK,
         d.dname LABEL,
         sum(e.SAL) sal
  FROM   emp e, dept d
  where e.deptno = d.deptno
  group by ’f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.::::P8_DEPTNO:’||d.deptno, d.dname
  ORDER  BY d.dname

• The 2D column that shows the average, minimum, and maximum salaries by department uses this query:

  SELECT NULL LINK,
         d.dname LABEL,
         avg(e.SAL) as "Average Salary",
         min(e.SAL) as "Minimum Salary",
         max(e.SAL) as "Maximum Salary"
  FROM
         emp e, dept d
  where e.deptno = d.deptno
     and d.deptno = nvl(:P8_DEPTNO, d.deptno)
  group by d.dname
  ORDER  BY d.dname

• The 2D column that shows the salary by employee uses this query:

  SELECT NULL LINK,
         ENAME LABEL,
         SAL VALUE
  FROM   EMP
  where deptno = nvl(:P8_DEPTNO, deptno)
  ORDER  BY ENAME

If you want to create the previous example yourself while you are reading, take a look at Figure 3-61, which shows all the regions, buttons, and items behind the scenes and how they are laid out. If you downloaded the application that belongs to this chapter, you should take a look at page 8.

Note that the pie chart has a link defined. That link will submit the page and set the item P8_DEPTNO, a hidden item you created in that region, with the value of the slice the user clicks.

You also use a button on the page called All Departments, which redirects to the same page and clears the cache. When creating the button for the action, select Redirect to Page in this Application (Page is 8 and Clear Cache is 8).

This example is the simplest dashboard you can create. It uses charts created by the wizard and provides interactivity and drill-down capabilities by using the link in the series SQL statement. To achieve the dashboard look and feel, I created a parent region with three subregions for the charts. That way the charts look like they are combined. The previous example has one big drawback: whenever the manager clicks a link, the entire page gets submitted, which isn’t a nice effect and doesn’t flow that well. You will fix the flow by adding a bit of JavaScript in the next section that will refresh the regions instead of submitting the entire page.

Simple Dashboard with JavaScript

This example will expand on the previous example, extending it to include a report on the employee data. Rather than doing a submit of the entire page, you will just refresh the necessary regions so the manager gets a nicer user experience.

You will copy the page of the previous example to page 10 and keep everything you did. You will add another report that will show the data of the employees. If the manager selects a specific department, the report only has to show the data for that department. So, you add a classic SQL report to the page with the following SELECT statement:

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM
 from emp
where deptno = nvl(:P10_DEPTNO, deptno)

To remove the submit event of the page when the manager clicks the pie chart, you need to change the link in the pie chart. You will set the value of P10_DEPTNO dynamically with JavaScript (by using $s(), a built-in function in APEX), so no submit happens. The select statement of the pie chart becomes the following:

SELECT ’javascript:$s("P10_DEPTNO",’||d.deptno||’)’ LINK,
       d.dname LABEL,
       sum(e.SAL) sal
FROM   emp e, dept d
where e.deptno = d.deptno
group by  ’javascript:$s("P10_DEPTNO",’||d.deptno||’)’ , d.dname
ORDER  BY d.dname

Now, when the manager clicks a slice in the pie chart, the hidden item will get a value (note that this value is not yet in session state because it has not been submitted yet but is available through JavaScript). The issue now is that the other charts won’t drill down yet because they were not yet refreshed, which happened automatically before when the entire page was reloaded because of the submit event of the page. To refresh the other charts, you could set the Asynchronous Update in the Chart Attributes area to five seconds, which will refresh the chart every five seconds. However, this is not a recommended way of handling the chart refresh in this scenario because the refresh will always happen, even if the manager does not click a slice. It also doesn’t work for the report on the page because you cannot define an Asynchronous Update like you could in the chart.

To solve the refresh issue, you use a dynamic action in APEX. The dynamic action will refresh the two charts and the report. The dynamic action will fire whenever the value of the hidden item P10_DEPTNO gets changed. This is how the dynamic action is defined:

• Event: Change
• Selection Type: Item(s)
• Item(s): P10_DEPTNO
• True Action 1
  • Action: Refresh
  • Fire When Event Result is: True
  • Fire On Page Load: No
  • Selection Type: Region
  • Region: Avg, Min and Max Salary per Department
• True Action 2
  • Action: Refresh
  • Fire When Event Result is: True
  • Fire On Page Load: No
  • Selection Type: Region
  • Region: Salary by Employees
• True Action 3
  • Action: Refresh
  • Fire When Event Result is: True
  • Fire On Page Load: No
  • Selection Type: Region
  • Region: Salary by Employees (Report)

In all the series that will get refreshed, make sure to add P10_DEPTNO in the Page Items to the Submit field so the value of P10_DEPTNO is set in session state before it’s refreshed.

Finally, you can change the button for seeing all departments to Redirect to Url. Use the following for the URL: javascript:$s(’P10_DEPTNO’,’’). This basically empties out the hidden item.

Figure 3-62 represents the result of the manager clicking a slice of the pie chart.

As you can see, the Sales slice in the pie chart is moved out from the rest of the chart. This “explode” action is a feature of AnyChart when you click something in the chart, but you didn’t see this in the first example because the entire page was submitted and rerendered.

Complex Dashboard with Actions

This example is completely different from previous examples and takes you beyond APEX. Oracle licensed only part of AnyChart charts, so for example if you want to create a real dashboard (one chart object with multiple charts inside it), you need to include the full AnyChart files. If you try to render the chart with Oracle’s AnyChart version, you get an error (Figure 3-63).

This example (Figure 3-64) hardly uses any built-in features of APEX. The only thing you need is an HTML region where you define the div tag that holds the dashboard and a dynamic action to load the data for the dashboard. Unlike in the previous examples, this dashboard consists of only one AnyChart object, but inside that one object six different charts are defined. Column, bar, spline-area, spline, bubble, and range-area charts are inside one container.

The definition of the HTML region is as follows:

<div id="chartDiv"></div>

And this is how the dynamic action is defined:

• Event: Page Load
• True Actions
• Action 1:
  • Action: Execute JavaScript Code
  • Fire When Event Result is: True
  • Fire On Page Load: No
  • Code:

    AnyChart.renderingType = anychart.RenderingType.SVG_ONLY;
    chart = new AnyChart;
    chart.width = 800;
    chart.height = 500;
    chart.setXMLFile(’#OWNER#.GET_DASHBOARD_XML_PRC?p_param1=Full’);
    chart.write(’chartDiv’);

As you can see, there is almost no complex structure to the page. The dynamic action runs on page load and will create a chart in the div tag. The only magic piece in the JavaScript is the call to setXMLFile, which gets the XML for the chart. In this case, I didn’t use a hidden item to store the XML because that would give a problem for large datasets (more than 32KB). Assigning a value to a page item is limited to 32KB, but because the dashboard you want to create is based on three different charts, passing the information for all three charts will exceed that 32KB limit. The setXMLFile calls a procedure on the server called GET_DASHBOARD_XML_PRC. The #OWNER# will get replaced by the default parsing schema defined in your workspace. The procedure has two parameters to pass extra information to the chart or change behavior based on the user interaction, but there is another parameter called XMLCallDate, which you need to include because AnyChart is attaching extra parameters to the call. XMLCallDate is used by AnyChart to make sure every call is unique; otherwise, the browser might cache the result, and you might get incorrect results. You don’t have to do anything special for that—AnyChart handles everything for you. You just need to make sure you accept these extra parameters in your procedure. The procedure looks like this:

create or replace procedure get_dashboard_xml:prc(
    p_param1 varchar2 default null,
    XMLCallDate IN NUMBER DEFAULT NULL)
is
  -- limit of 32K in single byte characterset, for UTF8 devide by 4 -1
  l_amt    number default 8191;
  l_offset number default 1;
  l_length number default 0;
  l_chart clob;
  l_chart_v varchar2(32767);
  l_chart_data clob;
begin
  dbms_lob.createtemporary( l_chart, FALSE, dbms_lob.session );
  dbms_lob.open( l_chart, dbms_lob.lob_readwrite );

    l_chart_v := ’<?xml version="1.0" encoding="UTF-8"?>
<anychart>
  <dashboard>
    <view type="Dashboard">
      <title padding="0">
        <text>Multiple Charts in Dashboard Mode</text>
      </title>
      <background>
        <inside_margin all="3" top="10" />
      </background>
      <vbox width="100%" height="100%">
        <margin all="0" />
        <hbox width="100%" height="50%">
          <margin all="0" />
          <view type="Chart" source="Chart1" width="33.3%" height="100%" />
          <view type="Chart" source="Chart2" width="33.3%" height="100%" />
          <view type="Chart" source="Chart3" width="33.3%" height="100%" />
        </hbox>
        <hbox width="100%" height="50%">
          <margin all="0" />
          <view type="Chart" source="Chart4" width="33.3%" height="100%" />
          <view type="Chart" source="Chart5" width="33.3%" height="100%" />
          <view type="Chart" source="Chart6" width="33.3%" height="100%" />
        </hbox>
      </vbox>
    </view>
  </dashboard>
... /* TRUNCED FOR READABILITY */ ...’;

  dbms_lob.writeappend( l_chart, length(l_chart_v), l_chart_v);
  dbms_lob.close( l_chart );
  --
  owa_util.mime_header(’text/xml’, FALSE, ’utf-8’);
  owa_util.http_header_close;
  l_length := dbms_lob.getlength(l_chart);
  if l_length > 0 then
     while ( l_offset < l_length )
     loop
          sys.htp.prn(dbms_lob.substr(l_chart, l_amt, l_offset) );
          l_offset := l_offset + l_amt;
     end loop;
  end if;
  --
  if l_chart is not null then
      dbms_lob.freetemporary(l_chart);
  end if;
end;

When you look at this code for the first time, it might look challenging, but it comes down to generating the correct XML that AnyChart requires to render a dashboard. You would need to look into the AnyChart Gallery and Documentation to know what XML is expected.

In this case, you hard-code many things, but you can make this procedure as dynamic as you like by using more parameters or building more queries to retrieve the data. Because there is a limit in sys.htp.prn, you have to write a loop to pass the XML back in chunks. There are many ways to generate the data (XML), you could use AJAX (apex.ajax), a REST web service, an on-demand process, or a procedure. It’s just what you feel most comfortable with what to use to generate the correct XML. I wanted to show how to do this manually, so you see the exact steps, but typically you would wrap the code in an APEX plugin. Further in this chapter, you’ll see how much easier it is to use a plugin to build any chart you like.

There are many more options in AnyChart such as using specific events to refresh one particular view of the dashboard (setViewData in JavaScript) or letting the chart behave completely differently when the user is hovering over and clicking the chart.

The possibilities are endless, and you can’t show every possible feature of AnyChart, but by understanding how AnyChart works behind the scenes and by looking at the different techniques used throughout this chapter, you should be able to build your dream chart.

Building Charts with the AnyChart Plugin

You saw before that Oracle licensed only part of AnyChart’s charts. If you want to extend the existing charting capabilities, you’ll need to include the full version (meaning, purchase a license) of the AnyChart engine (some JavaScript and other files) and create the correct XML for the chart. To ease that pain, APEX R&D developed an AnyChart plugin. By adding the region plugin to your page, it will include the full version of AnyChart and the ability to add the settings and data more easily. AnyChart is also moving more quickly than the releases of APEX, so the versions are out of sync. APEX 5.0 is based on AnyChart 6.2, but the latest release of AnyChart is at the time of writing already on version 7, which is actually a completely new code base and—so far—not backward compatible.

If you go to the AnyChart web site, all the new examples are based on the new engine, so using a plugin makes more sense than to manually include AnyChart. It’s easier to upgrade the plugin with a new release of the AnyChart engine than to manually update your pages. For the latest release of the AnyChart plugin, check https://www.apexrnd.be.

To add a chart, drag the AnyChart plugin on your page in the Page Designer (Figure 3-65). You have different options to enter the source of the chart settings and data. To make it easy, you just copied an example JSON from the AnyChart Playground into the source of the region. You see the result in Figure 3-66.

Most Common Issues

This section explains some issues people frequently seem to have and how they can be solved.

Chart Not Rendering Correctly

When upgrading the Flash charts to HTML5 charts, I noticed that sometimes my chart didn’t look completely the same; for example, the labels were gone, or a title disappeared. It looks like some old syntax of AnyChart is not 100 percent compatible, but luckily there’s an easy way to get around this issue. When you edit your chart attributes and series and save it again, APEX will regenerate the correct XML, which solved the issues in my case. If you use custom XML, you might want to set it first to not use custom XML, save it, and make the changes again when you set it back to use custom XML.

Search for a Specific Feature

Looking at the Oracle APEX Forum, most of the requests are about how to do a certain thing in a chart. In some cases, people don’t know where to add a link or what every option in the APEX wizard means. In other cases, a feature wasn’t supported by an option in the APEX screens, and a change had to be made in the XML by using Custom XML and/or some JavaScript to generate the correct XML data. And in still other cases, people wanted to produce a chart that was not licensed by Oracle, so they had to get a valid license of AnyChart for APEX on the AnyChart web site.

All the previous questions have been answered in this chapter, and you now have a good understanding of how charts work in APEX and how you can enhance them. It’s a matter of reading the AnyChart documentation and finding the correct XML syntax or JavaScript to produce the chart you want.

Charts in the Future

Charts are becoming more and more important in new web sites and applications. They provide a quick overview of a situation at any moment. Business intelligence is already widely adopted, but having charts in your APEX application gives you an advantage. When you look at the packaged applications, you see the trend of using many charts and dashboards.

The charting world is rapidly changing. Column and bar charts have been around for a long time and will exist in the future, but as data grows exponentially, time-based charts will become more and more important. Being able to quickly navigate through a large volume of time-based data in a user-friendly way will be a challenge. Other types of charts are becoming more popular too, such as spider and sunburst charts.

In the last few years, different companies have produced new versions of their charting engines. Flash is disappearing in favor of JavaScript/HTML5 charting engines. HighCharts seems popular on the Web, AnyChart created a complete new code base because it looked old-school, and many other types of charting engines to create specific charts are flying around—some free, some not. Probably the most interesting JavaScript library out there, which is more than just a charting engine, is D3.js. The D3.js site says it like this: “D3.js is a JavaScript library for manipulating documents based on data. D3 helps you bring data to life using HTML, SVG, and CSS. D3’s emphasis on web standards gives you the full capabilities of modern browsers without tying yourself to a proprietary framework, combining powerful visualization components and a data-driven approach to DOM manipulation.”

With D3.js you can do anything you like, and more and more people are sharing what they have done. Oracle started to create plugins for D3.js charts, and I believe enhancing and bringing out more of those plugins will be the direction for the next couple of years.

Other elements of the future of charting solutions will include user interaction such as zooming, drag and drop, different information depending on user interaction, and the like. Allowing the user to do things in an innovative and intuitive way will become increasingly important and will decide whether your application is “wow” or just OK.

Chart Plugins

As the future of charts is in new (D3.js) plugins, I want to mention where you find those new types of charts in APEX 5.0 and highlight the most important features.

Sample Charts in Packaged Application

APEX 5.0 includes a new Sample Charts packaged application (Figure 3-67) that includes some interesting new chart engines.

The following plugins are available in the Sample Charts application:

• Badge List
• D3 Bar Chart
• D3 Bubble Chart
• D3 Line Chart
• D3 Pie Chart
• D3 Sunburst Chart
• D3 Treemap Chart
• Flot Line Chart
• Flot Pie Chart
• Gantt Chart
• HTML 5 Bar Chart
• JustGage Gauge

As you can see, many D3.js plugins are already available, which you can include in your own application too. You just export the plugin and import in your application and there you go. The different plugins have different settings and ways they work behind the scenes, but mostly it comes down to some JavaScript to define the chart, a way to generate the data in the correct format, and then the plugin to make it available natively in APEX.

When you look at the libraries that ship with APEX 5.0 (in the images folder), the following charting libraries are included: raphaeljs, d3js, and justgage. This gives a good indication of where Oracle is going in the future. Although there’s a Flot chart plugin, I believe the D3.js charts will be most interesting. Those D3.js charts are also hooked up to Theme Roller, so based on your theme, you can give your charts the same color scheme. There are many more options in the D3.js plugins; see the example of the D3 Bubble Chart plugin (Figure 3-68).

In the Source setting of the region, you define a SQL query. In the plugin attributes (Figure 3-68), you then define which column is used for the label, which one for the value, and so on. You can also define some custom JSON to extend and customize the chart even more.

Inline Charts in Report

Another interesting use of charts is in reports. It’s actually easy to include a percentage bar in your own report. Go to a number column in your report (between 0 and 100) and set the type to Percent Graph. You can see the result in Figure 3-69.

Other interesting use cases of charts are in lists or some custom regions. Looking at, for example, the Bug Tracker sample application or P-Track gives you some other ideas on how to incorporate badges, use inline charts, and combine different components in attractive dashboards.

Resources

These are the resources linked to using charts in a web environment and Oracle Application Express. You might find them useful if you need more information or examples:

• The APEX application used in this book:

  https://www.apexrnd.be/ords/f?p=APEX5_EXPERT_CHART

• APEX documentation, accessibility in Oracle Application Express:

  http://apex.oracle.com/doc50

• AnyChart 6.x resources:

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

• AnyChart XML reference:

  http://www.anychart.com/products/anychart/docs/xmlReference/index.html

• AnyChart plugin for APEX:

  https://www.apexrnd.be

• Hilary Farrell from the APEX Development Team also has a sample chart application with many demos and tips:

  http://apex.oracle.com/pls/apex/f?p=36648

• Firebug for Firefox:

  http://getfirebug.com

Summary

I started this chapter with an overview of the charting possibilities in Oracle APEX. There are two big charting types in APEX 5.0: HTML5 charts and Flash charts. You learned that Flash charts are not further enhanced and that HTML5 charts are the way to go.

I explained in great detail how the charts are working behind the scenes so you can identify quickly how to do something or where to look in case of unexpected behavior.

You saw how you can customize the AnyChart XML and enhance the charting by using dynamic actions and by creating charts and dashboards manually.

Using charts in APEX is a great way for your users to visualize the data they work with day-in and day-out. The native AnyChart charts combined with the newer charting plugins have everything on board to fulfill your charting dreams, and there is more to come in the future. If the current implementation of charting doesn’t include a particular feature, there are already many extensions and plug-ins available that give you that functionality today.

Good luck with charting, and enjoy this wonderful technology!