Sunday, February 27, 2011

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)

10 comments:

  1. Hilary Farrell3/02/2011 6:29 PM

    Hi Dimitri,

    Thank you for bringing this issue to our attention. As another workaround, in your testcase, if you switch the sequence of your initial two queries, then you should achieve the expected result. The fix for this issue will be contained in our next release.

    Regards,
    Hilary

    ReplyDelete
  2. Hi Dimitri, on last OOW I lucky to be on your last presentation and receive from you a copy of the Expert Oracle Apex book. My question here is about when you need a column as a percentage, for example, in your chart, if the last series is the percentage of the salary among the total salaries. I've manage to include the series but even with the secondary y axis, it gets without proportion, as the maximum value of the last series is 100 and the minimum of the others begins as 1000. Any ideas on solving that? Thanks!

    ReplyDelete
  3. Hi Ygor,

    You can use multiple Y-Axis.
    So the % goes on one, the other values on another Y-axe.

    Hope that helps,
    Dimitri

    ReplyDelete
  4. Hi Dimitri,

    How can we achieve the following:

    Amount on Y-Axis for 2013 year and 2014 month-wise on X-Axis and now need another 2 Y-Axis for same X-Axis Values. Is it possible to achieve by using multiple Y-Axis ?

    ReplyDelete
  5. @Bharat: if you look at the AnyChart gallery (http://www.anychart.com/products/anychart/gallery) and see if you find a chart that is clause to what you want, you can do it in APEX too.
    But sometimes you need maybe two charts, to take out the complexity of multiple Y-axes?

    ReplyDelete
  6. Hi Dimitri,

    I came across the chart report in the following location

    http://www.anychart.com/products/anychart/gallery/samples/Pareto-Chart-of-Late-Arrivals-by-Reported-Cause.html

    and this is having 2 Y-Axis's and 1 value is based on 1st Y-Axis and another value is based on 2nd Y-Axis. Now I want to place 2 more (1 Bar and 1 Line) and that another Bar should be based on 1st Y-Axis and another Line should be based on 2nd Y-Axis.

    Is it possible to achieve this in a single chart?

    Regards,
    Bharat

    ReplyDelete
  7. Hi Bharat,

    It is possible yes, but you would need to generate the data part yourself to define which series is on which y-axis.



    Hope that helps,
    Dimitri

    ReplyDelete
  8. Hi Dimitri,

    How can we generate data part to define which series is on which Y-Axodid..

    Regards,Bharat

    ReplyDelete
  9. Hi Bharat,

    I have same requirement as yours in Oracle Apex 5 chart.
    Were you able to plot line series in extra y axis.

    ReplyDelete
  10. Hi Bharat and Shilpa,

    I covered that in the book "Expert Oracle APEX".

    You have a couple of options; a process that fills an item and in the custom XML you reference the item &ITEM.
    If the XML is over 32K you have to do it a bit differently - but see the Charts chapter in the book it walks you through it.

    Dimitri

    ReplyDelete