APEX 4 (bug) - Series Type (Bar, Line, Marker) and Multiple Series

A new feature in APEX 4.0 is that you can define per series what you want the series type (Bar, Line or Marker).

Lets start with an example.

We create a 3D Column chart based on the salary of the employees.

Our first Series looks like this (the Series Type is per default Bar):

select null as link, ename, sal

from emp

Now to add a second series you have two choices, either you add another value to the existing series or you create a new series. We will go with the first option, so our query becomes:

select null as link, ename, sal, nvl(comm,0) as comm

from emp

If you run the chart you see for both salary as commission a bar (column)

Now we want to add a line with the average of the salary, so we add another series with as sql query:

select null as link, ename, avg(sal) over() as avg_sal

from emp

The Chart Series look like this

Make sure you select Line as Series Type for Series 2 (the average salary)

When you run the chart you expect to see the same chart as above but with a line that represents the average of the salary...

As you can see that is not really what happened. It seems there is a bug in APEX 4.0.2.00.07 (and probably before) with defining multiple series in the same query.

What happened is that the first value, salary, is still a bar, but the second value, commission, changed from bar to line and the third value, average salary, became a bar (that is because our main chart type is a 3D Column chart, so it takes whatever is default). So APEX doesn't take the Series type into account correctly.

The workarounds is to not use the single query with multiple values, but a single query per series.

Running the chart again shows us what we expected

(Found in APEX 4.0.2.00.07)

APEX 4 (bug) - Chart Attributes > Add Series > Build Query button

Let's pretend you still have that 3D Column chart of my previous post or create a new chart.

Now edit the chart and go to Chart Attributes and look for the Chart Series section.

Click the Add Series button, you'll get a screen like

Hit the Build Query button to open the wizard to generate the SQL statement and follow the wizard. Define the owner

Define the table or view

Define the Label and Value...

Hmm... the value option is not here... yes, that is a bug... if you go further with the wizard you will end up with "select * from emp" instead of a real query for the chart.

The strange thing though is that during the creation of the chart it works just fine. Also when you edit an existing series and hit the Build Query button it works ok as you can see on the next screenshot.

My guess is there's something wrong with session state.

The workaround is to edit an existing series, follow the wizard there, copy the sql statement, cancel the edit, and then add a new series and paste the sql statement. Or if you are more experienced and know the syntax of the chart you can just manually enter the sql statement.

(Found in APEX 4.0.2.00.07)

APEX 4 (bug) - Font Grid Label in Charts

For a chart you can define the fonts and colors you want to use for the different components (mostly text) on the chart. This works great except for the Grid Label. It seems that that value never gets stored when you change it.

I thought if I wanted to change the grid lines of the chart I could change the Grid Label settings. I'm not sure why Label is specified after Grid and not Line, as I'm not aware of labels against the grid. It seems that currently there is a hardcoded value of "Black" against the line color of the X axis grid and nothing for the Y axis grid (so default of AnyChart).

Again the workaround is simple if you want to have a different line colors of the grid (X / Y Axis), you can just change it in the XML after you specified Custom XML = Yes.

The chart looks like this after doing the above change:

(Found in APEX 4.0.2.00.07).

APEX 4 (bug) - Axes in Charts

Consider this chart, it just shows the salary of the employees in a 3D Bar chart.

Now, if you want your chart to start with e.g. 500 instead of 0 you need to define the axis minimum value.

The wizard shows X Axis: Min, so lets put 500 as the value in... However running the chart again gives the same result.

This is in fact a known bug in APEX 4 (bug id 11700598), or you can read on it in this forum thread. Basically the Axes Settings should show the Y Axis Min instead of the X Axis.

The workaround is to change your chart type to 3D Column (instead of 3D Bar Chart).

Running the page gives us this result, so yes it's different, but for this chart the wizard is correct. Later you can change the chart type again and it should be ok.

So again we want the Y Axis to start with 500 instead of 0, so we define the Y Axis: Min in the Axes Settings. (see that now Y Axis: Min is listed and not X Axis: Min)

Run the page again and the chart will start from 500 instead of 0.

So now you can change the type back to 3D Bar Chart and you see it applied the start value correctly (the axis start now from 500).

My recommendation to the APEX Development team would be that either we see both X and Y Axis min and max or add a dynamic action to the drop down list of the chart type to dynamically show the appropriate axis. (next to fixing the bug)

!! But there is even a nastier bug related to the above...

Let's say we kept the 3D Column chart type but added a scrollbar for the X-Axis.

Running the page shows us this! The chart is only partially visible.

This is related to the previous bug... as we defined a value of the X-Axis Min when the chart type was still 3D Bar Chart, APEX still keeps that into account... (that is why the workaround worked before). However in this case it has a negative impact on the way the chart is shown.

The way to correct the issue is, change the Chart Type back to 3D Bar Chart, delete the value that is in X-Axis Min, hit the Apply Changes button, edit the chart again and change the Chart Type back to 3D Column.

Running the page again shows the correct chart.

(Found in APEX 4.0.2.00.07).

APEX 4 (bug) - Gradient in Charts

Something surprised me in APEX 4.0.2.00.07 (and probably entire 4.0).

APEX allows to specify a Gradient Angle when you specify two background colors for the chart and put the Background Type to Gradient. (see screenshot)

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.

Running the page results in:

Cool isn't it? ... But I just wanted it a little bit different, I wanted the first color (green) on top and the second color (yellow) at the bottom. So I would have thought if I change the value of the Gradient Angle to 90 that will do it... but no, the same (or very very similar).

Looking at the XML that was generated it showed me:

So although I put 90 degrees in the Gradient Angle, it seems like APEX divided it by something close to 60...

If you want the same as me and really use the Gradient Angle you either have to multiply your value by 60 or change the XML yourselves (Custom XML = Yes and you can change it). I prefer the second option as if they fix it (as I can't believe it was intentional!) you end up with a different gradient angle.

Happy charting ...

APEX 4 (bug) - Add marker to your chart

It seems to me that in APEX 4.0.2.00.07 (and probably entire 4.0, but can't check at the moment) there's a bug with adding markers to your (bar/column) chart. For a Pie chart it works fine.

If you edit your Chart Attributes you can define a Marker in the Display Settings.

However when you run the page and look at the chart there is no Marker to see.

If you need Markers, there are two workarounds at the moment:

- customise the XML (my preferred option): you set Custom XML in the Chart Attributes to Yes and you change the marker section to the marker you want. You will see marker type="None", but you can easily change that. Below I changed it to Cross, to have a cross marker.

- add another Series in the chart of type Marker, but this is not my preferred option because you lose performance as it will need to run the query twice.

When you run the page after you changed the XML, the chart with the marker looks like this (see the cross on top of the bar):

I'm sure the APEX development team will fix it soon, but for now you know a workaround.

Holland won, who predicted that? Look at this chart

When I was looking at the game Netherlands - Italy at the Euro 2008 championship I couldn't believe my eyes. The Netherlands won with 3-0.

Some people are betting on the game in DG Tournament and I wondered how many people bet that score! Instead of looking at all bets in a report I decided to create a chart.


In my presentations at the Oracle conferences (ODTUG, IOUG, OOW, UKOUG, ...) and on our Oracle APEX training days I often say that a picture tells more than 1000 words. So the proof is in this Statistics page again. Tell me, what do you find easier, going through the report or just looking at the chart?

In the chart you see all games and a bar which represent the betting of the game: Wrong, Good (means you knew who won) or Exact (you bet the exact score).
For the match Netherlands-Italy NOBODY predicted that score! It's easier to see it in the bar, there's no green and the red bar is the highest among the other games.

Some people asked me how to create a drill-down chart in APEX. The above example is a drill-down one. You can click on the bar and the Interactive Report and the pie chart will show only the details of that game. What you have to do to get it working is: create an hidden item and in the link of your chart you link the hidden item with the value (primary key). In the query of the "child" chart and report you add that hidden item in the where clause. Whenever you submit the page, the hidden item gets filled and the report filtered.