So you may have created several charts within APEX already, and there are plenty of templates available for these such as Line, Bar, Stacked, etc.
The charting technology in APEX uses a JavaScript (HTML5) based charting solution called AnyChart and there are plenty of AnyChart examples here.
To recap on the basics, like many components in APEX, you start with a SQL.
The SQL becomes a Series – a chartable set of data. The Label becomes the X axis and any other columns which appear after the Label appear on the Y axis with their column alias becoming the Series appearing on the Legend. Are you with me so far?
Take a while to review the all the settings under Chart Attributes and you’ll see that the APEX Team has provided an excellent interface to many AnyChart settings.
Right down at the bottom of the page we have a Custom XML section. If you haven’t used this yet, then this is the area where you will extend your Charting skills.
So what is this? Well to read the XML you need to select “Yes” to “Custom” and then copy and paste the XML in to an online XML formatter to make it readable. You’ll see that all the chart look & feel settings have been converted to XML… for example, dig deep and you’ll find the name you gave to the chart.
Here you can modify the XML to do things like: change how the points are displayed, change what the tooltip says, add the time element to date points, add decimal points, etc., etc. As a quick demo I will change the line
to
This will ignore any missing points and create a nice smooth line chart for the Commission Series.
So if the Custom XML is how the Chart should look, then where is the data? Right down at the bottom you’ll see a #DATA# substitution string. When a Chart is rendered, APEX replaces the #DATA# substitution string with a XML conversion of the SQL results… and voilà the chart is displayed.
We can’t really see what #DATA# contains, although we do have the AnyChart Documentation, so we know what format of XML it accepts so we can replace it with our own XML. This isn’t one of those blogs which tells you to go off and read the documentation, so now I’ll describe a neat trick I found of converting your SQL to the required format XML.
Inspired by Tom Petrus’ Charting Application, I performed some tweaks to what I read, to convert the above SQL to Chart XML.
Running the SQL returns the below (shortened for brevity); which is a typical XML format expected for #DATA#
I placed this code in Before Header Process and created a hidden Item called P1_XML_DATA within the Chart Region.
I then simply replace #DATA# in the Custom XML with &P1_XML_DATA. and I’m good to go.
I didn’t just do this to complicate matters for myself; there are many advantages of performing this such as being able to avoid the 30 character limit for Series Names (Oracle identifiers are not allowed to exceed 30 characters in length) on Legends, being able to define exactly which type of chart I want and to define custom colours for my series.
In the below picture I have changes the series colours and used a legend over 30 characters:
There are far more configurations in AnyChart than I can write in a single article. Therefore in this blog, I’ve given a taster to what can be achieved by exploring the configuration beyond the standard attributes found within APEX.
Happy Charting.
(This article was based on APEX 5.0)
Author: Matt Mulvaney
Job Title: Senior Oracle APEX Development Consultant
Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.
