This discussion is archived
9 Replies Latest reply: May 15, 2013 4:53 AM by emma-apex RSS

Apex 4.2: Dynamic URLs (linking from charts to interactive report)

emma-apex Newbie
Currently Being Moderated
Apex 4.2, DB 11g.

Hello

Is it possible to create the LINK dynamically when coding from a chart to an interactive report?

The code below produces this : select 'f?p=107:40:1467119559713::::IREQ_STATUS:COLUMN_NAME:' link, COLUMN_NAME name, count(activities_id) value from AA_WL_ACTIVITIES3

The problem is that I want to return the contents of COLUMN_NAME and not the text. However, the pie chart correctly labels its segments with the contents of COLUMN_NAME (e.g. Planned, Started, Completed). I assume the problem occurs because IREQ_STATUS:COLUMN_NAME is within quotation marks, but I have fiddled with all sorts of combinations and no joy.

The link correctly takes me to my interactive report and attempts a filter, but of course the column STATUS does not contain the value 'COLUMN_NAME' so it returns an empty dataset.

Any help appreciated
Emma

DECLARE

vSQL VARCHAR2(10000);

BEGIN

IF :P31_DISPLAY is not null AND :P31_COLUMN is not null THEN

vSQL:='select ''f?p=&APP_ID.:40:'||:app_session||'::::IREQ_'||substr(:P31_COLUMN,-(length(:P31_COLUMN)-instr(:P31_COLUMN,'-5-')-2))||':'||substr(:P31_COLUMN,0,instr(:P31_COLUMN,'-1-')-1)||':'' link, ';
vSQL:= vSQL || substr(:P31_COLUMN,0,instr(:P31_COLUMN,'-1-')-1) ||' name, ';
vSQL:= vSQL || substr(:P31_DISPLAY,0,instr(:P31_DISPLAY,'-')-1)||'('||substr(:P31_DISPLAY,-(length(:P31_DISPLAY)-instr(:P31_DISPLAY,'-')))||') value ';
vSQL:= vSQL || 'from '|| substr(:P31_CATEGORY,-(length(:P31_CATEGORY)-instr(:P31_CATEGORY,'-')));
vSQL:= vSQL ||' group by ' || substr(:P31_COLUMN,0,instr(:P31_COLUMN,'-1-')-1);

ELSE

vSQL:='select null link, ''All work activities'' name, count(*) value from aa_wl_activities3';

END IF;

return vSQL;

END;
  • 1. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    emma-apex Newbie
    Currently Being Moderated
    Sorry to bump, but is anyone able to help with this?

    Thanks
    Emma
  • 2. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    VC Guru
    Currently Being Moderated
    This is a basic sql/plslql question and not APEX, you should post them into the relevant forum so that you will get quick response.

    Basically your end query should look something like this
    //concatenate the real column value
    select 'f?p=107:40:1467119559713::::IREQ_STATUS:'||COLUMN_NAME link, COLUMN_NAME name, count(activities_id) value from 
    So you will have to amend your plsql block to produce this query
  • 3. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    emma-apex Newbie
    Currently Being Moderated
    Thanks for your reply. The PLSQL forum keep telling me to post in this forum, so...

    I figure plenty of people here will have done this.

    I've updated my query to show it more clearly - it follows the same (sensible) syntax you are suggesting, but still not working:
    DECLARE
    
    vSQL VARCHAR2(10000);
    vTABLE VARCHAR2(30) := substr(:P32_CATEGORY,-(length(:P32_CATEGORY)-instr(:P32_CATEGORY,'-')));
    vJOIN VARCHAR2(1) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-1-')+3,1);
    vJOINTBL VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-3-')+3,instr(:P32_COLUMN,'-4-')-instr(:P32_COLUMN,'-3-')-3);
    vJOINCOL1 VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-2-')+3,instr(:P32_COLUMN,'-3-')-instr(:P32_COLUMN,'-2-')-3);
    vJOINCOL2 VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-4-')+3,instr(:P32_COLUMN,'-5-')-instr(:P32_COLUMN,'-4-')-3);
    vALIAS VARCHAR2(30) := substr(:P32_COLUMN,-(length(:P32_COLUMN)-instr(:P32_COLUMN,'-5-')-2));
    vPARAM VARCHAR2(30) := substr(:P32_COLUMN,0,instr(:P32_COLUMN,'-1-')-1);
    vVALUE VARCHAR2(30) := substr(:P32_DISPLAY,0,instr(:P32_DISPLAY,'-')-1)||'('||substr(:P32_DISPLAY,-(length(:P32_DISPLAY)-instr(:P32_DISPLAY,'-')))||')';
    
    
    BEGIN
    
    IF :P32_DISPLAY is not null AND :P32_COLUMN is not null THEN
    
    vSQL:='select ''f?p=&APP_ID.:40:'||:app_session||'::::IREQ_'|| vALIAS ||':'|| vPARAM;
    vSQL:= vSQL ||':'' link, ';
    vSQL:= vSQL || vPARAM ||' name, ';
    vSQL:= vSQL || vVALUE ||' value ';
    vSQL:= vSQL || 'from '|| vTABLE;
    
    IF vJOIN ='1' THEN
    
    vSQL:= vSQL ||' join ' || vJOINTBL;
    vSQL:= vSQL ||' on ' || vJOINCOL1;
    vSQL:= vSQL ||' = ' || vJOINCOL2;
    
    END IF;
    
    
    vSQL:= vSQL ||' group by ' || vPARAM;
    vSQL:= vSQL ||' order by ' || vVALUE;
    
    ELSE
    
    vSQL:='select null link, ''All work activities'' name, count(*) value from aa_wl_activities3';
    
    END IF;
    
    
    
    return vSQL;
    
    
    
    
    
    END;
    The filter it links, for example, will be STATUS:COLUMN_NAME (where STATUS is the column ALIAS and COLUMN_NAME is the name of the column rather than the contents (which would be 'Possible', 'Planned' etc).

    So vPARAM is producing COLUMN_NAME within the link, yet it is producing 'Possible', 'Planned' as status items to label the chart. I am confused!

    Can anyone shed any light?

    Thanks
    Emma
  • 4. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    VC Guru
    Currently Being Moderated
    Emma,

    If you debug your page then view debug > you should see the sql query that was returned from this function body > then you can see what is going wrong
    http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/debug_mode.htm#BABJEJGI

    Other option is to setup an example on apex.oracle.com
  • 5. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    emma-apex Newbie
    Currently Being Moderated
    Hi

    I've copied the app so you can take a look. I really would be grateful for any help.

    To see the problem, go to "MI - Charts" and click "Pivot pie chart". Make some selections, e.g. Work + Count rows + Frequency and click "Redraw".

    You should see the chart split its data by Frequency. Now if you click on a segment of the pie, it should take you to page 40 (tick) with a filter for e.g. FREQUENCY: ANNUAL. Instead it tries to filter on FREQUENCY: NAME (where NAME is the column name).

    http://apex.oracle.com/pls/apex/f?p=51663:LOGIN_DESKTOP:9794262359131
    demo/demo

    You can see the code I'm using in previous posts in this thread.

    Can anyone help?

    Thanks
    Emma
  • 6. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    VC Guru
    Currently Being Moderated
    can you share your workspace login details?
  • 7. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    emma-apex Newbie
    Currently Being Moderated
    I'm afraid not. Aspects of the site are confidential and have been hidden. I'm hoping you can build up a full picture, though, by seeing the problem and knowing my code.
  • 8. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    VC Guru
    Currently Being Moderated
    you don't have to share your current workspace instead replicate the issue on demo tables in a demo workspace
  • 9. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
    emma-apex Newbie
    Currently Being Moderated
    I've fixed it actually: 'select ''f?p=&APP_ID.:40:'||:app_session||'::::IREQ_'|| vALIAS ||':''||'|| vPARAM

    Thanks anyway
    Emma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points