Building Template-based Reports

In addition to single query list view report, the GTD Report Server also support template-based report since v3.0.

• Using SQL queries directly in XLS templates
• Multiple Sql query in one sheet.
• Simple property access notation
• Full expression language support
• Complex object graph export
• Flexible collection export
• Flow-Control Tags support
• Dynamic grouping of data
• Adjacent tables support!
• Complex formulas support
• Charts, Macros and many other Excel features in XLS template
• Dynamic Outlines
• Dynamic Columns Hiding
• Dynamic Cell Style processing through custom Processors
• Merged Cells support
• Multiple bean properties in a single cell

How to create a template based report, just 2 steps.

1. Create a excel template.
2. Import template into GTD server, setup the template based report.

The template defines the data modal, layout of the report. We create a few sample templates for you. Because the template get data from pre-defined table: department and employee. So you shold please run the sql file to create tables and insert data.

The ddl sql for Oracle is listed below. You can also see the ddl and dml sql for Mysql, Sql Server , Oracle

CREATE TABLE department (
name varchar(20) NOT NULL, id int NOT NULL PRIMARY KEY );

CREATE TABLE employee (
name varchar(20) default NULL,
payment number default NULL,
bonus number default NULL,
birthDate date default NULL,
id int NOT NULL PRIMARY KEY,
depid int, FOREIGN KEY (depid) REFERENCES department (id) );

Here is the simple template. To view it in Excel please click here.

The output of excel looks like the figure below. To view it in Excel please click here.

Elements in template

Static text: For example A1,B1

forEach tag: see A2. The items property defines the data model. The text in red is fixed, you should change the sql statment and the name of variable only.

End of forEach tag: See A4. The end of the loop.

Property: For example B3. var_employee is defined in the forEach tag, name is property of the each row of sql statement. You can also refer the property by ${var_employee.gtdcolumn1}.

Formula: See D3. Starts with $[.

We private a few templates for your reference. Please view them in ${GTD_HOME}/docs/template-sample.

Step 1: Create a template and put it to ${configDir}/template folder.

The file name of template is also the id of report in GTD server. The ID should be a text string comprise letters (a-z and A-Z), digits (0-9) .
The reportid should be unique in a server.Provided we have created the template named sample2.xls, we will setup a report named sample2.

The sample2.xls can be found in ${GTD_HOME}/docs/template-sample

Step 2: In the Report Manager, click "New Template Based Report... " link, you will see the figure below.

Please enter the report id of the new report and select the data source.

• ID
• Data Source

Step 3: Click next button. You will see the firm like this.

Please fill in the initial valus of paramenter(if availiable), choose the data type of parameter(if availiable),

and then click Next.

Step 4: View the sample output, if OK, please fill in the description of report and parameters, and then save the report.

Now the report has been setup. Is it very easy?

It is not easy for new developer to create the template. So the GTD server provide a minor tool to help you to buld template.

Step 1: In the Report Manager, click "Build Template..." link, you will see the form.

Step 2: Please select the data source, and fill in the sql statement.

For example, the statement is

select name ,payment ,bonus, birthDate from employee where payment > :payment

Click Next and you will see the form like below.

Step 3: Select data type of parameter, and then click next.

Step 4: The GTD server will generate a sample including data model for you. You should just edit the layout and put it to ${configDir}/template folder.