The task

I'm working on APEX since half a year now and i really like it. I also think its quite "complete" regarding its functionality, besides... well, besides the reporting possibilities.

There are several available solutions, but either they require additional "middleware" (as BI Publisher or Apache FOP), or they are database driven, but not declarative (like PL/PDF or AS_PDF3 by Anton Scheffler).

I don't want that "middleware"-overhead, so i somehow have to deal with the database-driven pdf-generation as a starting point. But what i want is a graphical editor to define my report and then run and render it inside the database.

And there are graphical editors which are very powerful and easy to use, my favorite one is iReport for JasperReports.

The approach

So my approach is quite easy. I want to design my report in a graphical way, then transfer the resulting report-definition to the database, and then render the report using a PL/SQL-package to PDF.And of course i do not want to reinvent the wheel but use already existing code, if possible.

 

• There already is a tool i would like to use to design my report, that's iReport.

• There already is a tool which is capable of creating PDF-files from inside the database, the one i choose here is AS_PDF3 from Anton Scheffler ( i like it for its simplicity).

 

There is only one part missing, that's the "thing in the middle" which is capable of reading my report-definition and translate the single pieces to statement-call's to the procedures inside the AS_PDF3-package.

The good news is that reading the report-definition is quite simple, as iReport stores the definition as xml-file.

 

Here's a simple Report-definition which just renders a thin blue line.

 

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="simple" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <queryString language="SQL">
        <![CDATA[SELECt 1 X FROM DUAL]]>
    </queryString>
    <field name="X" class="java.math.BigDecimal"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <line>
                <reportElement x="202" y="38" width="100" height="1" forecolor="#0000CC"/>
            </line>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>

 

The result looks as simple.

 

 

 

A "real" report in iReport basically exists of

• a title-region rendered once for the report

• a pageheader-region rendered ony for each page at the top of the page

• a columnheader-region rendered once on each page before the data

• a detail-region rendered once for each record of data

• a columnheader-region rendered once on each page after the data

• a pagefooter-region rendered ony for each page at the bottom of the page

• a summary-region once for the report

each of this region maybe there or maybe not.

 

Each region consists of graphical objects, like

• lines with different colors and line-widths

• rectangles with different colors and line-widths

• static text, with or without border

• textfields, which take the data from specific fields of a record from the report-query and show them

• images, either static or taken from the database (a blob-column)
• subreports, which can be used to implement master-detail-reports

 

There is more, but for a first version of my package this is enough to implement.

 

The implementation

My package consists of three parts

• the logic for recursively reading the xml-structure of the report-definition.

• the rendering part which works out the logic of the different objects of the report-definition and calls the render-procedures for the different objects.

• the render-methods, which "translate" the logical object (e.g. a line) to its physical implementation.

 

The reading logic

 

I created a report-table with an CLOB-column in which the reports xml is stored. I concentrated on the, in my opinion, are the most important objects and attributes of the report, details will be added later. I won't show the complete code, as an example here's the code of the procedure reading the main report-definition.

 

  FUNCTION FK_LOAD_REPORT (i_vcReportName IN VARCHAR2)
  RETURN tReport IS
    CURSOR crReport IS
      SELECT EXTRACTVALUE(XML, '/jasperReport/@pageWidth'   ) PAGE_WIDTH,
             EXTRACTVALUE(XML, '/jasperReport/@pageHeight'  ) PAGE_HEIGHT,
             EXTRACTVALUE(XML, '/jasperReport/@leftMargin'  ) LEFT_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@rightMargin' ) RIGHT_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@topMargin'   ) TOP_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@bottomMargin') BOTTOM_MARGIN,
             EXTRACT     (XML, '/jasperReport/title'        ) TITLE_XML,
             EXTRACT     (XML, '/jasperReport/pageHeader'   ) PAGEHEADER_XML,
             EXTRACT     (XML, '/jasperReport/columnHeader' ) COLUMNHEADER_XML,
             EXTRACT     (XML, '/jasperReport/detail'       ) DETAIL_XML,
             EXTRACT     (XML, '/jasperReport/columnFooter' ) COLUMNFOOTER_XML,
             EXTRACT     (XML, '/jasperReport/pageFooter'   ) PAGEFOOTER_XML,
             EXTRACT     (XML, '/jasperReport/summary'      ) SUMMARY_XML,
             EXTRACTVALUE(XML, '/jasperReport/queryString'  ) QUERY_STRING
        FROM (SELECT XMLTYPE(REPORT_XML) XML
                FROM REPORTS
               WHERE REPORT_NAME =i_vcReportName
             );
    recReport crReport%ROWTYPE;
    rReport tReport;
  BEGIN
    OPEN crReport;
    FETCh crReport INTO recReport;
    IF crReport%FOUND THEN
      rReport.nPageWidth     :=recReport.PAGE_WIDTH;
      rReport.nPageHeight    :=recReport.PAGE_HEIGHT;
      rReport.nLeftMargin    :=recReport.LEFT_MARGIN;
      rReport.nRightMargin   :=recReport.RIGHT_MARGIN;
      rReport.nTopMargin     :=recReport.TOP_MARGIN;
      rReport.nBottomMargin  :=recReport.BOTTOM_MARGIN;
      rReport.xmlTitle       :=recReport.TITLE_XML;
      rReport.xmlPageHeader  :=recReport.PAGEHEADER_XML;
      rReport.xmlColumnHeader:=recReport.COLUMNHEADER_XML;
      rReport.xmlDetail      :=recReport.DETAIL_XML;
      rReport.xmlColumnFooter:=recReport.COLUMNFOOTER_XML;
      rReport.xmlPageFooter  :=recReport.PAGEFOOTER_XML;
      rReport.xmlSummary     :=recReport.SUMMARY_XML;
      rReport.vcQuery        :=recReport.QUERY_STRING;
    END IF;
    CLOSE crReport;
    RETURN rReport;
  END;

For other objects the code looks similar. So its basically translating xml to "flat" record-structures.

 

The rendering part

 

This is the most complex part, as i has to deal with all the rules which apply to a report, based on its structure and attributes. As an example here is the main-procedure (shortened)

 

  PROCEDURE PR_RENDER_REPORT(i_rReport IN tReport) IS

    ..

  BEGIN
    -- Execute the Query and check if there are results
    lResult:=FK_EXECUTE_QUERY(i_rReport.vcQuery, i_rReport.lParams);
   
    IF lResult.COUNT>0 THEn
      -- There are records, start
      PR_INIT_PDF;
   
      PR_SETUP_PAGE(i_nWidth       =>i_rReport.nPageWidth,
                    i_nHeight      =>i_rReport.nPageHeight,
                    i_nLeftMargin  =>i_rReport.nLeftMargin,
                    i_nRightMargin =>i_rReport.nRightMargin,
                    i_nTopMargin   =>i_rReport.nTopMargin,
                    i_nBottomMargin=>i_rReport.nBottomMargin
                   );

      rArea:=FK_RENDER_REGION(i_rReport.xmlTitle, 'title', rArea, lResult, NO_RECORD, TRUE, FALSE, FALSE);
      rArea:=FK_RENDER_REGION(i_rReport.xmlPageHeader, 'pageHeader', rArea, lResult,NO_RECORD, FALSE, FALSE, FALSE);
      rArea:=FK_RENDER_REGION(i_rReport.xmlColumnHeader, 'columnHeader', rArea, lResult, NO_RECORD, TRUE, nPageHeaderheight>0, nPageFooterheight>0);

      FOR i IN 1..lResult.COUNT LOOP
        -- Check if region fits on page
        IF FK_FITS_IN_PAGE(rArea.nY, nDetailHeight, nColumnFooterHeight, nPageFooterheight) THEN
          rArea:=FK_RENDER_REGION(i_rReport.xmlDetail, 'detail', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
        ELSE
          -- Finish page
          rArea:=FK_RENDER_REGION(i_rReport.xmlColumnFooter, 'columnFooter', rArea, lResult, i,TRUE, nPageHeaderheight>0, nPageFooterheight>0);
          -- psoition to bottom for page-footer
          rArea:=FK_RENDER_REGION(i_rReport.xmlPageFooter, 'pageFooter', rArea, lResult, i, FALSE, FALSE, FALSE);
          PR_NEW_PAGE;
          rArea:=FK_RENDER_REGION(i_rReport.xmlPageHeader, 'pageHeader', rArea, lResult,i, FALSE, FALSE, FALSE);
          rArea:=FK_RENDER_REGION(i_rReport.xmlColumnHeader, 'columnHeader', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
          rArea:=FK_RENDER_REGION(i_rReport.xmlDetail, 'detail', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
        END IF; 
      END LOOP;
      rArea:=FK_RENDER_REGION(i_rReport.xmlColumnFooter, 'columnFooter', rArea, lResult, NO_RECORD, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
      rArea:=FK_RENDER_REGION(i_rReport.xmlSummary, 'summary', rArea, lResult, NO_RECORD, TRUE, FALSE, FALSE);

      -- psoition to bottom for page-footer
      rArea.nY:=rPageSetup.nPageHeight-rPageSetup.nBottomMargin-rPageSetup.nTopMargin-nPageFooterheight;
      rArea:=FK_RENDER_REGION(i_rReport.xmlPageFooter, 'pageFooter', rArea, lResult, NO_RECORD, FALSE, FALSE, FALSE);
      PR_FINISH_PDF;
    END IF;
  END;

The render-methods

 

The render-methods are used to translate a logical object from the report to the implementation of the AS_PDF3-package by Anton Scheffler. Also, here is an example-procedure, this one's for rendering a line.

 

  PROCEDURE PR_RENDER_LINE(i_nX          IN NUMBER,
                           i_nY          IN NUMBER,
                           i_nWidth      IN NUMBER,
                           i_nHeight     IN NUMBER,
                           i_nLineWidth  IN NUMBER,
                           i_vcLineColor IN VARCHAR2
                          ) IS
  BEGIN
    IF i_nWidth=1 THEN
      -- vertical line
      AS_PDF3.vertical_line(p_x          =>rPageSetup.nLeftMargin+i_nX,
                                p_y          =>rPageSetup.nPageHeight-i_nY-rPageSetup.nTopMargin,
                                p_height     =>-i_nHeight,
                                p_line_width =>i_nLineWidth,
                                p_line_color =>REPLACE(i_vcLineColor, '#', '')
                                );
    ELSIF i_nHeight=1 THEN
      -- horizontal line
      AS_PDF3.horizontal_line(p_x          =>rPageSetup.nLeftMargin+i_nX,
                                  p_y          =>rPageSetup.nPageHeight-rPageSetup.nTopMargin-i_nY,
                                  p_width      =>i_nWidth,
                                  p_line_width =>i_nLineWidth,
                                  p_line_color =>REPLACE(i_vcLineColor, '#', '')
                                );
    END IF;
  END;

 

Some examples

The following are some examples which can be generated with the current version of my package

 

Tabular report on DEMO_PRODUCT_INFO with images

tabular report on EMP built with the wizard using one of iReports predefined template (cherry).

Master-detail-report on DEPT and EMP

 

Whats next

 

The package is currently in a beta-state, i hope to be able to publish a first stable version in maybe a month.