You are here

Advanced functions in the offline template builder

Use advanced SQL or XSL functions in your report layout to insert custom attribute fields, calculated using your own logic.

Before you begin

You should already be familiar with creating your own custom reports. This article assumes you have already created a report layout using the offline template builder.

We'll provide you with some examples to help you get started. Additional formulas can be found in the complete guide on BI Publisher Functions.

Formulas are defined using either XSL or SQL syntax. You cannot use both an XSL and SQL syntax within a single condition statement.

SQL and XSL Functions

The syntax for SQL and XSL functions for use in RTF templates is as follows:

For extended SQL functions: <?xdofx:expression?>
For extended XSL functions: <?xdoxslt:expression?>

You cannot mix SQL statements with XSL expressions in the same context. For example, assume you had two attributes, FIRST_NAME and LAST_NAME that you wanted to concatenate into a 30-character field and right pad the field with the character "x":

Incorrect syntax: <?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 'x')?>
Why? This syntax begins as an SQL expression, but concat is an XSL expression.

Correct syntax: <?xdofx:rpad(FIRST_NAME||LAST_NAME),30,'x')?>

Insert a Custom Field

  1. Click your mouse in the table cell where you want to place the custom field, then click Field from the insert panel. Insert any field from this list (it doesn’t matter which field is inserted as we are going to customize it’s display name and calculation)
insert a Field
  1. Right-click on the data field you inserted and select BI Publisher > Properties. Give your field a new label in Text to display
add display text
  1. Click the Advanced tab, and enter your formula in the Code field. (you’ll need to remove any text that was already entered there)
  2. Click OK. Your field will be inserted into your layout where you can apply formatting as required.
add custom code


The examples below in both XSL and SQL are specifically useful to Aconex users. 


Difference between two dates

Calculate the difference between two dates in the given locale. The dates need to be in "yyyy-mm-dd" format. This function supports only the Gregorian calendar.
Note: format is the time value for which difference is to be calculated. Valid values are: y - for year; m - for month; w - for week; d - for day; h - for hour; mi - for minute; s - for seconds; ms - for milliseconds
Formula: <?xdoxslt:date_diff(‘format’, ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?>
Sample: <?xdoxslt:date_diff(‘m’, ‘2001-04-08’, ‘2000-02-01’, $_XDOLOCALE, $_XDOTIMEZONE)?>
This example will display ‘-14’ as the result.

Add number of days to a date

Adds ‘x’ number of days to the given date_in field <?xdofx:date_in?>. Output is rendered in the same date format as that of ‘date_in’ field. 
Formula: <?xdoxslt:ora_format_date_offset(date_field, ‘x’, '+')?>
Sample: <?xdoxslt:ora_format_date_offset(date_in,4,’+’)?>

Conditional results (choose-when-otherwise)

Display different results when various conditions are met. Similar to switch/case/evaluate statements in other programming languages.
Fomula: <?choose:?> <?when:expression?> <?otherwise?><?end otherwise?> <?end choose?>
Sample: Identify all delayed steps in In- Progress Workflows that need action on priority. (if a Workflow step is delayed by over a week it needs immediate action & if it is delayed up to a week then it needs attention)
<?when:workflow_status='Completed'?><?end when?>
<?when:workflow_status='Terminated'?><?end when?>
<?when:days_late>7?>Immediate action<?end when?>
<?when:days_late=0?>On Track<?end when?>
<?when:days_late<=7?>Attention<?end when?>
<?end choose?>
‘Immediate action’ displays when days_late field <?xdofx:days_late?> is more than 7
‘Attention’ displays when days_late field <?xdofx:days_late?> is less than or equals 7


Add number of days to a date

Adds ‘x’ number of days to the given date_in field <?xdofx:date_in?>. Also formats the date output in DD-MM-YYYY.
Sample: <?format-date:xdoxslt:ora_format_date_offset(date_in,4,’+’);'DD-MM-YYYY'?>

Conditional text using ‘if’ statement

Use “if” statements to customize text in your report. As an example, for the sentence ‘The document is reviewed’, we can add the word ‘not’ depending on the value of a field named STATUS.
Sample: The document is <?if@inlines:STATUS='Overdue'?>not<?end if?> reviewed.
Result: If the STATUS is ‘Overdue’ the text will read ‘The document is not reviewed’. Otherwise, it will read ‘The document is reviewed’

Conditional results (if-then-else)

Uses an if-then-else condition to display a customized Status.  The Status displays as ‘Late’ if date_in is greater than revision_date, otherwise it displays ‘On Time’.  The field displays as ‘Not Started’ if date_in is NULL (empty).
<?xdofx:If(date_in IS NOT NULL) then
then ‘Late’
else ‘On Time’
end if
else ‘Not Started’
end if?>

Was this article helpful?

Thanks. A ticket has been opened with the Support Central team.