Sorting outer group based on inner group in BI Publisher

I spent almost a day to implement a grouping and sorting requirement and after all that brain storming I found that the requirement can be implemented by an elementary piece of code. I wish to share this with the rest of the world

Consider the following dataset

<top>

<data_node> <text_field>a</text_field> <num_field>5</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>3</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>6</num_field> </data_node>

<data_node> <text_field>a</text_field> <num_field>9</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>4</num_field> </data_node>

<data_node> <text_field>b</text_field> <num_field>7</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>2</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>8</num_field> </data_node>

<data_node> <text_field>c</text_field> <num_field>10</num_field> </data_node>

<data_node> <text_field>d</text_field> <num_field>1</num_field> </data_node>

<data_node> <text_field>d</text_field> <num_field>11</num_field> </data_node>

<data_node> <text_field>d</text_field> <num_field>2</num_field> </data_node>

</top>

The requirement is to group the text_field data together and to order it based on num_field. In the above XML data source, text_field ‘d’ has the smallest number value i.e. 1. So, according to the requirement, D should be displayed on the top with all its num_fields i.e. (1,2,11). After D, C should be  displayed since C has a num_field value of 2. C should again be followed by all its num_fields i.e. (2,8,10)

So the output should be

D

1,2,11

C

2,8,10

A

3,5,9

B

4,6,7

After a day of dealing with arrays, variables and tons of rearrangement, the following solution looked most elegant

<?for-each-group:data_node;./text_field?><?sort:current-group()/num_field;'ascending';data-type='number'?>

<?text_field?>

<?for-each@inlines:current-group()/num_field?><?sort:current-group()/num_field;'ascending';data-type='number'?><?.?>,

<?end for-each?>

<?end for-each-group?>

 

Magic is done by the for-each-group statement at the top. When the field on which grouping is done (text_field in our case) is different from the field on which sorting is done(num_field in our case), BI Publisher sorts the group-field based on the sort-field

The sort statement inside for-each:current-group() statement is to sort the num_field data inside each text_field group

Till next time