Simple RTF Template report in BI Publisher
RTF- Rich Text Format is a document file format which is used for common word processing applications such as Microsoft Word. Word file is saved as a RTF just by giving ‘.rtf’ extension to the file name. This file format is very useful and easy to create reports for business scenario accomplished with oracle’s Business Intelligence Publisherknown as BI publisher.
This is a very powerful and efficient tool for creating business reports. There is tons of content to learn about BI publisher. We will start learning this one by one so let’s start with creating one simple report.
For that we require one XML file as input or to load into RTF. Consider a book.xml which is easily available on web and you can easily access it for practice. I paste some sample XML data from book.xml so that you can identify and use same XML.
Now we have to create report considering following one simple requirement.
RTF- Rich Text Format is a document file format which is used for common word processing applications such as Microsoft Word. Word file is saved as a RTF just by giving ‘.rtf’ extension to the file name. This file format is very useful and easy to create reports for business scenario accomplished with oracle’s Business Intelligence Publisherknown as BI publisher.
This is a very powerful and efficient tool for creating business reports. There is tons of content to learn about BI publisher. We will start learning this one by one so let’s start with creating one simple report.
For that we require one XML file as input or to load into RTF. Consider a book.xml which is easily available on web and you can easily access it for practice. I paste some sample XML data from book.xml so that you can identify and use same XML.
Now we have to create report considering following one simple requirement.
Requirement:
Get the detail list of the books of ‘Price’<10.
Get the detail list of the books of ‘Price’<10.
Solution:
Now for the given XML there are 12 books. We have to filter out those books from the list which has their price less than 10. First of all we will list the total book-list then will try to apply this filter to get the requirement work. Now to get the list of total books we will proceed step by step.
Step #1: Create RTF file.
Open a blank word document. Save that word document with ‘.rtf’ extension. Lets give here ‘books.rtf’.
Step #2: Load the XML.
Now we have to load that Books.xml into created Books.rtf. To do this, click on the ‘Add-Ins’ from the title bar of word file. Following window will pop up.
Click on ‘Data’ and then ‘Load XML Data’. Refer below image.
Then you have to select the XML which you want load into RTF. If the XML load is successful then following window will pop up.
Step #3: Insert Table.
As the XML is loaded successfully next step we have to get the table form of this XML data. To do so navigate to ‘Insert’ tab which is next to ‘Data’. Refer above image. After that select Table wizard as shown in below image.
After selecting ‘Table Wizard’ following window will pop up.
Select ‘Table’, click on ‘Next’. You will see the hierarchies available in loaded XML. But for our XML there is only one so its showing that one as default. Refer below image. Click ‘Next’
Above image shows list of all the field names for particular Book ID tab. You can select which are necessary. In our case we have to get detailed list so will select all field names. Those which are selected will get shifted to the right pane as below.
If we click on ‘Next’, click on ‘Finish’. Following table will get loaded in your RTF pane.
You can adjust the column width, font, font size, column header etc. as per your requirement.
Step #4:After this table get inserted we have to check it’s giving correct output or not. To do so navigate through ‘Preview’ tab. You will find options like PDF, HTML etc. refer below image. You will get the idea.
These PDF. HTML, EXCEL etc. options gives facility to show output in different format. Let’s take our output in PDF format. Click on ‘PDF’. If your word document is not saved as RTF file it will ask you to save it in RTF format. Just save it using ‘.rtf’ extension. Otherwise it will show your PDF output as below.
Step #5: Work on requirement.
Now we have to get our requirement work. For that we have to apply some logic like “If price < 10 then show corresponding records”. Follow the following procedure. You will get the clear idea.
- As we have to deal with ‘price’, double click on it, following window will pop up.
- Click on ‘Advance’ Tab.
This symbol <? ?> gives the value of field whichever is inside it. As per now we are getting ‘price’ value because of it.
- Now we have to apply our logic. To do so write following code or query in advance tab as shown in following image.
- Now run the report by navigating through ‘Preview’ -> ‘PDF’. You will get detailed list of books of having price less than 10 as below. All the record of having price > 10 will get omitted.
I hope this will help you to create your BIP report. This is efficient and easy to understand method I followed when I started working on BIP. There are another method to put ‘If’ condition also but it needs some modification.
Now for the given XML there are 12 books. We have to filter out those books from the list which has their price less than 10. First of all we will list the total book-list then will try to apply this filter to get the requirement work. Now to get the list of total books we will proceed step by step.
Step #1: Create RTF file.
Open a blank word document. Save that word document with ‘.rtf’ extension. Lets give here ‘books.rtf’.
Step #2: Load the XML.
Now we have to load that Books.xml into created Books.rtf. To do this, click on the ‘Add-Ins’ from the title bar of word file. Following window will pop up.
Click on ‘Data’ and then ‘Load XML Data’. Refer below image.
Then you have to select the XML which you want load into RTF. If the XML load is successful then following window will pop up.
Step #3: Insert Table.
As the XML is loaded successfully next step we have to get the table form of this XML data. To do so navigate to ‘Insert’ tab which is next to ‘Data’. Refer above image. After that select Table wizard as shown in below image.
After selecting ‘Table Wizard’ following window will pop up.
Select ‘Table’, click on ‘Next’. You will see the hierarchies available in loaded XML. But for our XML there is only one so its showing that one as default. Refer below image. Click ‘Next’
Above image shows list of all the field names for particular Book ID tab. You can select which are necessary. In our case we have to get detailed list so will select all field names. Those which are selected will get shifted to the right pane as below.
If we click on ‘Next’, click on ‘Finish’. Following table will get loaded in your RTF pane.
You can adjust the column width, font, font size, column header etc. as per your requirement.
Step #4:After this table get inserted we have to check it’s giving correct output or not. To do so navigate through ‘Preview’ tab. You will find options like PDF, HTML etc. refer below image. You will get the idea.
These PDF. HTML, EXCEL etc. options gives facility to show output in different format. Let’s take our output in PDF format. Click on ‘PDF’. If your word document is not saved as RTF file it will ask you to save it in RTF format. Just save it using ‘.rtf’ extension. Otherwise it will show your PDF output as below.
Step #5: Work on requirement.
Now we have to get our requirement work. For that we have to apply some logic like “If price < 10 then show corresponding records”. Follow the following procedure. You will get the clear idea.
- As we have to deal with ‘price’, double click on it, following window will pop up.
- Click on ‘Advance’ Tab.
This symbol <? ?> gives the value of field whichever is inside it. As per now we are getting ‘price’ value because of it.
- Now we have to apply our logic. To do so write following code or query in advance tab as shown in following image.
- Now run the report by navigating through ‘Preview’ -> ‘PDF’. You will get detailed list of books of having price less than 10 as below. All the record of having price > 10 will get omitted.
I hope this will help you to create your BIP report. This is efficient and easy to understand method I followed when I started working on BIP. There are another method to put ‘If’ condition also but it needs some modification.
Extended Function Support in RTF Templates using in Oracle BI Publisher
BI Publisher has extended a set of SQL and XSL functions for use in RTF templates. The syntax for these extended functions is
<?xdofx:expression?>
for extended SQL functions or
<?xdoxslt:expression?>
for extended XSL functions.
Note: You cannot mix xdofx statements with XSL expressions in the same context. For example, assume you had two elements, FIRST_NAME and LAST_NAME that you wanted to concatenate into a 30-character field and right pad the field with the character "x", you could NOT use the following:
INCORRECT:
<?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 'x')?>
because concat is an XSL expression. Instead, you could use the following:
CORRECT:
<?xdofx:rpad(FIRST_NAME||LAST_NAME),30,'x')?>
The supported functions are shown in the following table:
SQL Statement or XSL Expression Usage Description 2+3 <?xdofx:2+3?> Addition 2-3 <?xdofx:2-3?> Subtraction 2*3 <?xdofx:2*3?> Multiplication 2/3 <?xdofx:2/3?> Division 2**3 <?xdofx:2**3?> Exponential 3||2 <?xdofx:3||2?> Concatenation lpad('aaa',10,'.') <?xdofx:lpad('aaa',10,'.')?> The lpad function pads the left side of a string with a specific set of characters. The syntax for the lpad function is:
lpad(string1,padded_length,[pad_string])
string1 is the string to pad characters to (the left-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the left-hand side ofstring1 . rpad('aaa',10,'.') <?xdofx:rpad('aaa',10,'.')?> The rpad function pads the right side of a string with a specific set of characters.
The syntax for the rpad function is:
rpad(string1,padded_length,[pad_string]).
string1 is the string to pad characters to (the right-hand side).
padded_length is the number of characters to return.
pad_string is the string that will be padded to the right-hand side ofstring1 trim() <?xdoxslt:trim(‘ a ‘)?> Removes spaces in a string. Enter the text to be trimmed, the function returns the trimmed text. ltrim() <?xdoxslt:ltrim(‘ a ‘)?> Removes the leading white spaces in a string. rtrim() <?xdoxslt:rtrim(‘ a ‘)?> Removes the trailing white spaces in a string. truncate <?xdoxslt:truncate ( number [, integer ] )?> The truncate function returns number truncated to integer places right of the decimal point. If integer is omitted, then number is truncated to the whole integer value. integer can be negative to truncate values left of the decimal point. integer must be an integer.
Example:
<?xdoxslt:truncate(-2.3333)?>
returns
-2
Example:
<?xdoxslt:truncate(2.7777, 2)?>
returns
2.77
Example:
<?xdoxslt:truncate(27.7777, -1)?>
returns
20 replicate <?xdoxslt:replicate(‘string’, integer)?> The replicate function will replicate the specified string the specified number of times.
Example:
<?xdoxslt:replicate(‘oracle’, 3)?>
returns
oracleoracleoracle decode('xxx','bbb','ccc','xxx','ddd') <?xdofx:decode('xxx','bbb','ccc','xxx','ddd')?> The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is:
decode(expression, search, result [,search, result]...[, default])
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is returned if no matches are found. Instr('abcabcabc','a',2) <?xdofx:Instr('abcabcabc','a',2)?> The instr function returns the location of a substring in a string. The syntax for the instr function is:
instr(string1,string2,[start_position],[nth_appearance])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth appearance is the nth appearance of string2. substr('abcdefg',2,3) <?xdofx:substr('abcdefg',2,3)?> The substr function allows you to extract a substring from a string. The syntax for the substr function is:
substr(string, start_position, length)
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is the number of characters to extract. left <?xdoxslt:left(‘abcdefg’, 3)?> Enables you to extract the specified number of characters from a string, starting from the left. The syntax is left(string, Numchars)
For example, <?xdoxslt:left(‘abcdefg’, 3)?>
returns
abc right <?xdoxslt:right(‘abcdefg’, 3)?> Enables you to extract the specified number of characters from a string, starting from the right. The syntax is right(string, Numchars)
For example, <?xdoxslt:right(‘abcdefg’, 3)?>
returns
efg replace(name,'John','Jon') <?xdofx:replace(name,'John','Jon')?> The replace function replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is:
replace(string1,string_to_replace,[replacement_string])
string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. to_number('12345') <?xdofx:to_number('12345')?> Function to_number converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype. format_number <?xdoxslt:format_number(12345, n, $_XDOLOCALE)?> Converts a number to a string and formats the number according to the locale specified in $_XDOLOCALE and to the number of decimal positions specified in n using Java's default symbols. For example:
<?xdoxslt:format_number(-12345, 2, ‘fr-FR’)?>
returns
-12 345,00 format_number <?xdoxslt:format_number(12345, n, s1,s2,$_XDOLOCALE)?> Converts a number to a string and uses the specified separators: s1 for the thousand separator and s2 for the decimal separator. For example:
<?xdoxslt:format_number(12345, 2, 'g', 'd', $_XDOLOCALE)?> returns
12g345d00 pat_format_number <?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?> Returns a number formatted with the specified pattern.
For example:
<?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?>
returns
12,345.00 to_char(12345) <?xdofx:to_char('12345')?> Use the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype. to_date <?xdofx:to_date ( char [, fmt [, 'nlsparam']] ) TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then charmust be in the default date format. If fmt is 'J', for Julian, then charmust be an integer. sysdate() <?xdofx:sysdate()?> SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments. current_date() <?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?>
Example: <?xdoxslt:current_date('ja-JP', 'Asia/Tokyo')?> Returns the current date in "yyyy-MM-dd" format in the given locale and timezone. This function supports only the Gregorian calendar. current_time() <?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?>
Example:
<?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?> Returns the current time in the given locale and timezone. This function supports only the Gregorian calendar. minimum <?xdoxslt:minimum(ELEMENT_NAME)?> Returns the minimum value of the element in the set. date_diff <?xdoxslt:date_diff(‘y', ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?> This function provides a method to get 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. The syntax is as follows:
<?xdoxslt:date_diff(‘format’, ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?>
where
format is the time value for which the 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
Example:
<?xdoxslt:date_diff(‘y’, ‘2000-04-08’, ‘2001-05-01’, $_XDOLOCALE, $_XDOTIMEZONE)?>
returns
1
Example:
<?xdoxslt:date_diff(‘m’, ‘2001-04-08’, ‘2000-02-01’, $_XDOLOCALE, $_XDOTIMEZONE)?>
returns
-14
Example:
<?xdoxslt:date_diff(‘d’, ‘2006-04-08’, ‘2006-04-01’, $_XDOLOCALE, ‘America/Los_Angeles’)?>
returns
-7 sec_diff <?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?> This function provides a method to get the difference between two dates in seconds in the given locale. The dates need to be in "yyyy-MM-dd'T'HH:mm:ss". This function supports only Gregorian calendar.
Example:
<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?>
returns
3600 get_day <?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?> This function provides a method to get the day value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar.
Example:
<?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?>
returns
8 get_month <?xdoxslt:get_month(‘2000-04-08’, $_XDOLOCALE)?> This function provides a method to get the month value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar.
Example:
<?xdoxslt:get_month(‘2000-04-08’, $_XDOLOCALE)?>
returns
4 get_year <?xdoxslt:get_year(‘2000-04-08’, $_XDOLOCALE)?> This function provides a method to get the year value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar.
Example:
<?xdoxslt:get_year(‘2000-04-08’, $_XDOLOCALE)?>
returns
2000 month_name <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> This function provides a method to get the name of the month in the given locale. This function supports only the Gregorian calendar.
The syntax for this function is:
<?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?>
where
month is the numeric value of the month (Januany = 1)
and
[abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate.
Example:
<?xdoxslt:month_name(12, 1, ‘fr-FR’)?>
returns
dec.
Example"
<?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>
returns
January maximum <?xdoxslt:maximum(ELEMENT_NAME)?> Returns the maximum value of the element in the set. abs <?xdoxslt:abs(-123.45)?> Returns the absolute value of the number entered.
Example:
<?xdoxslt:abs(-123.45)?>
Returns:
123.45 chr <?xdofx:chr(n)?> CHR returns the character having the binary equivalent to n in either the database character set or the national character set. ceil <?xdofx:ceil(n)?> CEIL returns smallest integer greater than or equal to n. floor <?xdofx:floor(n)?> FLOOR returns largest integer equal to or less than n. round
(SQL function) <?xdofx:round ( number [, integer ] )?> ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integercan be negative to round off digits left of the decimal point. integermust be an integer.
Example:
<?xdofx:round (2.777)?>
returns
3
Example:
<?xdofx:round (2.777, 2)?>
returns
2.78 round
(XSLT function) <?xdoxslt:round ( number [, integer ] )?> ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integercan be negative to round off digits left of the decimal point. integermust be an integer.
Example:
<?xdoxslt:round (2.777)?>
returns
3
Example:
<?xdoxslt:round (2.777, 2)?>
returns
2.78 lower <?xdofx:lower (char)?> LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char. upper <?xdofx:upper(char)?> UPPER returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char. length <?xdofx:length(char)?> The "length" function returns the length of char. LENGTH calculates length using characters as defined by the input character set. greatest <?xdofx:greatest ( expr [, expr]... )?> GREATEST returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. least <?xdofx:least ( expr [, expr]... )?> LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. next_element <?xdoxslt:next_element(current-group(),.,'<element-name>')?> Method to get the next element in the current group. Will return the element that occurs after the element named. For example:
<?xdoxslt:next_element(current-group(),.,'employee')?>
will return the element that occurs in the current group after "employee". prev_element <?xdoxslt:prev_element(current-group(),..,'<element-name')?> Method to get the previous element in the current group. Will return the element that occurs before the element named. For example:
<?xdoxslt:prev_element(current-group(),.,'employee')?>
will return the element that occurs in the current group before "employee". set_array <?xdoxslt:set_array($_XDOCTX, ‘<name of hash table>’, n, ‘<value>’)?> Sets a value in a hash table. Syntax is <?xdoxslt:set_array($_XDOCTX, ‘<name of hash table>’, n, ‘<value>’)?>
where
$_XDOCTX is required to set the context,
<name of hash table> is the name you supply for your table
n is the index of the hash table
<value> is the value to set in the hash table.
For example:
<?xdoxslt:set_array($_XDOCTX, ‘Employee’, 2, ‘Jones’)?>
See get_array below. get_array <?xdoxslt:get_array($_XDOCTX, ‘<name of hash table>’, n)?> Returns the value at the specified index of the hash table.
Syntax is <?xdoxslt:get_array($_XDOCTX, ‘<name of hash table>’, n)?>
where
$_XDOCTX is required to set the context,
<name of hash table> is the name you supplied for your table in set_array
n is the index value of the element you want returned.
For example, used in conjunction with the set_array example above,
<?xdoxslt:get_array($_XDOCTX, ‘Employee’, 2)?>
returns
Jones
The following table shows supported combination functions:
SQL Statement Usage (2+3/4-6*7)/8 <?xdofx:(2+3/4-6*7)/8?> lpad(substr('1234567890',5,3),10,'^') <?xdofx:lpad(substr('1234567890',5,3),10,'^')?> decode('a','b','c','d','e','1')||instr('321',1,1) <?xdofx:decode('a','b','c','d','e','1')||instr('321',1,1)?>
BI Publisher has extended a set of SQL and XSL functions for use in RTF templates. The syntax for these extended functions is
<?xdofx:expression?>
for extended SQL functions or
<?xdoxslt:expression?>
for extended XSL functions.
Note: You cannot mix xdofx statements with XSL expressions in the same context. For example, assume you had two elements, FIRST_NAME and LAST_NAME that you wanted to concatenate into a 30-character field and right pad the field with the character "x", you could NOT use the following:
INCORRECT:
CORRECT:
The following table shows supported combination functions:
<?xdofx:expression?>
for extended SQL functions or
<?xdoxslt:expression?>
for extended XSL functions.
Note: You cannot mix xdofx statements with XSL expressions in the same context. For example, assume you had two elements, FIRST_NAME and LAST_NAME that you wanted to concatenate into a 30-character field and right pad the field with the character "x", you could NOT use the following:
INCORRECT:
<?xdofx:rpad(concat(FIRST_NAME,LAST_NAME),30, 'x')?>CORRECT:
<?xdofx:rpad(FIRST_NAME||LAST_NAME),30,'x')?>| SQL Statement or XSL Expression | Usage | Description |
|---|---|---|
| 2+3 | <?xdofx:2+3?> | Addition |
| 2-3 | <?xdofx:2-3?> | Subtraction |
| 2*3 | <?xdofx:2*3?> | Multiplication |
| 2/3 | <?xdofx:2/3?> | Division |
| 2**3 | <?xdofx:2**3?> | Exponential |
| 3||2 | <?xdofx:3||2?> | Concatenation |
| lpad('aaa',10,'.') | <?xdofx:lpad('aaa',10,'.')?> | The lpad function pads the left side of a string with a specific set of characters. The syntax for the lpad function is: lpad(string1,padded_length,[pad_string]) string1 is the string to pad characters to (the left-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the left-hand side ofstring1 . |
| rpad('aaa',10,'.') | <?xdofx:rpad('aaa',10,'.')?> | The rpad function pads the right side of a string with a specific set of characters. The syntax for the rpad function is: rpad(string1,padded_length,[pad_string]). string1 is the string to pad characters to (the right-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the right-hand side ofstring1 |
| trim() | <?xdoxslt:trim(‘ a ‘)?> | Removes spaces in a string. Enter the text to be trimmed, the function returns the trimmed text. |
| ltrim() | <?xdoxslt:ltrim(‘ a ‘)?> | Removes the leading white spaces in a string. |
| rtrim() | <?xdoxslt:rtrim(‘ a ‘)?> | Removes the trailing white spaces in a string. |
| truncate | <?xdoxslt:truncate ( number [, integer ] )?> | The truncate function returns number truncated to integer places right of the decimal point. If integer is omitted, then number is truncated to the whole integer value. integer can be negative to truncate values left of the decimal point. integer must be an integer. Example: <?xdoxslt:truncate(-2.3333)?> returns -2 Example: <?xdoxslt:truncate(2.7777, 2)?> returns 2.77 Example: <?xdoxslt:truncate(27.7777, -1)?> returns 20 |
| replicate | <?xdoxslt:replicate(‘string’, integer)?> | The replicate function will replicate the specified string the specified number of times. Example: <?xdoxslt:replicate(‘oracle’, 3)?> returns oracleoracleoracle |
| decode('xxx','bbb','ccc','xxx','ddd') | <?xdofx:decode('xxx','bbb','ccc','xxx','ddd')?> | The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode(expression, search, result [,search, result]...[, default]) expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is returned if no matches are found. |
| Instr('abcabcabc','a',2) | <?xdofx:Instr('abcabcabc','a',2)?> | The instr function returns the location of a substring in a string. The syntax for the instr function is: instr(string1,string2,[start_position],[nth_appearance]) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1. nth appearance is the nth appearance of string2. |
| substr('abcdefg',2,3) | <?xdofx:substr('abcdefg',2,3)?> | The substr function allows you to extract a substring from a string. The syntax for the substr function is: substr(string, start_position, length) string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is the number of characters to extract. |
| left | <?xdoxslt:left(‘abcdefg’, 3)?> | Enables you to extract the specified number of characters from a string, starting from the left. The syntax is left(string, Numchars) For example, <?xdoxslt:left(‘abcdefg’, 3)?> returns abc |
| right | <?xdoxslt:right(‘abcdefg’, 3)?> | Enables you to extract the specified number of characters from a string, starting from the right. The syntax is right(string, Numchars) For example, <?xdoxslt:right(‘abcdefg’, 3)?> returns efg |
| replace(name,'John','Jon') | <?xdofx:replace(name,'John','Jon')?> | The replace function replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is: replace(string1,string_to_replace,[replacement_string]) string1 is the string to replace a sequence of characters with another set of characters. string_to_replace is the string that will be searched for in string1. replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. |
| to_number('12345') | <?xdofx:to_number('12345')?> | Function to_number converts char, a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype. |
| format_number | <?xdoxslt:format_number(12345, n, $_XDOLOCALE)?> | Converts a number to a string and formats the number according to the locale specified in $_XDOLOCALE and to the number of decimal positions specified in n using Java's default symbols. For example: <?xdoxslt:format_number(-12345, 2, ‘fr-FR’)?> returns -12 345,00 |
| format_number | <?xdoxslt:format_number(12345, n, s1,s2,$_XDOLOCALE)?> | Converts a number to a string and uses the specified separators: s1 for the thousand separator and s2 for the decimal separator. For example: <?xdoxslt:format_number(12345, 2, 'g', 'd', $_XDOLOCALE)?> returns 12g345d00 |
| pat_format_number | <?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?> | Returns a number formatted with the specified pattern. For example: <?xdoxslt:pat_format_number(12345, ‘##,##0.00’, $_XDOLOCALE)?> returns 12,345.00 |
| to_char(12345) | <?xdofx:to_char('12345')?> | Use the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype. |
| to_date | <?xdofx:to_date ( char [, fmt [, 'nlsparam']] ) | TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then charmust be in the default date format. If fmt is 'J', for Julian, then charmust be an integer. |
| sysdate() | <?xdofx:sysdate()?> | SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments. |
| current_date() | <?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?> Example: <?xdoxslt:current_date('ja-JP', 'Asia/Tokyo')?> | Returns the current date in "yyyy-MM-dd" format in the given locale and timezone. This function supports only the Gregorian calendar. |
| current_time() | <?xdoxslt:current_time($_XDOLOCALE, $_XDOTIMEZONE)?> Example: <?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?> | Returns the current time in the given locale and timezone. This function supports only the Gregorian calendar. |
| minimum | <?xdoxslt:minimum(ELEMENT_NAME)?> | Returns the minimum value of the element in the set. |
| date_diff | <?xdoxslt:date_diff(‘y', ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?> | This function provides a method to get 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. The syntax is as follows: <?xdoxslt:date_diff(‘format’, ‘YYYY-MM-DD’, ‘YYYY-MM-DD’, $_XDOLOCALE, $_XDOTIMEZONE)?> where format is the time value for which the difference is to be calculated. Valid values are :
Example: <?xdoxslt:date_diff(‘y’, ‘2000-04-08’, ‘2001-05-01’, $_XDOLOCALE, $_XDOTIMEZONE)?> returns 1 Example: <?xdoxslt:date_diff(‘m’, ‘2001-04-08’, ‘2000-02-01’, $_XDOLOCALE, $_XDOTIMEZONE)?> returns -14 Example: <?xdoxslt:date_diff(‘d’, ‘2006-04-08’, ‘2006-04-01’, $_XDOLOCALE, ‘America/Los_Angeles’)?> returns -7 |
| sec_diff | <?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?> | This function provides a method to get the difference between two dates in seconds in the given locale. The dates need to be in "yyyy-MM-dd'T'HH:mm:ss". This function supports only Gregorian calendar. Example: <?xdoxslt:sec_diff(‘2000-04-08T20:00:00’, ‘2000-04-08T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?> returns 3600 |
| get_day | <?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?> | This function provides a method to get the day value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?> returns 8 |
| get_month | <?xdoxslt:get_month(‘2000-04-08’, $_XDOLOCALE)?> | This function provides a method to get the month value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_month(‘2000-04-08’, $_XDOLOCALE)?> returns 4 |
| get_year | <?xdoxslt:get_year(‘2000-04-08’, $_XDOLOCALE)?> | This function provides a method to get the year value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_year(‘2000-04-08’, $_XDOLOCALE)?> returns 2000 |
| month_name | <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> | This function provides a method to get the name of the month in the given locale. This function supports only the Gregorian calendar. The syntax for this function is: <?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?> where month is the numeric value of the month (Januany = 1) and [abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate. Example: <?xdoxslt:month_name(12, 1, ‘fr-FR’)?> returns dec. Example" <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> returns January |
| maximum | <?xdoxslt:maximum(ELEMENT_NAME)?> | Returns the maximum value of the element in the set. |
| abs | <?xdoxslt:abs(-123.45)?> | Returns the absolute value of the number entered. Example: <?xdoxslt:abs(-123.45)?> Returns: 123.45 |
| chr | <?xdofx:chr(n)?> | CHR returns the character having the binary equivalent to n in either the database character set or the national character set. |
| ceil | <?xdofx:ceil(n)?> | CEIL returns smallest integer greater than or equal to n. |
| floor | <?xdofx:floor(n)?> | FLOOR returns largest integer equal to or less than n. |
| round (SQL function) | <?xdofx:round ( number [, integer ] )?> | ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integercan be negative to round off digits left of the decimal point. integermust be an integer. Example: <?xdofx:round (2.777)?> returns 3 Example: <?xdofx:round (2.777, 2)?> returns 2.78 |
| round (XSLT function) | <?xdoxslt:round ( number [, integer ] )?> | ROUND returns number rounded to integer places right of the decimal point. If integer is omitted, then number is rounded to 0 places. integercan be negative to round off digits left of the decimal point. integermust be an integer. Example: <?xdoxslt:round (2.777)?> returns 3 Example: <?xdoxslt:round (2.777, 2)?> returns 2.78 |
| lower | <?xdofx:lower (char)?> | LOWER returns char, with all letters lowercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char. |
| upper | <?xdofx:upper(char)?> | UPPER returns char, with all letters uppercase. char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same datatype as char. |
| length | <?xdofx:length(char)?> | The "length" function returns the length of char. LENGTH calculates length using characters as defined by the input character set. |
| greatest | <?xdofx:greatest ( expr [, expr]... )?> | GREATEST returns the greatest of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. |
| least | <?xdofx:least ( expr [, expr]... )?> | LEAST returns the least of the list of exprs. All exprs after the first are implicitly converted to the datatype of the first expr before the comparison. |
| next_element | <?xdoxslt:next_element(current-group(),.,'<element-name>')?> | Method to get the next element in the current group. Will return the element that occurs after the element named. For example: <?xdoxslt:next_element(current-group(),.,'employee')?> will return the element that occurs in the current group after "employee". |
| prev_element | <?xdoxslt:prev_element(current-group(),..,'<element-name')?> | Method to get the previous element in the current group. Will return the element that occurs before the element named. For example: <?xdoxslt:prev_element(current-group(),.,'employee')?> will return the element that occurs in the current group before "employee". |
| set_array | <?xdoxslt:set_array($_XDOCTX, ‘<name of hash table>’, n, ‘<value>’)?> | Sets a value in a hash table. Syntax is <?xdoxslt:set_array($_XDOCTX, ‘<name of hash table>’, n, ‘<value>’)?> where $_XDOCTX is required to set the context, <name of hash table> is the name you supply for your table n is the index of the hash table <value> is the value to set in the hash table. For example: <?xdoxslt:set_array($_XDOCTX, ‘Employee’, 2, ‘Jones’)?> See get_array below. |
| get_array | <?xdoxslt:get_array($_XDOCTX, ‘<name of hash table>’, n)?> | Returns the value at the specified index of the hash table. Syntax is <?xdoxslt:get_array($_XDOCTX, ‘<name of hash table>’, n)?> where $_XDOCTX is required to set the context, <name of hash table> is the name you supplied for your table in set_array n is the index value of the element you want returned. For example, used in conjunction with the set_array example above, <?xdoxslt:get_array($_XDOCTX, ‘Employee’, 2)?> returns Jones |
| SQL Statement | Usage |
|---|---|
| (2+3/4-6*7)/8 | <?xdofx:(2+3/4-6*7)/8?> |
| lpad(substr('1234567890',5,3),10,'^') | <?xdofx:lpad(substr('1234567890',5,3),10,'^')?> |
| decode('a','b','c','d','e','1')||instr('321',1,1) | <?xdofx:decode('a','b','c','d','e','1')||instr('321',1,1)?> |
XSL Equivalents
The following table lists the BI Publisher simplified syntax with the XSL equivalents.
Supported XSL Elements Description BI Publisher Syntax <xsl:value-of select= "name"> Placeholder syntax <?name?> <xsl:apply-templates select="name"> Applies a template rule to the current element's child nodes. <?apply:name?> <xsl:copy-of select="name"> Creates a copy of the current node. <?copy-of:name?> <xsl:call-template name="name"> Calls a named template to be inserted into/applied to the current template. <?call:name?> <xsl:sort select="name"> Sorts a group of data based on an element in the dataset. <?sort:name?> <xsl:for-each select="name"> Loops through the rows of data of a group, used to generate tabular output. <?for-each:name?> <xsl:choose> Used in conjunction with when and otherwise to express multiple conditional tests. <?choose?> <xsl:when test="exp"> Used in conjunction with choose and otherwise to express multiple conditional tests <?when:expression?> <xsl:otherwise> Used in conjunction with choose and when to express multiple conditional tests <?otherwise?> <xsl:if test="exp"> Used for conditional formatting. <?if:expression?> <xsl:template name="name"> Template declaration <?template:name?> <xsl:variable name="name"> Local or global variable declaration <?variable:name?> <xsl:import href="url"> Import the contents of one stylesheet into another <?import:url?> <xsl:include href="url"> Include one stylesheet in another <?include:url?> <xsl:stylesheet xmlns:x="url"> Define the root element of a stylesheet <?namespace:x=url?>
The following table lists the BI Publisher simplified syntax with the XSL equivalents.
| Supported XSL Elements | Description | BI Publisher Syntax |
|---|---|---|
| <xsl:value-of select= "name"> | Placeholder syntax | <?name?> |
| <xsl:apply-templates select="name"> | Applies a template rule to the current element's child nodes. | <?apply:name?> |
| <xsl:copy-of select="name"> | Creates a copy of the current node. | <?copy-of:name?> |
| <xsl:call-template name="name"> | Calls a named template to be inserted into/applied to the current template. | <?call:name?> |
| <xsl:sort select="name"> | Sorts a group of data based on an element in the dataset. | <?sort:name?> |
| <xsl:for-each select="name"> | Loops through the rows of data of a group, used to generate tabular output. | <?for-each:name?> |
| <xsl:choose> | Used in conjunction with when and otherwise to express multiple conditional tests. | <?choose?> |
| <xsl:when test="exp"> | Used in conjunction with choose and otherwise to express multiple conditional tests | <?when:expression?> |
| <xsl:otherwise> | Used in conjunction with choose and when to express multiple conditional tests | <?otherwise?> |
| <xsl:if test="exp"> | Used for conditional formatting. | <?if:expression?> |
| <xsl:template name="name"> | Template declaration | <?template:name?> |
| <xsl:variable name="name"> | Local or global variable declaration | <?variable:name?> |
| <xsl:import href="url"> | Import the contents of one stylesheet into another | <?import:url?> |
| <xsl:include href="url"> | Include one stylesheet in another | <?include:url?> |
| <xsl:stylesheet xmlns:x="url"> | Define the root element of a stylesheet | <?namespace:x=url?> |
Using FO Elements
You can use most FO elements in an RTF template inside the Microsoft Word form fields. The following FO elements have been extended for use with BI Publisher RTF templates. The BI Publisher syntax can be used with either RTF template method.
The full list of FO elements supported by BI Publisher can be found in the Appendix: Supported XSL-FO Elements.
FO Element BI Publisher Syntax <fo:page-number-citation ref-id="id"> <?fo:page-number-citation:id?> <fo:page-number> <?fo:page-number?> <fo:ANY NAME WITHOUT ATTRIBUTE> <?fo:ANY NAME WITHOUT ATTRIBUTE?>
You can use most FO elements in an RTF template inside the Microsoft Word form fields. The following FO elements have been extended for use with BI Publisher RTF templates. The BI Publisher syntax can be used with either RTF template method.
The full list of FO elements supported by BI Publisher can be found in the Appendix: Supported XSL-FO Elements.
The full list of FO elements supported by BI Publisher can be found in the Appendix: Supported XSL-FO Elements.
| FO Element | BI Publisher Syntax |
|---|---|
| <fo:page-number-citation ref-id="id"> | <?fo:page-number-citation:id?> |
| <fo:page-number> | <?fo:page-number?> |
| <fo:ANY NAME WITHOUT ATTRIBUTE> | <?fo:ANY NAME WITHOUT ATTRIBUTE?> |
In last article we have seen two mostly used or faced scenarios by BI publisher users now a day. In this article we will go through some functions in BI Publisher with examples which may help you while dealing with Bi publisher.
- Substr:
As name suggest this function is used to deal with part of a string. Suppose we want to display “ “ (Null Value) where ‘Price’ contains “None” then we can use this function as follows. Here we will see If Then clause in BI Publisher too.
<?xdofx:if:substr(Price,2,4)!=”None” then Price else ” ?>
(Price,2,4) = For entity Price, from 2nd character next 4 characters (2nd character enclusive) are not equal to None then put Price’s value as it is otherwise ‘ ’ (Null).
- convert_case:
It converts lower to upper or upper to lower case. This is one of the most useful function while dealing with BI Publisher. Lets see above example withConvert_case.
If ‘Price’ contains upper case ‘NONE’ then in this scenario we can modify above query as below.
<?xdofx: if convert_case(substr(Price,2,4),’UPPER’)=’NONE’ then ” else Price?>
Convert_case converts For entity Price, from 2nd character next 4 characters (2nd character enclusive) to upper case and compares.
Lets study some more function in detail.
Consider I have following XML
<book id=”bk101″>
<author>Gambardella, Matthew</author>
<title>XML Developer’s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<price>42.25</price>
<price>42.21</price>
<price>47.65</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications with XML.</description>
</book>
Now we can see from above XML that there is variation of values under same entity i.e. Price. Now lets see how BI publisher will react for this type of XML.
From above result we can see that it takes only first value for Price i.e. 44.95. If we want list it as variation in price we can’t do that. For that purpose we need to use some queries and all. We are going to learn how to use variable, how to put values in one line and some other functionalities too.
Consider we want the output in following format.
First of all we need identify how many times this variation of price is there i.e. we need to get count of that. In our case its 4. To get this value we will use following queries.
<?xdoxslt:set_variable($_XDOCTX, ‘cnt’, count(.//Price))?>
Here, ‘cnt’ is a variable name which is set to get number of values for ‘Price’. For that we have used ‘Count’ function. As we get this count, we need to use this to retrieve the values for price. For that consider following query.
<?for-each@inlines:price?><?.?><?if@inlines:position()!=xdoxslt:get_variable($_XDOCTX, ‘cnt’)?>, <?end if?> <?end for-each?>
In above code, we have used For-each function to retrieve each value of ‘Price’ and if you can notice there is comma inserted before <?end if?> to show list of prices comma separated. Now for below input
You will get following output
To learn how to insert field and to get output, refer Create Your 1st Report with BI Publisher.
In above scenario we have seen the use of variable, how to declare it?, how to retrieve it? And some important functions like @inlines and position().
- Substr:
<?xdofx:if:substr(Price,2,4)!=”None” then Price else ” ?>
(Price,2,4) = For entity Price, from 2nd character next 4 characters (2nd character enclusive) are not equal to None then put Price’s value as it is otherwise ‘ ’ (Null).
- convert_case:
If ‘Price’ contains upper case ‘NONE’ then in this scenario we can modify above query as below.
<?xdofx: if convert_case(substr(Price,2,4),’UPPER’)=’NONE’ then ” else Price?>
Convert_case converts For entity Price, from 2nd character next 4 characters (2nd character enclusive) to upper case and compares.
Lets study some more function in detail.
Consider I have following XML
<book id=”bk101″>
<author>Gambardella, Matthew</author>
<title>XML Developer’s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<price>42.25</price>
<price>42.21</price>
<price>47.65</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications with XML.</description>
</book>
Now we can see from above XML that there is variation of values under same entity i.e. Price. Now lets see how BI publisher will react for this type of XML.
From above result we can see that it takes only first value for Price i.e. 44.95. If we want list it as variation in price we can’t do that. For that purpose we need to use some queries and all. We are going to learn how to use variable, how to put values in one line and some other functionalities too.
Consider we want the output in following format.
First of all we need identify how many times this variation of price is there i.e. we need to get count of that. In our case its 4. To get this value we will use following queries.
Here, ‘cnt’ is a variable name which is set to get number of values for ‘Price’. For that we have used ‘Count’ function. As we get this count, we need to use this to retrieve the values for price. For that consider following query.
<?for-each@inlines:price?><?.?><?if@inlines:position()!=xdoxslt:get_variable($_XDOCTX, ‘cnt’)?>, <?end if?> <?end for-each?>
In above code, we have used For-each function to retrieve each value of ‘Price’ and if you can notice there is comma inserted before <?end if?> to show list of prices comma separated. Now for below input
You will get following output
To learn how to insert field and to get output, refer Create Your 1st Report with BI Publisher.
In above scenario we have seen the use of variable, how to declare it?, how to retrieve it? And some important functions like @inlines and position().
No comments:
Post a Comment