Maintenance required - Friday August 7th 10:00 pm (UTC – Coordinated Universal Time)

Support Central will be briefly offline on Friday August 7th at 10:00 pm (UTC) while we make improvements and upgrades to this site.

You are here

Advanced calculations in BI Publisher

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 BI Publisher

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 calculated 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 Conditional Format from the insert panel.
conditional format
  1. Click the Advanced tab, and enter your formula in the Code field.
click advanced and add formula
  1. Give your field a name and click OK.  
    Your field will be inserted into your layout.  Apply formatting to it as required.
name your formula and click OK

Examples

The examples below in both XSL and SQL are specifically useful to Aconex users.  Additional formulas can be found in the complete guide on BI Publisher Functions.

XSL

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)
<?choose:?>
<?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?>
Sample Result:
‘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

SQL

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).
Sample: 
<?xdofx:If(date_in IS NOT NULL) then
if(date_in>revision_date)
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.