An Overview

JApex includes what can be considered as a mini reporting server that supports nice features like transparent downloading, custom exporting format and generic sub reporting (in progress).

The concept is to store Compiled Report Templates (see below) in a table in the database, and dynamically generate output documents in desired formats by generically parsing a URL into some special parameters and Report parameters and use such parameters together to generate and serve reports

Why store report templates in database?

At first, especially if you used JasperReports before, the design decision to store compiled report templates it the database seems like a bit of an extreme choice. why not simply use file systems.

The reasons behind such decision are many, here is a few of them:

• If you're building a data-centric application that uses a database, you're certainly have access to "a database". so there is no addedd dependency to your project.
• Storing reports in the database has the added benefit of being remotely adminstratable by your normal applications front end. you can even use Blober to build your own management solution for your reports.
• Database storage adds several features not readily found in simple file based approaches,e.g.
  • Simple Metadata management, examples include report title, parent report ( for sub reports ), report description, optional report thumbnail etc.
  • Integrated report backup with the normal database back up procedures, no extra steps are required.
  • To avoid file system constraints, especially when it comes to fine grained access rights over report template management and administration.
  • Databases not only store but can enrich your storage capabilities, for instance you can very simply extend the proposed database model to support versioning, history, multi-versioning of the same report, restoring older reports etc.
  • The overhead involved in storing reports in the database is minimal
    • Storage wise, report template sizes are relatively small. and reports are typically countable.
    • Storing reports is a very infrequent process especially in porduction environments.
    • Loading reports at "report filling time" is commonly cached by the database, or can be added as a simple cache layer into the logic of JApex itself.

Introducing JasperReports

JasperReports is a very capable reporting library, however, it is a headless library. i.e. by itself it has no UI to design, preview and generate report templates.

JasperReports uses the term Template to describe a report design.

A report design is visual depiction of the final report layout, without the actual data of the report.

JasperReport report designs are XML based, commonly having the extensions JRXML (Jasper Reports XML).

Of course JasperReports are conveniently designed using a fully featured visual tool called iReport

Once reports are designed, they are passed through a phase of "compilation" so that they ready to process arbitrary data.

Sample Report Template Storge table structure

In the following sample SQL DDL script an assumption that the table objects are created in a schema named "JAPEX". please note that this assumption is purely for demonstration purpose, as you will see shortly, not only the schema name, table name, and table structure can be customized to your liking, but also the entire logic of how report templates are stored is shielded behind a single configuration parameter of JApex itself.

Also please note several attributes may not match your tooling like statement seperator etc. please feel free to follow your usual procedure when it comes to running the following scripts

• The report storage table

  CREATE TABLE JAPEX.JAPEX_REPORT (
              REPORT_SN                   NUMBER NOT NULL,
              REPORT_ID                                   VARCHAR2(250) NOT NULL,
              PARENT_REPORT_ID                    VARCHAR2(250) NULL,
              DESCRIPTION                 VARCHAR2(500) NOT NULL,
              TEMPLATE_JRXML                              BLOB NULL,
              TEMPLATE_JASPER                             BLOB NULL,
              THUMBNAIL                   BLOB NULL,
              CREATED_ON                  TIMESTAMP(6) NULL,
              CREATED_BY                  VARCHAR2(32) NULL,
              UPDATED_ON                  TIMESTAMP(6) NULL,
              UPDATED_BY                  VARCHAR2(32) NULL
              )
  GO

  As you can see a given report is identified by its textual ID (required) while the name of the field can be any thing as you will see later, the choice of the domain for the report id is closely related to both; how JApex works as well as the second field, PARENT_REPORT_ID

  The choice for the PK for this table follows the common APEX convention of using identity fields (numeric with attached sequence and triggers)

  Report JRXML and Jasper fields store both versions of report templates, source and executable. source is only necessary for report inventorying reasons.

  Thumbnail is also optional but can certainly help locate the report template you need to modify in a moderately large application.

  The four fields, create_on, update_on, created_by and updated_by are as usual used for auditing purposes to give a glance over the described activities without complex joins with history tables.

• Report storage table primary key sequence

  CREATE SEQUENCE JAPEX.JAPEX_REPORT_SEQ
              INCREMENT BY 1
              START WITH 1
              NOMAXVALUE
              NOMINVALUE
              NOCYCLE
              CACHE 20
              NOORDER
  GO

  this is the sequence used to populate the report system number (REPORT_SN) in the previous table.

• Report Storage table constraints.

  CREATE UNIQUE INDEX JAPEX.JAPEX_REPORT_PK
              ON JAPEX.JAPEX_REPORT(REPORT_SN)
  GO
  
  CREATE UNIQUE INDEX JAPEX.JAPEX_REPORT_UK1
              ON JAPEX.JAPEX_REPORT(REPORT_ID)
  GO
  
  ALTER TABLE JAPEX.JAPEX_REPORT
              ADD ( CONSTRAINT JAPEX_REPORT_PK
              PRIMARY KEY (REPORT_SN)
              NOT DEFERRABLE INITIALLY IMMEDIATE )
  GO
  
  ALTER TABLE JAPEX.JAPEX_REPORT
              ADD ( CONSTRAINT JAPEX_REPORT_UK1
              UNIQUE (REPORT_ID)
              NOT DEFERRABLE INITIALLY IMMEDIATE )
  GO
  
  ALTER TABLE JAPEX.JAPEX_REPORT
              ADD ( CONSTRAINT JAPEX_REPORT_PARENT
              FOREIGN KEY(PARENT_REPORT_ID)
              REFERENCES JAPEX.JAPEX_REPORT(REPORT_ID)
              NOT DEFERRABLE INITIALLY IMMEDIATE VALIDATE )
  GO

  self describing constraints for the table

• Initializaiton / Simple Audit Before insert trigger.

  CREATE TRIGGER JAPEX.BI_JAPEX_REPORT
    before insert on "JAPEX_REPORT"
    for each row
  begin
    if :NEW."REPORT_SN" is null then
      select "JAPEX_REPORT_SEQ".nextval into :NEW."REPORT_SN" from dual;
    end if;
    
    if :NEW.CREATED_ON is null then
      select sysdate into :NEW."CREATED_ON" from dual;
    end if;
   
    if :NEW.UPDATED_ON is null then
      select sysdate into :NEW."UPDATED_ON" from dual;
    end if;
  end;
  
  GO

  a simple trigger that populates missing field values before inserting a new record into the reports table.

  Please note that a common naming convention was used in describing the previous schema as well as throughout the entire JAPEX code level, while it's optional and configurable, it is highly recommended to follow the same naming convention to help present a consistent coherent naming convention throughout the entire deployment.

  Of course you may go ahead and build a UI interface for managing this table using Apex convenient report/form setup. or even use Blober to provide a fancy interface to such administration page.

Configuring JApex Report Storage

For the following section, it's assumed that you have completed the steps at "JApex Database Connectivity Configuration". this section focuses only on the steps relevant to JApex Reporting Module only

Please consider the following configuration example from the supplied file under /JAPEX_ROOT/WEB-INF/spring/blob-descriptor.xml

<!--    =======================================================================================
                        this is a concrete implementation for the special blob of report
                        ReportDescriptor extends BlobDescriptor with 1 extra field to 
                        retrieve the sub reports (if any) 
                ========================================================================================   -->
<bean   id = "reportTemplate"                   class = "net.sourceforge.japex.blober.ReportDescriptor">
        
        <property name="mimeType"                       value="object/octet-stream"/>
        
        <property name="selectSql"                      value="SELECT TEMPLATE_JASPER FROM JAPEX_REPORT WHERE REPORT_ID=:REPORT_ID"/>
        <property name="updateSql"                      value="UPDATE JAPEX_REPORT SET TEMPLATE_JASPER=:BLOB WHERE REPORT_ID=:REPORT_ID"/>

        <property name="mode"                           value="merge"/>
        <property name="stripExtraParams"       value="true"/>
        
        <property name="insertSql"                      value="INSERT INTO JAPEX_REPORT (REPORT_ID, TEMPLATE_JASPER) VALUES (:REPORT_ID, :BLOB)"/>
        <property name="clearSql"                       value="UPDATE JAPEX_REPORT SET TEMPLATE_JASPER = EMPTY_BLOB() WHERE REPORT_ID=:REPORT_ID"/>
        <property name="deleteSql"                      value="DELETE FROM JAPEX_REPORT WHERE REPORT_ID=:REPORT_ID"/>
<!--
        The Only extra field from those of BlobDescriptor, a report may have sub-reports
        and this query is used by the reporter servlet to recursively retrieve them,
        retrieved reports and piled up inside the report parameter map using their report ids as keys
        hence, sub reports may be accessed in the containing report using and expression identical 
        to normal parameters, i.e. for subreport expression use something like $P{SUB_REPORT_1}
 -->
        <property name="subReportsQuery"        value="SELECT REPORT_ID, TEMPLATE_JASPER FROM JAPEX_REPORT WHERE PARENT_REPORT_ID=:REPORT_ID"/>
        
        <property name="notFoundUrl"            value="/images/nophoto.gif" /> 
</bean>

As you can see the configuration options are almost identical to those described in the similar section for generic blobs. Also as previously said, the report templates table name, schema and exact structure and field names are configurable by means of a single configuration option, that is : selectSql.

As long as the selectSql query returns a single field blob with compiled report content the query takes care of all remaining naming flexibilities.

Please note: the only inflexibly named part of this query is the name of the parameter which is fixed at this point of project lifecycle at the name REPORT_ID.

RESTful report server parameters

with the exception of the following predefined parameters, any parameters passed through the URL will be treated as STRING report parameters and will thus be passed unchanged to the report itself at the time of execution.