Apex 4 Charts: Dynamically adding a series.

March 31, 2011 at 8:55 am | Posted in Oracle Developement | 42 Comments

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:


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

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


--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

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.


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;

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!

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;

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:


 &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;

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

&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;

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.

Advertisements

42 Comments »

RSS feed for comments on this post. TrackBack URI

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

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

  3. that was a light to me – thanks 🙂

  4. Thanks! This was a big help to me.

    Your solution is simple elegant and well presented.

  5. 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

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

  6. 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

    • 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

      • 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?

      • 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.

  7. 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..

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

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

  8. 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>

    • Can you include the code for the test_d_qry function?

    • 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;

  9. 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.

  10. 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).

    • 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?

  11. 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).

    • 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).

      • Soumen,
        why don’t we communicate via email, since wordpress seems to mess up the code format.
        Please email me your code to cruepprich@gmail.com

        Thanks
        C

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

  13. wow, this is very good. Thanks for sharing.

  14. 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?

  15. 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?

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

    • 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)

  17. 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

  18. 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

  19. 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)?

    • 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

      • 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

  20. very helpful solution.

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

  22. 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.

    • Arif,
      unfortunately this is not possible with this methodology.

      Christoph

      • 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.

      • 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: