Tuesday, August 14, 2012

extending chart capabilities in APEX 4.1.x

In the Expert Oracle Application Express book I wrote a chapter about charting in APEX. At the moment of writing APEX 4.0 was production. Things changed over time (4.1.x) and will change further in the future (4.2). If you try to run my sample application in APEX 4.1.x, following charts won't work anymore:
  • Manual Flash Chart with JavaScript
  • Complex Dashboard with Actions (full AnyChart)
  • Complex Dashboard with Events (full AnyChart)
The reason is, there is a change in the files the AnyChart team gave to Oracle. Just to be clear, the above examples go further than what the APEX wizards give you, so it's not really something we can complain about. 

The issue:

If you look at the code on page 143, this is what I wrote down:

The issue is the OracleAnyChart.swf file and the AnyChart.swf file. They are different than the files supplied in APEX 4.0 (which the chapter was based on). With every new release of APEX, AnyChart provides the latest chart engine, but this time, next to update the engine, they limited the file in what it can do (which they didn't do before). Oracle didn't license all the charts and the the full capabilities of the AnyChart engine so far. It remains to be seen which version we get with APEX 4.2. It should be AnyChart 6 with the support of HTML5 charts, so it might be that the other functionalities will work again too. 

Anyway as I started to get regular emails about it, I guess because more and more people are on APEX 4.1 now, I decided to blog about the different solutions you have to get my examples working.

The solution(s):

Just to be clear, the techniques I described to get most out of the charts in APEX are still valid, but for the three examples that didn't work anymore, you need to do an extra step.

- Option 1 - The solution is to not reference the APEX 4.1.x swf files, but reference swf files that do work. You can for example download the latest AnyChart files and copy them on your webserver, preferable in a different path than the APEX /images/ folder. I encourage you to copy them in your own path and leave the APEX folder as it is.

- Option 2 - As for some people it seems not trivial to do that, I created another solution by using the APEX Plugin mechanism. The plugin is basically a framework around the full AnyChart files. The only thing you have to do, is come up with a datasource (file, procedure, resource profile or REST service) that has the correct XML for the chart. You find the xml reference for AnyChart here.

The APEX plugin is of type region, so once you installed the plugin in your application, the only thing you have to do is create a new region on your page. I included three region attributes in the plugin; the width and height you want the chart to be and the datasource (procedure name).

In the above example I've a procedure called MANUAL_CHART in my TRAINING schema which will generate the correct xml. This is the same as the example in the book. The result looks like this:

As the datasource (procedure) gets called as an external process you need to make sure it has the correct grants.

You can see the above example live for APEX 4.1.x and for APEX 4.2 EA2. (note that depending when you read this post versions might have changed and url's might fail)

Note that I included a trial version of AnyChart, but you can easily replace that file. As APEX 4.2 is so close for production I also wanted to wait to see what files are distributed there, nevertheless I wanted to give you another solution to work around the issue in APEX 4.1.x. You can download the plugin here (which includes the procedure to generate the above chart).

Once APEX 4.2 is live I plan to revise the entire chapter as things changed a lot, not only in the APEX 4.2 wizards, but also by the new HTML5 engine of AnyChart.

On another note; the AnyChart Integration Kit we distributed before is end-of-live, instead we will provide plugins to extend APEX. At the moment we have following plugins:

  • Bubble Chart
  • Dynamic Action plugin to enable HTML5 charts for existing charts in APEX 4.1.x
  • Framework Plugin (see above), which can produce any type of chart (dashboard, heatmap, ...)
  • Full Gantt Chart Plugin with drag-drop functionalities
  • AnyStock Plugin
Some of those plugins are payable, others we distribute for free, but we charge for support, upgrades, training and development of the charts you want. As the plugins are based on the AnyChart engine (which is not free), a lot will depend on what AnyChart provides to Oracle in APEX 4.2 if we can distribute the full AnyChart license with the plugins. Negotiating with AnyChart about a deal for the APEX community is postponed till after the APEX 4.2 release.

Hope you are back on track with charting by this update :-)


Martin Eysackers said...


I made a region based on your plugin, also performed grant execute on manual_chart to apex_public_user
the region displays with an error :
error loading file manual_chart?....
am I missing something ?


Dimitri Gielis said...

Which version of APEX are you trying this in?

Anonymous said...

Could you possibly update the situation relative to APEX 4.2?


Dimitri Gielis said...

APEX 4.2 has HTML5 charts build-in and uses the latest AnyChart version. The only thing it doesn't allow is to do other charts than what the wizards allows you to do.

We build plugins to do that but we made them payable now. You can contact me for pricing if you are interested.

thargenediad said...

What do the plug-ins allow you to do? I'd like to be able to create something like this: https://dl.dropbox.com/u/65658133/launch_milestones.png

But I'm guessing I won't be able to do that using the default ApEx wizards. Or do you think it's possible?

Dimitri Gielis said...

The plugins allow you to create any chart that you find on anychart.com.

Have a look at the sample application in APEX of Project Management, it includes a more milestones type report/chart which might fit you.

The wizard in APEX doesn't provide what you are looking for yet.

thargenediad said...

Thank you, Dimitri!

I don't see it in my apex.oracle.com workspace. Do you have a link?

thargenediad said...

Oh, is it the "P-Track" application?

Dimitri Gielis said...

Yes that is the one. It has some very nice screens in which might be helpful for you.

thargenediad said...

Thank you, Dimitri! It's not as pretty, but I think it might be sufficient for our needs. :)