9 Replies Latest reply: May 15, 2013 6:53 AM by emma-apex RSS

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

    emma-apex
      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
          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
            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
              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
                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
                  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
                    can you share your workspace login details?
                    • 7. Re: Apex 4.2: Dynamic URLs (linking from charts to interactive report)
                      emma-apex
                      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
                        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
                          I've fixed it actually: 'select ''f?p=&APP_ID.:40:'||:app_session||'::::IREQ_'|| vALIAS ||':''||'|| vPARAM

                          Thanks anyway
                          Emma