Oracle BI Publisher Blog

When you create a simple report that contains for example a single table, many users would like to have a single template that gets the column by which the table is sorted as a parameter - so that the end user can easily change it. Lately, I got this functionality working  and thought I share it.First you will need to define a parameter:

<xsl:param name="order" select="'SALARY'" xdofo:ctx="begin"/>

Next you will create a table that is sorted for example by name This can be accomplished easily with the table wizard. The resulting for-each and sort statements are:

<?for-each:ROW?>
<sort:NAME;'ascending';data-type='text'?>

So in a first attempt we would replace NAME with $order to use the parameter to sort:

<?for-each:ROW?>
<?sort:$order;'ascending';data-type='text'?>

This does not work? Why? Well, $order contains a string, while the <?sort:$order ?> statement which is translated into a <xsl:sort select=?$order?/> expect a node as a parameter and not a name. So how do we get the SALARY node from a variable that contains the text 鉄ALARY??

We can get the node by adding a predicate using the [] syntax. We first use the expression that matches any childnode: ./*. Now we add the condition that the name of the child node equals the content of the variable $order. The resulting expression is:

./*[name(.) = $order].

So we replace the NAME not with $order but with ./*[name(.) = $order] and achieve

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='text'?>

This worked fine, with one caveat: It always sorts alphanumerical and not numerical - meaning 1000 comes before 2. You will need to change the data-type to achieve correct sorting. You could achieve that with an if statement to set the data-type depending on the field name and run into some other issues or pass the type as a parameter.

If you want to use a parameter (e.g. with the name type) in for the data-type, you need to know that this field expects a string and not a node. If you write:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='$type'?>

your sorting will fail, because it will use the name of the variable $type as the data type and not its content. So you will need to user {} around the variable to substitute an XPATH expression or a variable inside the text. The final expression is:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='{$type}'?>

and your dynamic sorting should work like a charm. Unless you want to use an if statement to set the data-type depending on the parameter name - but that again is a different story to be told at a different time.

Klaus Fabian