Generic Charting in Application Express

An interesting post popped up on the Application Express forums where a user wanted to dynamically change the query that a chart used.

The comment that caught my eye was –

How about if I want to change the table and where clause as well.

Whilst it’s simple to dynamically change the where clause, it’s not so straightforward to change the table that you’re querying dynamically, since the chart relies on you specifying the query at designtime.

Fortunately, we know that the chart needs to have certain outputs from the query, for example –

SELECT 
  null as link, ename as label,  sal as value
FROM   
  scott.emp
WHERE  
  deptno = :P101_DEPTNO

So we need a method of returning a link column, a label column and a value column dynamically for each row of data.
Fortunately Oracle has the concept of Pipelined functions which should help us to achieve our aim.

First we need to create a type which will be used as the return type of our pipelined function, i.e. this type will represent a single point on our chart

create or replace type ty_chart_entry as object (
  link varchar2(60),
label varchar2(60),
value number )

Create another type which is a collection of the ty_chart_entry type, i.e. this will effectively hold the table representing all the data points on the chart.

create or replace type tbl_chart_entry as table of ty_chart_entry

Now we need to create the function itself

create or replace package chart_pkg
as
    function generate(p_type in varchar2) return tbl_chart_entry PIPELINED;
end;
create or replace package body chart_pkg as
  function generate(p_type in varchar2) return tbl_chart_entry PIPELINED is
  begin
    if (p_type = 'E') then
      for rec in (select ename, sal from emp) loop
        pipe row (ty_chart_entry(null, rec.ename, rec.sal));
      end loop;
    end if;
    if (p_type = 'D') then
      for rec in (select d.dname as name,
                  (select sum(e.sal) from emp e where e.deptno = d.deptno) as sal
                  from dept d) loop
        pipe row (ty_chart_entry(null, rec.name, rec.sal));
      end loop;
    end if;
    return;
  end;
end;

Essentially this function performs two entirely different queries depending on whether we pass in a ‘D’ (to query the DEPT table) or an ‘E’ (to query the EMP table).

SQL> select count(*) from table(chart_pkg.generate(‘E’));

COUNT(*)
———-
14

SQL> select count(*) from table(chart_pkg.generate(‘D’));

COUNT(*)
———-
4

So we can now create a region on a page with a select list (with submit) that returns ‘E’ or ‘D’ and use this value in a chart region.

Chart.png

Which nows gives us a completely dynamic chart on the EMP and DEPT tables

Emp.png

Dept.png

PIPELINED functions are pretty cool, and very very underused. Note though that I’m not sure that I would advocate heavy usage of them like this since I’m sure there’s probably more optimal ways of doing this, however they’re extremely flexible and worth checking out if you hit a similar sort of problem.

10 thoughts on “Generic Charting in Application Express

  1. Mark

    Jes, I’ve got the chart package built and working. But I’m unclear on how I should use it to generate the chart. Should I call the “generate” function from within a series in my chart, or did you use another method to load the generated table into the chart?

    Thanks!
    Mark

    Like

    Reply
  2. jes

    Hi Mark,

    If you look at the screenshots (you may need to choose one of the larger sizes on Flickr), you should be able to see that in my chart query I’m actually performing a select on my function itself, e.g. “select link, label, value from TABLE(chart_pkg.generate(:P1_VALUE))’

    You will undoutbedly need to modify this example to suit your own purposes, but it should give you a few ideas on how to achieve what you want to do.

    John.

    Like

    Reply
  3. Mark

    I’m sorry to have bothered you John, my client’s firewall blocked the 3 screenshot png’s, so I hadn’t seen your SELECT… screen. Logging on using my phone as a modem, it’s all clear as day.

    Thanks, not only for not only your great solution, but your patience!

    Like

    Reply
  4. Michael A. Rife

    Just a note that some of the text in the CREATE statements in your blog gets scrolled off to the right. So for your blog readers out there, make sure you scroll all the way to the right.

    Mike

    Like

    Reply
  5. MAnish

    Hi John,
    Is there any way of passing the values from report filters dynamically to charts so that the charts can be built dynamically in apex.We tried with intereactive reports there it seem spossible automatically but the problem is we cant built 3d charts in there.
    Could you pls suggest any idea.

    Thanks
    Manish

    Like

    Reply
  6. Senthil Kumaran

    In chart, the graph is appearing based on the data from the database. I need to build a chart where i have supply data for chart and it should not go to database, in the client itself it should get refresh. Is there any method?

    Like

    Reply

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