Apex 4 Charts: Dynamically adding a series.

Here is an example of how to dynamically add a series to a Apex 4 chart.

Imagine the following scenario: You have a table with sales data. The table has three columns: SALES_DATE, STATE, SALES_AMOUNT. And lets say you do business only in Texas. So if you wanted to create a simple line (or other) chart on this table to show the sales amount for each day, it is pretty straight forward. You create a chart series with a query that looks something like this:

[sourcecode language=”sql”]

SELECT null, sales_date, sales_amount FROM sales ORDER BY sales_date;

[/sourcecode]

If you were doing business in two states (Texas and Oklahoma), you could simply create two series, one for Texas and on for Oklahoma:

[sourcecode language=”sql”]

–Series 1

SELECT null, sales_date, sales_amount FROM sales WHERE state = ‘TX’ ORDER BY sales_date;

–Series 2

SELECT null, sales_date, sales_amount FROM sales WHERE state = ‘OK’ ORDER BY sales_date

[/sourcecode]

Let’s say your business continues to grow, and you may add more states in the future. Of course you could go into the chart manually and continue to add more series.

But wait! There is a better solution! You can add more series dynamically by only using a single series based on a PL/SQL function.

Before we get into it you should know that you can represent multiple series by using multiple columns in a single query.  So if we pivot our sales table by using the decode function, we can get Texas and Oklahoma represented by two different lines on the chart, and use only a single series in the Apex chart definition.

[sourcecode language=”sql”]

SELECT NULL
,sales_date
,SUM(decode(state, ‘TX’, sales_amount, 0)) "Texas"
,SUM(decode(state, ‘OK’, sales_amount, 0)) "Oklahoma"
FROM sales
GROUP BY sales_date
ORDER BY sales_date;

[/sourcecode]

Now we take it one step further. Since we don’t know which states may be added in the future, we’ll base the series not on a SQL query, but on a Function returning an SQL query.
The function can figure out which states are available and construct a sum(decode…) column for each state. It then returns the query and voila! Dynamically added series!

[sourcecode language=”sql”]
CREATE or REPLACE function sales_data_fn
return varchar2
is
l_qry VARCHAR2(32767);

BEGIN
l_qry := ‘SELECT null, sales_date, ‘;
–Loop through the states and add a sum(decode…) column with column alias
FOR r1 IN (SELECT DISTINCT state FROM sales)
LOOP
l_qry := l_qry || ‘sum(decode(state,”’ || r1.state ||
”’,sales_amount,0)) ‘ || r1.state || ‘,’;
END LOOP;

–Trim off trailing comma
l_qry := rtrim(l_qry, ‘,’);

–Append the rest of the query
l_qry := l_qry || ‘ FROM sales GROUP BY sales_date order by sales_date’;

RETURN l_qry;
END sales_data_fn;

[/sourcecode]

In your series definition in the chart you will now have to reference this function. Rather than basing the series on an SQL Query, base it on a Function Returning SQL Query.

Now you may want to show the series names in the chart labels and/or tool tips. To do this, you need to modify the Custom Chart XML. In the Chart Attributes page choose Yes from the Use Custom XML pulldown menu.

You can now edit the XML for the chart. Find the XML tag <data_plot_settings> -> <bar_series> -> <label_settings> -> <format>. There you edit the [CDATA] tag by replacing the %Name keyword with %SeriesName:

[sourcecode language=”xml”]

&lt;data_plot_settings enable_3d_mode=&quot;true&quot; &gt;
&lt;bar_series style=&quot;Default&quot;&gt;
&lt;tooltip_settings enabled=&quot;true&quot;&gt;
&lt;format&gt;&lt;![CDATA[{%SeriesName}{enabled:False} – {%Value}{numDecimals:0,decimalSeparator:.,thousandsSeparator:\,}]]&gt;&lt;/format&gt;

[/sourcecode]

You can then do the same for the [CDATA] tag in <tooltip_settings> -> <format>:

[sourcecode language=”xml”]
&lt;bar_series style=&quot;Default&quot;&gt;
&lt;tooltip_settings enabled=&quot;true&quot;&gt;
&lt;format&gt;&lt;![CDATA[{%SeriesName}{enabled:False} – {%Value}{numDecimals:0,decimalSeparator:.,thousandsSeparator:\,}]]&gt;&lt;/format&gt;
&lt;font family=&quot;Tahoma&quot; size=&quot;10&quot; color=&quot;0x000000&quot; /&gt;
&lt;position anchor=&quot;Float&quot; valign=&quot;Top&quot; padding=&quot;10&quot; /&gt;
&lt;/tooltip_settings&gt;
[/sourcecode]

The result should look something like this:

This sample is build on the table SALES, which contains the following data:

STATE SALES_AMOUNT SALES_DATE
OK

1000

01-Jan-12

OK

2000

01-Feb-12

OK

1500

01-Mar-12

OK

2500

01-Apr-12

TX

3000

01-Jan-12

TX

3500

01-Feb-12

TX

4000

01-Mar-12

TX

4500

01-Apr-12

If you were to add data for another state, then each column would have a new stack of a different color added.

Let me know if this works for you. I can provide the sample table upon request.

42 thoughts on “Apex 4 Charts: Dynamically adding a series.

  • June 10, 2011 at 10:38 am
    Permalink

    great,
    your solution is very good and i have experiment it and it work very well

    Reply
  • September 6, 2011 at 8:47 am
    Permalink

    That looks great I hope to try this in the next few days

    Reply
  • December 2, 2011 at 6:43 am
    Permalink

    that was a light to me – thanks 🙂

    Reply
  • January 17, 2012 at 6:57 am
    Permalink

    Thanks! This was a big help to me.

    Your solution is simple elegant and well presented.

    Reply
  • February 5, 2012 at 10:55 am
    Permalink

    landed to this page searching for more abt Oracle Decode

    frankly speaking its was little tough for me to understand but really want to understand as scenario you have given is of interest to me , helpful if you can add output/Screenshot somehow

    Reply
    • February 6, 2012 at 9:41 am
      Permalink

      mahender,
      thanks for the comment. I’ll try to get something visual up over the next couple of days.

      Reply
  • July 3, 2012 at 12:20 pm
    Permalink

    I’m pretty new to the APEX game. I want to do this with a stacked bar chart. I was wondering how you integrate this into the series in the chart. I make the above into a function but can’t get it to return into a Chart in my region….how do I make the connection? When I run it in the SQL workshop I get the TEXT back of the query and that I can past into the SEQUENCE box and it works fine…just need it to be truly dynamic.
    thanks
    Rob

    Reply
    • July 3, 2012 at 2:39 pm
      Permalink

      Rob,
      I’ve updated the post with how to reference the function in the Apex charts. Please take another look and let me know if this helps. I’ve also changed the anonymous block to a function.

      C

      Reply
      • July 3, 2012 at 3:08 pm
        Permalink

        Hi Chris and thanks for updating.
        It is still not showing a chart…here is my dilemma…when I run return stacked_repair; in the SQL workshop it throws an error but when I run SELECT stacked_repair from DUAL; and then subsequently run the resulting query it runs fine….any ideas?

        Reply
        • July 3, 2012 at 3:33 pm
          Permalink

          Rob,
          the syntax “return stacked_repair;” does not work in the SQL Workshop. That syntax is meant for the series function when you base a series on a “Function Returning SQL Query”.
          In your Chart Series page, set the Query Source Type to “Function Returning SQL Query”, then add “return stacked_repair;” into SQL textbox below. Apply your changes and run the chart. Let’s see what happens.

          Reply
  • July 3, 2012 at 3:36 pm
    Permalink

    crickets….nothing showing up. but again if I run the DUAL comment and then copy and past the reaulting query into a chat region I get a perfect result..

    Reply
    • July 3, 2012 at 3:38 pm
      Permalink

      Can you send me a copy of the stacked_repair function, the query it returns and some sample data for the underlying table?

      Reply
      • July 3, 2012 at 3:39 pm
        Permalink

        Sure where should I send it I don’t see an email on here.

        Reply
  • October 17, 2012 at 6:12 am
    Permalink

    Nope it is not working for me.
    when I write

    begin
    return TEST_D_QRY;
    end;

    it is Giving following error.

    1 error has occurred

    Failed to parse SQL query!

    Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,’Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon) select * from apex_release;

    VERSION_NO
    ————————————–
    API_COMPATIBILITY
    ————————————–
    PATCH_APPLIED
    ————————————–
    4.0.2.00.09
    2010.05.13

    SQL>

    Reply
    • October 17, 2012 at 9:59 am
      Permalink

      The query you provided has errors in it:


      SELECT NULL
      ,statusname
      ,ageind
      ,decode(statusname, ’scheduled’, COUNT(id), 0) scheduled
      ,decode(statusname, ’executing’, COUNT(id), 0) executing
      ,decode(statusname, ’completed’, COUNT(id), 0) completed

      from ( select a.id
      ,round(sysdate-a.registeredon) TimeSpan
      ,case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)
      select * from apex_release;

      Reply
  • October 18, 2012 at 1:27 am
    Permalink

    Hi Cristopher,

    I think this is a copy paste error happened during I was posting it on you site.

    Don’t include Select * from Apex release,,, That I posted to let you know the Apex version. Otherwise this query returns row in proper fashion…

    Regards,
    Soumen.

    Reply
  • October 18, 2012 at 1:32 am
    Permalink

    the actual error message it comes as

    1 error has occurred

    Failed to parse SQL query!

    Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,’Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)<30 then 'Month' else 'More…' end ageind, a.status_id,b.statusname from a02_task a,a02_status b where b.id=a.status_id) group by statusname,AgeInd;

    Certain queries can only be executed when running your application, if your query appears syntactically correct, you can save your query without validation (see options below query source).

    Reply
    • October 18, 2012 at 10:33 am
      Permalink

      The query still has errors. See the rounding functions in the CASE clause:
      SELECT NULL
      ,statusname
      ,ageind
      ,decode(statusname, ’scheduled’, COUNT(id), 0) scheduled
      ,decode(statusname, ’executing’, COUNT(id), 0) executing
      ,decode(statusname, ’completed’, COUNT(id), 0) completed
      FROM (SELECT a.id
      ,round(SYSDATE - a.registeredon) timespan
      ,CASE
      WHEN round(SYSDATE - a.registeredon)
      7 AND round(SYSDATE - a.registeredon)
      14 AND round(SYSDATE - a.registeredon) < 30 THEN
      'Month'
      ELSE
      'More…'
      END ageind
      ,a.status_id
      ,b.statusname
      FROM a02_task a
      ,a02_status b
      WHERE b.id = a.status_id)
      GROUP BY statusname

      Can you please provide the entire function?

      Reply
  • October 19, 2012 at 12:34 am
    Permalink

    Fine I am rewriting the query, it seems copy pasting is creating problem for me…

    CREATE OR REPLACE function PROD.”TEST_D_QRY”
    return VARCHAR2
    is

    msql VARCHAR2(32767);
    Begin
    msql:=’Select statusname,AgeInd, ‘||chr(10);
    For m_stat in (select statusname from a02_status)
    loop
    msql:= msql || q'[decode(statusname,’]’;
    msql:= msql || m_stat.statusname;
    msql:= msql || q'[‘,count(id),0) ]’;
    msql:= msql || m_stat.statusname|| ‘,’;
    –msql:= msql || ‘count(decode(statusname,’||m_stat.statusname||’,id,0))’||’ ‘|| m_stat.statusname|| ‘,’;
    end loop;
    –dbms_output.put_line(msql);
    msql:=rtrim(msql,’,’);
    –dbms_output.put_line(msql);
    msql:= msql||’ from’||chr(10);
    msql:= msql||'(‘||chr(10);
    msql:= msql||’select a.id, round(sysdate-a.registeredon) TimeSpan,’||chr(10);
    msql:= msql||’case ‘||chr(10);
    msql:= msql||q'[when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)> select test_d_qry from dual;

    shows following results

    Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,’Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon) Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,
    ‘Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)

    But the eror message I am getting in APEX is as follows

    1 error has occurred

    Failed to parse SQL query!

    Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,’Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)<30 then 'Month' else 'More…' end ageind, a.status_id,b.statusname from a02_task a,a02_status b where b.id=a.status_id) group by statusname,AgeInd;
    Certain queries can only be executed when running your application, if your query appears syntactically correct, you can save your query without validation (see options below query source).

    Reply
    • October 19, 2012 at 12:51 am
      Permalink

      Fine I am rewriting the query, it seems copy pasting is creating problem for me…

      CREATE OR REPLACE function PROD.”TEST_D_QRY”
      return VARCHAR2
      is

      msql VARCHAR2(32767);
      Begin
      msql:=’Select statusname,AgeInd, ‘||chr(10);
      For m_stat in (select statusname from a02_status)
      loop
      msql:= msql || q'[decode(statusname,’]’;
      msql:= msql || m_stat.statusname;
      msql:= msql || q'[‘,count(id),0) ]’;
      msql:= msql || m_stat.statusname|| ‘,’;
      –msql:= msql || ‘count(decode(statusname,’||m_stat.statusname||’,id,0))’||’ ‘|| m_stat.statusname|| ‘,’;
      end loop;
      –dbms_output.put_line(msql);
      msql:=rtrim(msql,’,’);
      –dbms_output.put_line(msql);
      msql:= msql||’ from’||chr(10);
      msql:= msql||'(‘||chr(10);
      msql:= msql||’select a.id, round(sysdate-a.registeredon) TimeSpan,’||chr(10);
      msql:= msql||’case ‘||chr(10);
      msql:= msql||q'[when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)> select test_d_qry from dual;

      shows following results

      Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,’Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon) Select null,statusname,AgeInd, decode(statusname,’Scheduled’,count(id),0) Scheduled,decode(statusname,’Executing’,count(id),0) Executing,decode(statusname,
      ‘Completed’,count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)<30 then
      'Month' else 'More…' end ageind, a.status_id,b.statusname from a02_task a,a02_status b where b.id=a.status_id) group by statusname,AgeInd;

      N STATUSNAME AGEIND SCHEDULED EXECUTING COMPLETED
      – ——————– ——— ———- ———- ———-
      Completed More… 0 0 4
      Completed Week 0 0 1
      Scheduled Week 1 0 0
      Completed Month 0 0 3
      Executing More… 0 1 0
      Executing Month 0 6 0
      Scheduled More… 1 0 0

      7 rows selected.
      =========================================================

      But the eror message I am getting in APEX is as follows
      =========================================================
      1 error has occurred

      Failed to parse SQL query!

      Select null,statusname,AgeInd, decode(statusname,'Scheduled',count(id),0) Scheduled,decode(statusname,'Executing',count(id),0) Executing,decode(statusname,'Completed',count(id),0) Completed from ( select a.id, round(sysdate-a.registeredon) TimeSpan, case when round(sysdate-a.registeredon)7 and round(sysdate-a.registeredon)14 and round(sysdate-a.registeredon)<30 then 'Month' else 'More…' end ageind, a.status_id,b.statusname from a02_task a,a02_status b where b.id=a.status_id) group by statusname,AgeInd;
      Certain queries can only be executed when running your application, if your query appears syntactically correct, you can save your query without validation (see options below query source).

      Reply
  • November 12, 2012 at 2:08 pm
    Permalink

    Nice! I needed to do something very similar to this. This solution was very helpful.

    Reply
  • November 14, 2012 at 3:16 am
    Permalink

    wow, this is very good. Thanks for sharing.

    Reply
  • May 3, 2013 at 10:47 am
    Permalink

    I am trying to achieve something similar and your article greatly helped me however instead sum I want count and as a result decode is returning three sums of three counts. Instead 5,3,1 I have 9,9,9. Any ideas?

    Reply
  • August 12, 2013 at 10:29 pm
    Permalink

    I have implemented this solution successfully, awesome btw, but I need to include links specific to the series and group clicked. In other words, using your example, the date and the state would need to be based in the link. But since the generic chart link is per row (ie date in your example) how would/could it dynamically identifyt he state series you clicked on. I am not sure that is possible.. I get that a link can be dynamically built, but it would have to reference the series, ie column clicked on. Is it possible?

    Reply
  • August 15, 2013 at 4:13 am
    Permalink

    tks. Works great.
    How do I send parameters to the function?

    Reply
    • August 15, 2013 at 10:28 am
      Permalink

      You include parameters just like any other function. For example in the Series Query you could enter:
      return (sales_data_fn(p_month => : P2_MONTH));

      In the function definition you then would include the parameter as well:
      CREATE or REPLACE function sales_data_fn (p_month IN VARCHAR2)

      Reply
  • November 30, 2013 at 3:05 pm
    Permalink

    Hi Chris,
    I tried adapting the function and applying it to my application, however, the function will not compile. Without passing any arguments, how does it return anything? This is on Apex 4.2.3 and Oracle 11g R2. I have tried creating the function directly in apex and using SQL Developer, both are a no go. I put the code in a procedure and used DBMS_OUTPUT.PUT_LINE to test. It is creating the statement. I then tried passing an argument to test. The function will compile, however, when you specify a parameter in apex it craps out. A sample of the code I tried is below. What am I missing?

    For reference, “CRAFT” would be the equivalent of “STATE”, and then of course NEXTDATE is self explanatory.

    create or replace
    function REPORT1_F

    V_COL VARCHAR2(4000);

    BEGIN

    V_COL := ‘SELECT NULL, NEXTDATE, ‘;

    FOR r1 IN (SELECT DISTINCT(CRAFT) FROM PM_PROJECTION ORDER BY CRAFT)
    LOOP
    V_COL := V_COL || ‘sum(decode(CRAFT,”’ ||R_1.CRAFT||
    ”’,NEXTDATE,0))’ || REPLACE(R_1.CRAFT, ‘-‘,’_’)||’,’;

    END LOOP;

    –TRIM TRAILING ‘,’
    V_COL := RTRIM(V_COL, ‘,’);

    V_COL := V_COL ||’ FROM PM_PROJECTION GROUP BY NEXTDATE ORDER BY NEXTDATE’;

    RETURN V_COL;

    END;

    Thanks for any help in advance!

    Kevin

    Reply
  • November 30, 2013 at 3:53 pm
    Permalink

    Hi Chris! Never mind, I figured it out. Two mistakes, first, was returning “NEXTDATE” in the series label and needed to return TOTLABHRS column. I was doing this in the procedure using DBMS_OUTPUT, so I didn’t notice it in the function. Then, to get the function to compile I put the “RETURN VARCHAR2 bit in. Didn’t realize I could do that, I’ve never had to write one without passing arguments. Any way, code is below in case there are any other newbies out there.

    create or replace
    function “TEST”
    return VARCHAR2
    is
    V_COL VARCHAR2(4000);
    begin
    V_COL := ‘SELECT NULL, NEXTDATE, ‘;

    FOR R_1 IN (SELECT DISTINCT(CRAFT)CRAFT FROM PM_PROJECTION ORDER BY CRAFT)
    LOOP
    V_COL := V_COL || ‘sum(decode(CRAFT,”’ ||R_1.CRAFT||
    ”’,TOTLABHRS,0))’ || REPLACE(R_1.CRAFT, ‘-‘,’_’)||’,’;

    END LOOP;

    –TRIM TRAILING ‘,’
    V_COL := RTRIM(V_COL, ‘,’);

    V_COL := V_COL ||’ FROM PM_PROJECTION GROUP BY NEXTDATE ORDER BY NEXTDATE’;

    RETURN V_COL;
    end;

    Thanks again!

    Kevin

    Reply
  • February 7, 2014 at 6:13 am
    Permalink

    Hey Chris – Thanks for this useful post. However if we dynamically construct the series and if I want to securitize my series based on a condition how would I acheive it – For e.g If we visualize the same thing on just 3D Column Bar chart rather than stacked and if I want to only display Texas bar if user has access for Texas sales data (This is stored in a security table for the apex user)?

    Reply
    • February 7, 2014 at 9:07 am
      Permalink

      Hi Shas,
      thanks for the comment. Depending on the overall criteria, you could include some logic to figure out which states the user has access to, construct a where clause, and append it to the query.

      --inside the function
      --Logic to find state the user is limited to
      SELECT state INTO l_state FROM your_users_table WHERE username = v('APP_USER');

      --Append where clause to query
      l_qry := l_qry || ' WHERE state = ' || l_state;
      l_qry := l_qry || ' ORDER BY sales_date';

      Let me know if this helps.

      Christoph

      Reply
      • February 7, 2014 at 1:05 pm
        Permalink

        Thanks Chris – Need to give it a bit more thought as how best to implement this as I am looking at Oracle e-Business security for the APEX report application (So APEX report application would be operating on ebs data – However I have integrated EBS authentication to it) – So next is how to securitize the operating unit to the user in the dashboards I make – Will have to give it a thought in a generic way as framework for eBS

        Reply
  • October 17, 2014 at 9:08 am
    Permalink

    Is it possible to pass a variable to a function?
    sales_data_fn(:my_item) do not work.

    Reply
  • June 4, 2015 at 12:58 pm
    Permalink

    Dear Chris,
    please tell me that can i pass a parameter upon click a mouse on particular stack in this stack bar chart to another report or chart.

    Reply
      • June 5, 2015 at 7:45 am
        Permalink

        OK, THANKS FOR YOUR REPLY, SO, CAN YOU GUIDE ME THE RIGHT METHOD TO FULFILL MY REQUIREMENT. AND ALSO GIVE ME SOME EXAMPLES OF A QUERY WHICH CAN RETURN MULTIPLE SERIES FOR STACKED CHART.

        Reply
        • June 5, 2015 at 8:06 am
          Permalink

          I personally haven’t explored this. You can always generate the chart XML with PL/SQL and use a substitution variable to reference the XML in the chart settings. You could also take a look at 3rd party charts like Flot or Google Charts. They will require some coding, but may be a little more flexible.

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *