How To: Using Print Format Item Scripts In Reports

Introduction

This document expands on the information provided in NF8.2 Print Format Item Script and incorporates the additional features provided by IDEMPIERE-5111 Data type improvements to Print Format Item Script and IDEMPIERE-5253.

The purpose of this feature is to make it possible to create new columns in a report based on an expression, without having to modify the database. The expressions  can make use of either beanshell or SQL scripts.

The Print Format Item Script supports three tokens as follows:

Type	Token	Usage
Column Value	@COL/	@COL/column_name@
Running Total	@ACCUMULATE/	@ACCUMULATE/numeric_column_name@
Line Number	@LINE@	@LINE@
SQL Statement	@SQL=	@SQL= SELECT column_name FROM DUAL

Scripts containing Column Value and Running Total tokens are parsed as a string to the beanshell interpreter which is then able to return numeric, boolean, date or string values to the report.

The SQLStatement token is exclusive and the script must start with the @SQL= token. The sql statement must return a single value and is displayed as text in the report.

Column Value

The @COL/column_name@ token is used to insert the value of a column in a script, for example @COL/DocumentNo@ means the to insert the value of the DocumentNo column in the report. The column must already exist as a Print Format Item prior to being used in a script, however it can be hidden by setting the display logic to 1=2 and clearing the Print Text field. The Column Value can also be a previously defined script column and is referenced by the Print Format Item "Name" field.

Consider the following example script comparing two columns and returning a True or False result:

if (@COL/GrandTotal@ > @COL/TotalLines@) {
 return true;
}
return false;

This could be entered as follows:

Using the value from the example above, our next script column could contain something like:

if (@COL/Has Tax@) {
   return (@COL/Tax@ / @COL/TotalLines@) ;
}
return 0;

This could be entered as follows and include a Format Pattern to display as a percentage:

There are a vast variety of scripts that can be created using the functionality provided by beanshell. Here are a few more examples:

Perform simple arithmetic operations on numeric columns and return a numeric result:

@COL/GrandTotal@ - @COL/TotalLines@

Join two strings and return a string result:

"@COL/DocumentNo@" + "-" + "@COL/DocStatus@"

Convert the string representation of an SQL timestamp to a date. Then based on a comparison add a certain number of days and return the updated date:

import java.util.date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

DateFormat formatter;
Date date;

str_date = "@COL/DateOrdered@";
formatter = new SimpleDateFormat("yyyy-MM-dd");
date = (Date)formatter.parse(str_date);

if (@COL/GrandTotal@ > 100) {
 date.setDate(date.getDate() + 7);
 return date;
}
date.setDate(date.getDate() + 14);
return date;

Running Total

The @ACCUMULATE token is used to print a running total for any numeric column. As in the Column Value token, the column must already exist as a Print Format Item prior to being used in a script. The Running Total and Column Value tokens can also be used in the same script.

Example of showing a running total in a report:

@ACCUMULATE/GrandTotal@

Line Number

The @LINE@ token is used to print the current line on the report.

Example of showing a number line:

@LINE@

Note: this was added in release-9.20220406

SQL Statement

You can use @SQL= to print the value returned from an SQL statement as a string. The SQL statement can reference any column contained in the table associated with the print format and also make use of system variables.

Example using available columns from C_Order base Print Format:

@SQL=SELECT CASE WHEN DatePromised <> DateOrdered THEN DatePromised - DateOrdered ELSE NULL END FROM Dual

Example using System variable @#AD_User_ID@:

@SQL=SELECT Name FROM AD_User WHERE AD_User_ID=@#AD_User_ID@

Example to show the ConvertedAmt on a report based on RV_Payment:

@SQL=SELECT COALESCE(ConvertedAmt,PayAmt)*CASE WHEN C_Payment.IsReceipt='Y' THEN 1 ELSE -1 END FROM C_Payment WHERE C_Payment_ID=RV_Payment.C_Payment_ID

Note About Security

Please note that the beanshell interpreter is a very powerful tool, but also very dangerous. The script field could be misused for SQL Injection, exposing database sensitive information, or even running scripts in the operating system.

That's why the script field is protected, just to be configured by advanced roles.