Advance BI Publisher Concepts - Sorting in RTF Template

The objective of this post is to explain the “sorting” capabilities available in the RTF. Many might already be aware of the standard BI Publisher syntax for sorting in the RTF template:

The above construct sorts the data by the VENDOR_NAME in ascending order – an alphabetic sort is performed. However, what if the requirement was to sort on a number field. For example, if you want to sort by the VENDOR_NUMBER, then the above will produce incorrect results as an aphabetic sort would be applied to a numeric field. The following BIP construct can be used to specifc the order and the sort type:

The above BIP construct performs a numeric sort on the vendor_number. That's rather simple to do. Let's say we would like to sort on a field but the field name is not known at design time. The field name exists in the xml data. Let's say the element P_SORT_BY specifies the field on which sorting must be done. So, if the value of P_SORT_BY is VENDOR_NAME, sorting must be performed on the field VENDOR_NAME. This can be acheived by using the following XSL:

Let's try to decipher the above xsl syntax. The above XSL has three parts – select, order and data-type. While order and data-type are self explanatory, the Xpath in the select actually does the magic. Let's try to understand this XPath in detail. The function node() returns an node. Specifying just node() will return all the nodes in the XML data. In order to filter, square brackets [] are used and the appropriate filter condition are specified inside. The call local-name() returns the name of the node. Therefore, the condition local-name()=P_SORT_BY will return all nodes whose name matches the value specified by P_SORT_BY.

The “order” and “data-type” fields can also use xdoxslt functions in them. Thus, if the sort type is known at run time, the following can be used to set the sort type at runtime:

The above sorts by the field whose value is specified by the P_SORT_BY and the sorting type is decided based on the variable sort_datatype. Similarly, the order can also be obtained from a xdoxslt variable.