Advanced functions in the offline template builder
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
- 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)
- Right-click on the data field you inserted and select BI Publisher > Properties. Give your field a new label in Text to display.
- Click the Advanced tab, and enter your formula in the Code field. (you’ll need to remove any text that was already entered there)
- Click OK. Your field will be inserted into your layout where you can apply formatting as required.
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’, '+')?>
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:days_late>7?>Immediate action<?end when?>
<?when:days_late=0?>On Track<?end when?>
‘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.
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
else ‘On Time’
else ‘Not Started’