jrXml to PDF - a declarative, pure PL/SQL reporting engine
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 Scheffer).
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.
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.
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 are used to translate a logical object from the report to the implementation of the AS_PDF3-package by Anton Scheffer. 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;
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
The package is currently in a beta-state, i hope to be able to publish a first stable version in maybe a month.