Saturday, 2 June 2012

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

1 comment:

Leslie Lim said...

This is really interesting and knowledgeable. Thanks for sharing. I really appreciate it a lot. Please do more blogs in the future. Thank you and God bless to the blogger!

www.imarksweb.org