Skip to Main Content

Custom SQL Report - Alternative to APEX built-in reports

Here is how you can create custom reports using PL/SQL region and jQuery. Report is capable of pagination and sorting in the column headings. This solution is an alternative to APEX built-in reports. It allows you control css and javascript more flexible. If you are using APEX 3.x, load jQuery library in page template header or page HTML header. Download and compile package to your application parsing schema. You may need change report styles in package procedure AJAX_REPORT.

Create On Demand process AJAX_REPORT_PPR:

report_util.ajax_report(
      pName     => APEX_APPLICATION.G_x01,
      pStartRow => APEX_APPLICATION.G_x02,
      pMaxRows  => APEX_APPLICATION.G_x03,
      pSortCol  => APEX_APPLICATION.G_x04,
      pSeqId    => APEX_APPLICATION.G_x05
    );
    

Create page process "Before Regions":

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      IF NOT APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => l_name) THEN
        APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => l_name);
        APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => l_name,
          p_clob001 => 'SELECT * FROM EMP', /* Report Query */
          p_c002    => 5,                   /* Max rows to display */
          p_c003    => 'EMPNO',             /* Sort column */
          p_c004    => 'ASC'                /* Sort order */
        );
        APEX_COLLECTION.ADD_MEMBER(
          p_collection_name => l_name,
          p_clob001 => 'SELECT * FROM DEPT',
          p_c003    => 'DEPTNO',
          p_c004    => 'ASC'
        );
      END IF;
      report_util.ajax_report_js;
    END;
    

Create two PL/SQL regions for reports. Region 1 source:

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      report_util.ajax_report(pName => l_name, pSeqId => 1);
    END;
    

Region 2 source:

DECLARE
      l_name VARCHAR2(255);
    BEGIN
      l_name := 'CUSTOM_REPORTS';
      report_util.ajax_report(pName => l_name, pSeqId => 2);
    END;
    

Comments

  • Jari Laine 5 Apr 2015

    Hi koloo,

    You need modify PL/SQL code and generate needed HTML for links.

    Regards,
    Jari

  • Koloo Enock 5 Apr 2015

    Hi,

    Ive created this report how do you add a link to one of the columns?

    regards,

  • omar 27 Mar 2012
    Hi - Thanks anyway, I will try to make my self clear by the following example, if it need a lot of work to do I will try to do it myself of hire someone, but if it needs some hints or guidelines from your side I will be appreciated. ex. 1. Page 1 to search within (emp) table by name. 2. The output is sql report based on your solutions, it will list all records, (brief display) may be empno and ename only, that matches our criteria (i.e 'AL%'). 3. By clicking one of the results(emp record as href), it will go to page 2. 4. In page 2 the detailed record for the clicked emp rec. 5. what I need is to have two buttons (prev and next) to navigate between the results from page 1, since the collection is populated how can I pass from page 1 (startrow, maxrow,...etc) so I can navigate one record by one, what javascript I need to use in page 2. I hope it is clear, need you guidance thankfully. Regards, Omar
  • Jari Laine 26 Mar 2012

    Hi,

    I'm sorry but I do not quite understand what you are trying to achieve. Sounds quite special requirement. Maybe you hire someone to do this.

    Regards, Jari

  • omar 25 Mar 2012
    Hi - Good work, it is something I have looking for for a while, but I need a specific enhancement need you help in this. I have a report with search results (hits) based on SQL query, so I will use your solution to control the pagination and other stuff. If I click on a specific hit so I am in a different page for this result detail.What I need is, while am in the detail of a specific result I need to navigate next and previous results, since the same collection is populated with my results how this could achieved. Need some guidelines. Thanks in advance