This discussion is archived
11 Replies Latest reply: Jul 27, 2008 10:44 PM by Dimitri Gielis RSS

chart drill-down based on series

525126 Newbie
Currently Being Moderated
I have a flash chart with multiple series, which are all retrieved by a single query, due to performance issues. Is there any way to build a drill-down link, which is dependent on series that user clicks. I guess I am looking for a system variable to reveal the series id, user clicks.
  • 1. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hello,

    Which version of APEX are you using?
    Basically I create some hidden items where I store the information for the drilldown of the other chart/report/region.
    Consider this select:
    --
    select 'f?p=&APP_ID.:1:'||:app_session||'::::P1_EMPNO:'||EMPNO||':' link, ENAME label, SAL "Salary"
    from EMP
    --

    There you can see I pass the value of empno in the page item P1_EMPNO.
    If you use this item in your other chart/report it will do a drill down for that employee (put something in your where clause empno = :P1_EMPNO

    Is it that what you're searching for?

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://apex-evangelists.com/
    -- http://apexblogs.info/
  • 2. Re: chart drill-down based on series
    525126 Newbie
    Currently Being Moderated
    Thanks for your advice, Dimitri. I guess I wasn't quite clear, so I would like to put an example of what I am looking for. Let's say I draw a chart upon query:

    SELECT ename,Sum(sal) salary,Sum(comm) commission FROM emp GROUP BY ename

    This chart would show up two series: salary and commission. So I would like to know whether user clicked on salary bar or commission bar?
  • 3. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hello,

    Instead of creating one serie with two values, you can also create two series with 1 value.
    The advantage of doing that is that you can adapt your link and set for ex an item to a value. So you know what you've clicked.

    If it's not clear I'll put an example online.

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://apex-evangelists.com/
    -- http://apexblogs.info/
  • 4. Re: chart drill-down based on series
    643617 Newbie
    Currently Being Moderated
    I'm having the same issue. Can you respond with a simple example of what to pass in the link? The following example of a flash chart on OTN (as well as the other examples I've found) only pass null in the link.

    SELECT NULL link,
    sales_month value,
    revenue "Hardware"
    FROM (
    SELECT TO_CHAR(o.order_date,'Mon YY') sales_month,
    SUM(oi.quantity * oi.unit_price) revenue,
    TO_DATE(to_char(o.order_date,'Mon YY'),'Mon YY') sales_month_order
    FROM OEHR_PRODUCT_INFORMATION p,
    OEHR_ORDER_ITEMS oi,
    OEHR_ORDERS o,
    OEHR_CATEGORIES_TAB ct
    WHERE o.order_date <= (trunc(sysdate,'MON')-1)
    AND o.order_date > (trunc(sysdate-365,'MON'))
    AND o.order_id = oi.order_id
    AND oi.product_id = p.product_id
    AND p.category_id = ct.category_id
    AND ct.category_name like '%hardware%'
    GROUP BY TO_CHAR(o.order_date,'Mon YY')
    ORDER BY sales_month_order
    )

    I can pass page items with no problem but trying to pass data from the result set the chart is based on it proving difficult.

    None of the syntax I've tried seems to pass the content of the "value" field the flash chart is based on to the new page. I've tried any of a number of ways (see below) but none of them pass the value.

    The following passes the literal "value" to page 64
    '/pls/apex/f?p=&APP_ID.:64:&SESSION.::&DEBUG.:RP,64:P64_FROM_DATE,P64_CURR_STS,P64_PARAM:&P63_START_DATE.,2,value'

    The following generates an error...
    '/pls/apex/f?p=&APP_ID.:64:&SESSION.::&DEBUG.:RP,64:P64_FROM_DATE,P64_CURR_STS,P64_PARAM:&P63_START_DATE.,2,:value'

    The following generates an error...
    '/pls/apex/f?p=&APP_ID.:64:&SESSION.::&DEBUG.:RP,64:P64_FROM_DATE,P64_CURR_STS,P64_PARAM:&P63_START_DATE.,2,&value.'

    The following generates an error (really thought this was the answer)
    '/pls/apex/f?p=&APP_ID.:64:&SESSION.::&DEBUG.:RP,64:P64_FROM_DATE,P64_CURR_STS,P64_PARAM:&P63_START_DATE.,2,' || value

    Some targeted help would be most appreciated.

    Regards,
    Mike
  • 5. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hello Mike,

    This is a SELECT I use in one of my trainings:
    select distinct 'f?p=&APP_ID.:33:'||:app_session||'::::P33_DEPTNO,P33_DNAME:'||e.DEPTNO||','||INITCAP(d.DNAME) link, d.DNAME label
         , sum(e.sal) over (partition by e.deptno) salary
      from  dept d, emp e
     where e.deptno = d.deptno
    I created a quick example here: http://examples.apex-evangelists.com/pls/apex/f?p=286:33:0

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://apex-evangelists.com/
    -- http://apexblogs.info/
  • 6. Re: chart drill-down based on series
    643617 Newbie
    Currently Being Moderated
    Excellent help Dimitri. I struggled with this for quite some time because my query source type was "Function Returning SQL Query" which added an extra layer of complexity to the coding.

    I'm incorporating some dropdowns on the page as filters into the display of the graph. Finally have it working thanks to your assistance and some persistance.

    Regards,
    Mike
  • 7. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hi Mike,

    Good you found it. If you're at ODTUG, I'm going into more dept about drill down charts, so feel free to come to my presentation and ask me more questions.

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://apex-evangelists.com/
    -- http://apexblogs.info/
  • 8. Re: chart drill-down based on series
    MuraliS Newbie
    Currently Being Moderated
    Hi,

    With reg to example given in http://examples.apex-evangelists.com/pls/apex/f?p=286:33:0

    What should be the code for the button "Show All departments" in order to show for all the departments

    Pls suggest

    Thanks,
  • 9. Re: chart drill-down based on series
    561094 Newbie
    Currently Being Moderated
    Dimitri,
    thanks for this.

    Is the same thing possible though for 2 line charts. Let's say that it's salary and commission over time (let's say 12 months). Is it possible to have a link on each line that when drilling on it would tell you that you selected sales as opposed to commission ...?

    My query currently is something like :
    select NULL link, mnth as period, salary, commision from ....
    This charts 2 series but I'd like to have a link that is aware which series the user clicked on ...

    Any ideas ?

    TIA, Ken.
  • 10. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hello,

    You would need to adapt the query to see all departments.
    I used: WHERE deptno = nvl(:P1_DEPTNO, deptno)

    Basically it means that if P1_DEPTNO has no value, you show the department (as deptno = deptno)

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://www.apex-evangelists.com/
    -- http://www.apexblogs.info/
  • 11. Re: chart drill-down based on series
    Dimitri Gielis Oracle ACE Director
    Currently Being Moderated
    Hello Ken,

    You would create a link in your select select NULL link, mnth as period, salary, commision from ...

    Now you've NULL there, so earlier an example of a link. You could for ex. create an extra ITEM on your page that you update through the url to say where it comes from.
    For ex. P1_ITEM:S (comes from the salary line), P1_ITEM:C (comes from the commission)
    You would put that in your link in your select.

    Regards,
    Dimitri
    -- http://dgielis.blogspot.com/
    -- http://www.apex-evangelists.com/
    -- http://www.apexblogs.info/