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