To add an HTML chart to your page, run the Create Region wizard and select Chart – HTML Chart. Follow the rest of the wizard. There's actually only one important screen in that wizard which defines the entire chart: the Source screen (see Figure 2-1). There you enter the query to retrieve the data for the chart to use, and you define how you want the chart to look. That HTML charts are very limited is demonstrated by the number of chart types you can use. You can only select whether the chart will show horizontal or vertical bars. Also, there are only three possible scales for the charts: 200, 400, and 600.

Figure 2-1. HTML Chart Source

The query is always in the same format:

select link, label, value
from table

The link column stands for the URL you can navigate to when you click the label, the label is the text that goes with the bar, the value is the numeric column that defines the bar size, and the table is the table or view you want to query from.

We created two HTML charts on top of the DEMO_PRODUCTS table. The result looks like Figure 2-2. On the left is a vertical HTML chart with all available attributes selected, and on the right is a horizontal HTML chart with minimal attributes selected.

Figure 2-2. HTML chart result

Running a page with HTML charts is very fast and there is almost no overhead generating the chart. The time that is necessary to render the page is completely linked to the query. Looking at the debug output in Figure 2-3, you can see that timing is negligible.

Figure 2-3. Debug output of a page with two HTML charts as in Figure 2-2

The reason HTML charts render so quickly becomes clear when you look behind the scenes. As the name states, HTML charts render straight HTML. Basically, a table gets rendered with a couple of columns (<td> tags).

Inside these <td> tags there is a one-by-one pixel image rendered, with a specific height and width, calculated by APEX based on the results of the query, so the image looks bigger.

You can see a part of the HTML code for the first chart:

<table class="standardLook">
<tbody>
  <tr><td align="center" colspan="10"></td></tr>
  <tr>
    <td valign="bottom">
      <img src="/i/blue.gif" alt="Business Shirt - 50" width="40" height="67" style=

"border-left:1px #ffffff solid; border-top:1px #ffffff solid; border-right:1px #000000

 solid; border-bottom:1px #000000 solid;">
    </td>
    <td valign="bottom">
      <img src="/i/green.gif" alt="Trousers - 80" width="40" height="107" style=

"border-left:1px #ffffff solid; border-top:1px #ffffff solid; border-right:1px #000000

 solid; border-bottom:1px #000000 solid;">
  </td>

The technique that was used to generate the bar, using a 1×1 pixel image (the color varies depending on which image is used) with a custom width and height and a style attached to it, might be useful in other parts of your Oracle APEX project—for example, if you want to have an inline chart in your report to represent a percentage or if you want to show how much has already been consumed of a total.

In APEX itself you find this method used in a couple of other places as well, for example, in Administration

Figure 2-4. Graph inline in Application Changes by Day report

The method APEX uses in the HTML chart could be even a bit more performant if it always used the same image instead of using a different image by color. The image would need to be a transparent 1×1 pixel image that has a style attached to it to give it the color you selected. Obviously we are talking about very small gains, but the browser would have to do only one request for the one transparent image, whereas now it has to do multiple requests depending on the number of different colors you have.

One of the attributes of the chart is the color; if you go with just one color instead of "random colors", the performance would be the same as with the transparent image.

In the Monitor Activity section where APEX uses this technique in the reports, APEX uses a transparent 1-pixel image, which is more performant.

If you want to verify yourself how many requests the browser is doing for your page, you can easily do that with Firebug, which is a plug-in of Firefox.

In Figure 2-5 you find the Firebug output of the chart with the random colors for the bar. In Figure 2-6 it is the same chart but with only one color. If you compare the time it takes, the number of requests it handles, and the amount of data it transfers to render the chart, you can see the difference.

Figure 2-5. HTML chart with random colors

Figure 2-6. HTML chart with one color

If you are creating HTML charts yourself and are really concerned about performance, you could even opt to only work with <div> tags instead of images. That would mean the browser doesn't have to do any request for the image. You would have two <div> tags on top of each other but with a different style (background-color and width).

Flash charts were introduced with the release of APEX 3.0 in 2007. Next 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 their 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 themselves because charting is a whole area in itself and evolves very quickly. There are many charting engines around, but going with AnyChart was definitely not a bad choice, as they are committed to evolving their 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, 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 later AnyChart 5 were used.

With the release of APEX 4.0, AnyChart 5 is now completely supported in the wizards. In fact, all new charts you create will automatically use the AnyChart 5 engine. As Oracle wants APEX to be compatible with older versions and needs to support existing applications using Flash charts, AnyChart 3.3 is also included in APEX 4.0. So if you run your APEX 3.x application in APEX 4.0, it will still show AnyChart 3.3 charts. Please note that the version of AnyChart 5 is a special version compiled for Oracle and doesn't include all available chart types that AnyChart 5 supports.

Over time, AnyChart has developed other charting components. Next to the AnyChart charting solution, they now also have 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 can create Gantt and map types declaratively too.

Let's say you have a blank page in an APEX application and you want to add a chart to it. From APEX 4 onwards, I recommend you use the Tree view instead of the Component view for your page. The Tree view allows you to access the different components (and make changes) a lot more quickly. That is why all screenshots and the actions I'll describe will be based on using the Tree view.

To add your first chart, right click Regions in the Tree view of the page and select Create in the menu to add a new region to that page. For region type, select Chart (Figure 2-7).

Figure 2-7. Select as Region Type: Chart

On the next page of the wizard, go with Flash Chart. Note that in APEX 4.0 there is no way to create an SVG chart anymore, as there was in APEX 3.x (Figure 2-8).

Figure 2-8. Select Flash Chart

You'll see that the progress indicator of the wizard on the left-hand side has changed and now asks you to select the chart types you want to use (Figure 2-9).

Figure 2-9. Select a chart type

One of the great new enhancements in the APEX 4.0 charts wizard is that you can see how the chart type looks like without having to click the Update button, as you had to do in APEX 3.x.

In APEX 4.0 there are more types to choose from, and they have a fresher look and have more options. The Chart wizard in APEX 4.0 also categorizes the charts nicely: you first select the main chart type and then get the choice to select the subtype if that is available. For example, the Line chart type doesn't have a subtype whereas the Column chart type has eight subtypes (Figure 2-10).

Figure 2-10. Column subcharts

Clicking the subtype brings you to the Create Region screen, which is not different than any other Region screen in APEX (Figure 2-11). By default the region template is Chart Region, but you can choose any you like. Now give the region a title and click Next.

Figure 2-11. Create Region

The next screen defines the look and feel of the chart and which options you want to include (Figure 2-12). You can go with the defaults during creation and adapt them later on 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 that (Left, Right, Top, Bottom, Float).

In APEX 4.0 there are a lot more Animations to choose from than there were in APEX 3.x. Also, the ability to include scrollbars is completely new in APEX 4.0. Scrollbars are particularly handy when you have large sets of data and you still want to have a good view.

We'll come back to the different attributes in the "Understanding the Chart Attributes" section.

Figure 2-12. Define the chart attributes

Previously, we defined how the chart would look, but we 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 2-13). 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

Figure 2-13. Define the SQL query of the chart

As said before, the query of the chart depends on the type of chart you selected. We'll come back to the different select statements for the different chart types in the section "Understanding the Chart Attributes."

Once the Chart region is created, it will appear on the page with a chart icon in the Tree view (Figure 2-14). Drilling into the chart region shows the series which contains the SQL statement to provide the chart with data.

Figure 2-14. Chart region in Tree view of the page

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

Figure 2-15. The chart displayed on the page

When editing the chart region (double-click the region or right-click and select Edit) and looking at the region definition, you might be surprised at first as there is only some HTML code in the Region Source (Figure 2-16). But remember, APEX has to include a Flash object in the page, and the way you do that is to use exactly that HTML code.

The region source consists of an object tag and an embed tag, which both have some parameters. If you look a bit deeper in that code you see the parameters of the object and embed tag are almost the same. When I first saw this code I wondered why you would want to include the same code twice. The answer is browser compatibility. In the knowledge base of Adobe (owner of Flash) it is explained well. The OBJECT tag is used by Internet Explorer on Windows and the EMBED is used by Netscape-like browsers (Macintosh and Windows) and Internet Explorer (Macintosh) to direct the browser to load the Flash Player. Internet Explorer on Windows uses an ActiveX control to play Flash content while all other browser and platform combinations use the Netscape plug-in technology to play Flash content. This explains the need for the two tags.

Figure 2-16. The region definition of the chart

In the parameters of the object and embed tags you find parameters for quality, width, height, name, scale, and so on. The parameters either have a static value assigned to them (for example, quality="high") or a token (for example, width="#WIDTH#"). APEX replaces these tokens when it renders the page. You can change the value of these tokens in the Chart Attributes tab (see further on). Normally you won't change these parameters, unless you want to make the chart more dynamic and, for example, let the user define a custom loading message or change the width or height of the chart. In that case, you would replace the token with "&PAGE_ITEM.", a reference to a page item. You are responsible for giving your page item a value before APEX renders the chart, for example, by using a computation or a page process.

Figure 2-17. The region definition of the chart line by line

Figure 2-17 provides a detailed explanation of the region definition:

The previous section covered the region definition, but more importantly, if you want to change the appearance and behavior of a chart, this can be done via the Chart Attributes (second tab on the Edit Region page or right-click in page Tree view and Edit Chart).

Depending on the chart type chosen, you have different sections in the chart attributes (Figure 2-18):

Figure 2-18. The different sections of the chart attributes

The Chart Settings allow you to change the chart type, the title, the size of the chart and the look and feel. Figure 2-19 shows the settings, which you can use as follows:

Figure 2-19. The main settings of the chart in Chart Settings

The Chart Series holds the select queries which 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 2-20). In the next section, "Adding Multiple Series," we go into more detail about the series.

Figure 2-20. One or more series per chart in chart series

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

Figure 2-21. Define the main look and feel of the chart in display settings.

In the Axes Settings shown in Figure 2-22, you define the title of the Axis, the interval, and the format of the values.

Figure 2-22. The Axes Settings

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

Figure 2-23. Define where the legend should appear

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

Figure 2-24. The font settings of the different text on the chart

The Chart XML page shows the XML that APEX will send to the AnyChart SWF file (more on that in the section "Behind the Scenes". See Figure 2-25.)

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.

For a complete reference of the XML that can be used and a detailed explanation, see the AnyChart website at http://www.anychart.com/products/anychart/docs/xmlReference/index.html.

Figure 2-25. The Chart XML

In the Refresh section, you can set Asynchronous Update to Yes to give the chart new data at an interval you specify (Figure 2-26). This is very 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 2 seconds are discouraged as that would mean APEX has to constantly retrieve the data. The maximum value for this setting is 99999.

Figure 2-26. The region definition of the chart line by line

When the page is run the #CHART_REFRESH# token from the Region Attributes will be replaced by the following JavaScript:

<script type="text/javascript" language="javascript">
function chart_r10604411968639592_InitRefresh(pNow) {
  setTimeout("chart_r10604411968639592_InitRefresh(true)",10000);
  if (pNow){apex_RefreshFlashChart (2, '10604411968639592', 'en'),}
}
apex_SWFFormFix('c10604411968639592'),
addLoadEvent(chart_r10604411968639592_InitRefresh(false))
</script>

The big number is the id of the chart and you will only see the addLoadEvent when the Asynchronous Update select list is set to Yes. The addLoadEvent enables the function. In the section "Customizing Charts by using Custom XML, Dynamic Actions and JavaScript" we will customize this JavaScript.

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:

The result is shown in Figure 2-27.

Figure 2-27. An example of a modified chart

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). Below are the steps to add a 2D column chart and two trend lines, one for the forecast and one for the target:

Figure 2-58. AnyChart Gallery example of trend lines

Figure 2-59. XML code behind the example in the AnyChart Gallery

Figure 2-61. 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 and, presto, you extend your chart!

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 we 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 2-62). It would mean we define thresholds, sales numbers below a certain number (red), between numbers (yellow) and over a certain number (green).

Figure 2-62. Column chart with custom XML and Dynamic Action to show thresholds

These are the steps to create a chart with thresholds:

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

If you have Asynchronous Update set to Yes, you will also need to change the Region and replace #CHART_REFRESH# with some custom JavaScript; instead of calling APEX's procedure to refresh the data, it needs to call your own. One way of doing that is by copying the Dynamic Action and assigning it to the change event of P18_CHART_XML.

In the JavaScript you would trigger the change event on that item, so the Dynamic Action fires. Following JavaScript code does that:

<script type="text/javascript" language="JavaScript">
var chartName = '#CHART_NAME#';
chartName = chartName.substring(1);
function chart_r#CHART_NAME#_InitRefresh(pNow) {
  setTimeout("chart_r#CHART_NAME#_InitRefresh(true)",5000);
  if (pNow){
    apex_RefreshFlashChart (&APP_PAGE_ID., chartName, 'en-us'),
    $('#P18_CHART_XML').trigger('change'),
  }
}
apex_SWFFormFix('#CHART_NAME#'),
addLoadEvent(chart_r#CHART_NAME#_InitRefresh(false));
</script>

To get it working, replace the #CHART_REFRESH# token you find in the Region Definition of the chart by the above JavaScript.

Figure 2-63. AnyChart XML Reference documentation—thresholds node

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 on parts of the chart. In the following example we will create a multiseries chart with multiple axes and different tooltips per series. We will also make the width and height of the chart depend on what the user defined on the page. We 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).

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:

Because you will load the entire chart with JavaScript you need to add the JavaScript package that comes with AnyChart. Edit the Page and in the HTML Header put the following:

<script type="text/javascript"
src="#IMAGE_PREFIX#flashchart/anychart_5/js/AnyChart.js"></script>

Because we 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 the following variable declaration:

var chart;

You now need to initialize the chart by calling a specific AnyChart function and generate the data and give that to the chart. From APEX 4.0 onwards you should try to do as much JavaScript as possible through Dynamic Actions. Create a new Advanced Dynamic Action to generate the XML:

We use the same XML DB features of the database to generate the data part. We also have a variable to store the XML definition of the chart. Note that the above example only works with datasets that are less than 32K because there is currently a limitation in APEX that you can assign a maximum of 32K to a Page Item. If you need to work with big datasets you need to slightly change the code. Later in this chapter, we will generate a dashboard and use the other technique which supports XML bigger than 32K. Also note that we used NVL(comm,0) for the value in the second series. If you don't use NVL the line will be incomplete as 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.

A second True Actions to load the chart:

The chart JavaScript object we defined on the page level. The above code initiates a new AnyChart chart. The object can have different properties and events; in this case we 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 as shown in Figure 2-64.

Figure 2-64. A chart manually created with JavaScript

You also want to change the tooltip of Series 2 to have a custom message. To achieve that, follow these steps:

Edit the Dynamic Action "Load Chart" - "Set Value" in the True Action. 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;

In the point node you now added a tooltip node. The XML that gets generated looks like this:

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

To let the user define the width and height of the chart, you need to add two other dynamic actions that fire 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 Width

Add a new Dynamic Action with the name: Change Chart Height

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.

In this first example a manager wants to have an overview of the salaries he's giving to the respective departments and employees. To fullfill the manager's dream, we will create a page with one region and three subregions which hold the different charts, so it looks like the charts are in one region (Figure 2-65):

Figure 2-65. A dashboard with three charts: one pie and two 2D column charts

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

Figure 2-66. Dashboard chart drilled-down to the Sales department

Behind the scenes, we used the wizard to create three different charts:

If you want to create the above example yourself while you are reading, have a look at Figure 2-67, 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 have 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 we created in that region, with the value of the slice the user clicks.

We 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, (the Page is 8 and Clear Cache is 8). Figure 2-67 shows how the rendering of page 8 looks, where no page processing has been defined.

Figure 2-67. Behind the scenes of the simple dashboard page with Submit

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, we created a parent region with three subregions for the charts. That way the charts look like they are combined. The above example has one big drawback: whenever the manager clicks on a link the entire page gets submitted, which isn't a nice effect and doesn't flow that well.

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, we will just refresh the necessary regions so the manager gets a nicer user experience.

We will copy the page of the previous example to page 10 and keep everything we did. We will add another report which 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 we 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 on the pie chart, you need to change the link in the pie chart. We 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:

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

To solve the refresh issue, you use a Dynamic Action in APEX. Our 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 our dynamic action is defined:

If you are not familiar with Dynamic Actions, you will find more information in Chapter 7. The following is a brief explanation of what our dynamic actions do. We first need to get the value of P10_DEPTNO into session state; one of the techniques to do that is used in Action 1. Action 2 will refresh the two charts. To know which chart it needs to refresh, we add the following code to the Region Definition of the chart with the average, maximum, and minimum salary:

<script type="text/javascript" language="javascript">
var chartNameAvg = '#CHART_NAME#';
</script>

to the chart with the salary by employee we add:

<script type="text/javascript" language="javascript">
var chartNameSal = '#CHART_NAME#';
</script>

The #CHART_NAME# token gets replaced at runtime by the chart name that APEX is using, and gets assigned to a global JavaScript value that we can reuse in the dynamic action. You need to use the apex_SWFFormFix to make it Internet Explorer–compatible as Internet Explorer has issues with objects that get changed on the fly.

Action 3 is a built-in dynamic action that refreshes the report.

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 2-68 represents the result of the manager clicking on a slice of the pie chart.

Figure 2-68. Dashboard chart and report drilled-down to Sales department (with JavaScript)

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 on something in the chart, but you didn't see this in the first example because the entire page was submitted and rerendered. Figure 2-69 shows a behind-the-scenes view of the page.

Figure 2-69. Behind the scenes of the simple dashboard page with JavaScript

This example is completely different from previous examples and takes us beyond APEX. In this example, the manager wants to know how well his products have sold, broken down by state, and how many products every salesperson has sold. The manager also wants drill-down capabilities so that if he clicks on a state he can see how the products sold for only that state (Figure 2-70).

Figure 2-70. A complex dashboard using the full AnyChart capabilities

This example 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 (Figure 2-71). Unlike in the previous examples, this dashboard only consists of one Flash object, but inside that one Flash object three different charts are defined. The first one is a map, the second one a 2D bar chart, and the third one a 2D column chart.

Figure 2-71. Behind the scenes of the complex dashboard

The definition of the HTML Region is

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

And this is how the Dynamic Action is defined:

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 we didn't use a hidden item to store the XML as that would give a problem for large datasets (more than 32K). Assigning a value to a page item is limited to 32K, but because the dashboard we want to create is based on three different charts, passing the information for all three charts will exceed that 32K 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 := '<anychart>
    <settings>
        <animation enabled="True"/>
    </settings>
    <dashboard>
        <view type="Dashboard">
            <title padding="2">
                <text>USA Sales Map</text>
            </title>
            <background>
                <inside_margin all="3" top="10"/>
            </background>
            <vbox width="100%" height="100%">
                <margin all="0"/>
                <hbox width="100%" height="60%">
                    <margin all="0"/>
                    <view type="Chart" source="mapChart" width="100%" height="100%"/>
                </hbox>
                <hbox width="100%" height="40%">
                    <margin all="0"/>
                    <view name="productsView" type="Chart" source="productsChart"

width="50%" height="100%"/>
                    <view name="personsView" type="Chart" source="personsChart"

width="50%" height="100%"/>
                </hbox>
            </vbox>
        </view>
    </dashboard>
    <charts>
        <chart plot_type="Map" name="mapChart">
            <chart_settings>

<controls>
                    <label inside_dataplot="True" position="Bottom" align="Near"

text_align="Center">
<text>Click on a state to see
sales splitted by Products and
Sales Representatives</text>
                    </label>
                </controls>
                <title enabled="False"/>
                <chart_background>
                    <border type="Solid" color="#CCCCCC" thickness="1"/>
                    <corners type="Square"/>
                    <effects enabled="false"/>
                    <inside_margin all="10" top="5"/>
                </chart_background>
                <data_plot_background enabled="False"/>
                <legend enabled="True" ignore_auto_item="True" inside_dataplot="True"

 rows_padding="0" align="Far">
                    <title enabled="False"/>
                    <format>{%Icon} - ${%RangeMin}{numDecimals:0,scale:(1000)|(k)} -

 {%RangeMax}{numDecimals:0,scale:(1000)|(k)}</format>
                    <items>
                        <item source="Thresholds"/>
                    </items>
                </legend>
            </chart_settings>
            <data_plot_settings enable_3d_mode="False">
                <map_series source="usa/country/states_48.amap">
                    <grid>
                        <parallels enabled="False">
                            <labels enabled="False"/>
                        </parallels>
                        <meridians enabled="False">
                            <labels enabled="False"/>
                        </meridians>
                        <background enabled="False"/>
                    </grid>
                    <defined_map_region>
                        <tooltip_settings enabled="True">
                            <font bold="True"/>
<format>
{%REGION_NAME}
${%Value}{numDecimals:1}
</format>
                        </tooltip_settings>
                        <label_settings enabled="True">
                            <format>{%REGION_ID}</format>
                            <background>
                                <corners type="Rounded" all="2"/>
                            </background>
                        </label_settings>

<map_region_style>
                          <fill type="Gradient" color="%Color" opacity="0.75">
                            <gradient angle="45">
                              <key position="0" color="%Color" opacity="1"/>
                              <key position="1" color="Blend(DarkColor(%Color),%Color,0.4)"

 opacity="1"/>
                            </gradient>
                          </fill>
                          <border color="White"/>
                          <states>
                            <pushed>
                              <fill type="Gradient" color="%Color" opacity="0.75">
                                <gradient angle="45">
                                  <key position="0" color="%Color" opacity="1"/>
                                  <key position="1" color="Blend(DarkColor(%Color),

%Color,0.4)" opacity="1"/>
                                </gradient>
                              </fill>
                            </pushed>
                          </states>
                        </map_region_style>
                    </defined_map_region>
                </map_series>
            </data_plot_settings>
            <palettes>
                <palette name="salesPalette">
                    <item color="Red"/>
                    <item color="Yellow"/>
                    <item color="Green"/>
                </palette>
            </palettes>
            <thresholds>
              <threshold name="thrSales" type="Quantiles" range_count="5"

 palette="salesPalette" />
            </thresholds>
      <data>
                <series name="Region Sales" threshold="thrSales">
                    <actions>
                        <action type="updateView" view="productsView"

 source_mode="internalData" source="productsChart">
                            <replace token="{$ProductASales}">{%PR_A}{enabled:False}</replace>
                            <replace token="{$ProductBSales}">{%PR_B}{enabled:False}</replace>
                            <replace token="{$ProductCSales}">{%PR_C}{enabled:False}</replace>
                        </action>
                        <action type="updateView" view="personsView"

 source_mode="internalData" source="personsChart">
                            <replace token="{$kateSales}">

{%KATE_SALES}{enabled:False}</replace>
                            <replace token="{$jacksonSales}">

{%JACKSON_SALES}{enabled:False}</replace>
                            <replace token="{$adamSales}">

{%ADAM_SALES}{enabled:False}</replace>
                            <replace token="{$brentSales}">

{%BRENT_SALES}{enabled:False}</replace>
                        </action>
                    </actions>';
  dbms_lob.writeappend( l_chart, length(l_chart_v), l_chart_v);

  select xmlagg( xmlelement("point", xmlattributes(p.c1 as "name", p.c2 as "y"),
         (select xmlelement("attributes",  xmlagg( xmlelement("attribute", xmlattributes

(t.c3 as "name"), t.c4)))
            from salespp t
           where t.c1=p.c1))).getclobval()
    into l_chart_data
    from (select distinct c1,c2 from salespp) p;

  dbms_lob.append(l_chart, l_chart_data);

  l_chart_v := '
                </series>
            </data>
        </chart>
        <chart plot_type="CategorizedHorizontal" name="productsChart">
            <chart_settings>
                <title>
                    <text>By Products</text>
                </title>
                <axes>
                    <y_axis position="Opposite">
                        <title enabled="False"/>
                        <labels>
                            <format>${%Value}{scale:(1000)|(k),numDecimals:0}</format>
                        </labels>
                    </y_axis>
                    <x_axis>
                        <title enabled="False"/>
                    </x_axis>
                </axes>
                <chart_background>
                    <border type="Solid" color="#CCCCCC" thickness="1"/>
                    <corners type="Square"/>
                    <effects enabled="false"/>
                    <inside_margin all="10" top="5"/>
                </chart_background>
            </chart_settings>
            <data_plot_settings>
                <bar_series>
                    <tooltip_settings enabled="True">
<format>
{%Name}
${%Value}{numDecimals:2}
</format>

</tooltip_settings>
                    <label_settings enabled="True">
                        <format>${%Value}{numDecimals:1,scale:(1000)|(k)}</format>
                        <font bold="False"/>
                        <background enabled="True">
                            <fill enabled="True" color="White" opacity="0.3"/>
                            <inside_margin all="0"/>
                            <corners type="Rounded" all="2"/>
                        </background>
                    </label_settings>
                </bar_series>
            </data_plot_settings>
            <data>
                <series>
                    <point name="Product A" y="{$ProductASales}"/>
                    <point name="Product B" y="{$ProductBSales}"/>
                    <point name="Product C" y="{$ProductCSales}"/>
                </series>
            </data>
        </chart>
        <chart plot_type="CategorizedVertical" name="personsChart">
            <chart_settings>
                <title>
                    <text>By Sales Representative</text>
                </title>
                <chart_background>
                    <border type="Solid" color="#CCCCCC" thickness="1"/>
                    <corners type="Square"/>
                    <effects enabled="false"/>
                    <inside_margin all="10" top="5"/>
                </chart_background>
                <axes>
                    <y_axis>
                        <title enabled="False"/>
                        <labels>
                            <format>${%Value}{numDecimals:0,scale:(1000)|(k)}</format>
                        </labels>
                    </y_axis>
                    <x_axis>
                        <title enabled="False"/>
                    </x_axis>
                </axes>
            </chart_settings>
            <data_plot_settings>
                <bar_series group_padding="0.3">
                    <tooltip_settings enabled="True">
<format>
{%Name}
${%Value}{numDecimals:2}
</format>
                    </tooltip_settings>
                    <label_settings enabled="True">

<format>${%Value}{numDecimals:1,scale:(1000)|(k)}</format>
                        <font bold="False"/>
                        <background enabled="True">
                            <fill enabled="True" color="White" opacity="0.3"/>
                            <inside_margin all="0"/>
                            <corners type="Rounded" all="2"/>
                        </background>
                    </label_settings>
                </bar_series>
            </data_plot_settings>
            <data>
                <series palette="Default">
                    <point name="Kate" y="{$kateSales}"/>
                    <point name="Jackson" y="{$jacksonSales}"/>
                    <point name="Adam" y="{$adamSales}"/>
                    <point name="Brent" y="{$brentSales}"/>
                </series>
            </data>
        </chart>
    </charts>
</anychart>';
  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
          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 we hardcode many things—for example, the title and the persons—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 htp.prn, you have to write a loop to pass the XML back in chunks. The look and feel of the chart in the above example is defined by a string, the data is retrieved from the salespp table, and you build the XML using the XML DB feature as we have done in previous examples.

Looking closer into the XML of the dashboard, AnyChart supports interactivity by defining Actions. We defined an action when a user clicks on the map to refresh the two detail charts of the products and persons. In a dashboard, AnyChart calls them views. So there is only one Flash Object, but because the View type is Dashboard and we defined how these views look, AnyChart will render multiple charts into one.

There are many more options in AnyChart; for example, 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 we 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.

Looking at the Oracle APEX Forum, most of the requests there 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 it 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 website.

All the above 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 and/or JavaScript to produce the chart you want.

Sometimes people say they don't see the chart. In that case it might be that they have issues with the #HOST# substitution string that APEX is using in the region definition of the chart:

"#IMAGE_PREFIX#flashchart/anychart_5/swf/#CHART_TYPE#.swf?XMLFile=#HOST#apex_util.flash?p=

&APP_ID.:&FLOW_PAGE_ID.:&APP_SESSION.:FLOW_FLASH_CHART5_R#REGION_ID#"

APEX might replace the #HOST# substitution string incorrectly. Many people running a reverse proxy or running in https seem to initially have this problem. To solve this issue, you have a few options:

With the following command you can verify if the host name and port are set correctly:

select owa_util.get_cgi_env('HTTP_HOST') from dual;

On some occasions you might receive the following error:

Flash Security Error:
AnyChart can not be launched due to Flash Security Settings violation.
Please refer to Security Error Article in AnyChart Documentation to fix this issue.

This error is linked to the previous error with the #HOST#, but this error is a more global security error that any Flash object might have. You receive this error when the AnyChart SWF and the data for the SWF are on a different domain. In some cases you might access APEX through one web server, while your data comes from another. In this case the #HOST# is different if you use the standard way to generate the XML for the chart, or you might have decided to call a procedure on another server, but this is something that Flash doesn't allow unless you specify a cross-domain policy file. Another issue might be that the SWF is loaded from http while the data comes from https.

The solution is, you use the same domain and protocol (http, https) or you tell Flash the other domain is a trusted location. In the documentation of AnyChart you will find an example of a policy file (AnyChart Documentation