X

An Oracle blog about BI Publisher

  • RTF
    May 26, 2006

Dynamic Sorting or Sort by Parameter

Guest Author

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 because $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
SALARY

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 is a different story to be told at a different time.

Klaus Fabian

Join the discussion

Comments ( 4 )
  • Nikki Wednesday, December 3, 2008
    i have been trying to do a sort similiar to this and it doesn't work when there is no data in the field.

    if i put a value in the sort, then it works correctly.

    any ideas?
    thanks.
  • Ritu Saturday, February 21, 2009
    Hey...great tip.
    I tried it and it works.
    However, I need this scenario: I have a table in a report and depending on some parameter, I need to change the sort criteria. I have the following code:

    When, CP_ORDER_BY='Hold Name', this works great. In the 2nd case, it doesnt sort.
    Also note, when I replace the sort statement with

    it again works fine.
    I think it is some problem with the way I am assigning values to 'order' and 'type'.
    This is urgent. Please advise.
    Ritu
  • tshirt Wednesday, August 18, 2010
    Interesting reading
  • Dodie Scheible Sunday, September 26, 2010
    Listerine really doesn't do anything to reduce decay or gum disease and it has too much alcohol in it which dries out your gum tissue. If you keep your teeth free of plaque, sweets will not hurt them. Plaque needs to mature for 24 hoursm before it can really cause har, so if youare doing a good job brushing and FLOSSING you should be ok. Actually it is better to brush before sweets to remove the damaging plaque. Plaque plus sugar creates the acid that causes decay. Also, it's not how much sugar you eat, it's the length of exposure to sugar that is important. So eating all your sweets at once is better then eating them slowly all day. The same thing goes for sugary drinks. Sipping them all day is really worse than drinking it all at once.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.