Apex 4 Charts: Dynamically adding a series.

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

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

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

• Let me know how it goes.

  C

• that was a light to me – thanks 🙂

• Thanks! This was a big help to me.

  Your solution is simple elegant and well presented.

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

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

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

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


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

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

• 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

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

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

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

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

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

• 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

• 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

• Great. I’m glad you figured it out.

  Thx.

• Kevin, can you create an example on Apex.Oracle. Com? It will be lot easier to trouble shoot it there.

• 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

• very helpful solution.

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

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

Comments are closed.