6.8. JXLS Reports

JXLS (http://jxls.sourceforge.net/1.x/) is a templating engine for Microsoft Excel and can be used together with the Dynamic List in order for users to export data into a predefined Excel spreadsheet (see the ReportServer User Guide). JXLS can, however, also be used as a first class report type which offers some advantage over the use as a Dynamic List templating engine: In particular you can define SQL queries directly from within the template and work with parameters.

At the moment ReportServer only supports version 1.0 of the jXLS library. As the current version 2.x is not backwards compatible and would break existing templates we cannot simply update. We are however working on supporting v2 templates alongside the legacy v1 templates.

In order to define a JXLS report, all that is needed in terms of configuration is an Excel file which serves as the template and a datasource which can be accessed from within the template. In addition as with any other report type you can specify parameters that can also be accessed from within the template.

6.8.1. The Basics

Templates are formed using two basic constructs: tags and replacements. A so called tag surrounds an area and defines how the cells within are to be interpreted:

	<jx:forEach items="${data}" var="department">
		${department.name} | ${department.chief}
	</jx:forEach>
In the example each line of the example becomes one line in the Excel document and the pipe symbol (|) marks the end of cell, that is the second line consists of two cells. It is also important to note that commands should start at the very beginning of a cell and that no leading whitespace may exist.

In the example the tag <jx:forEach ...> marks the beginning of a block. The block is closed by the corresponding end tag </jx:forEach>. All cells within the block are repeated for every record in the data variable. To access the individual attributes within the "forEach-block" the variable defined in the "var" attribute can be used: in the example "department". To access an individual attribute use ${department.colname} where "department" is the variable bound to the current data row and "colname" is the name of the attribute.

When used as a template for the dynamic list (see the chapter on the Dynamic List in the User guide), then ${data} contains the data selected by the dynamic list. When used as a first-class report type you need to select the data from the specified datasource. For this, you can use the object ${sql} which provides access to the underlying datasource. As an example consider the following template:

     <jx:forEach items="${sql.exec('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER')}" var="customer">
             ${customer.name} | ${customer.phone} | 
      </jx:forEach>
Variable ${rm} can be used synonymously to ${sql} in order to be consistent with the documentation of JXLS (http://jxls.sourceforge.net/samples/reportsample.html.

Here we select two fields from table T_AGG_CUSTOMER. In order to use parameters within queries, you can use the standard syntax for parameters in the query. This is best understood with an example:

     <jx:forEach items="${sql.exec('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER WHERE CUS_CUSTOMERNAME = ${parameterKey}')}" var="customer">
             ${customer.name} | ${customer.phone} | 
      </jx:forEach>

Here we assume that there exists a parameter with key "parameterKey". Additionally you can access parameters via the ${parameters} object. Again assuming that there is a parameter with key "parameterKey" you could access the parameters value via

${parameters.parameterKey}

Following is a complete example, which works for the demo data that is shipped with ReportServer. It shows a very simple employee report showing some basic information of the employee and the customers served by the employee. The report uses a single parameter with key employee which is assumed to hold an employee number.

Employee Number: | ${parameters.employee}		
<jx:forEach items="${rm.exec('SELECT EMP_FIRSTNAME as firstname, EMP_LASTNAME as lastname FROM T_AGG_EMPLOYEE WHERE EMP_EMPLOYEENUMBER=${employee}')}" var="employee">			
First name: | ${employee.firstname}		
Last name: | ${employee.lastname}		
</jx:forEach>			
			
List of Customers			
			
Customer Name | Customer Number | Volume	
<jx:outline detail="true">			
<jx:forEach items="${rm.exec('SELECT CUS_CUSTOMERNAME as name, CUS_CUSTOMERNUMBER as num, Y_VOLUME as volume FROM T_AGG_CUSTOMER WHERE EMP_EMPLOYEENUMBER=${employee}')}" var="customer">			
${customer.name}  | ${customer.num}  | ${customer.volume}	
</jx:forEach>			
</jx:outline>			
			
	| TOTAL:	| $[SUM(C11)]

A full documentation of the possibilities offered by JXLS is out of scope of this documentation, and we refer the interested reader to the official JXLS documentation available on http://jxls.sourceforge.net/1.x/index.html.