Single Article

Custom SQL Report - Alternative to APEX built-in reports

Category APEX Tricks

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 gives: direct css control, direct javascript control.

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;

 

See working example.

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     

Global Right Column

Search

Archives

Subscribe in a Reader