Sorting for work breakdown structure code

Hello,

currently I am trying to create some reports using BIRT (which I really like).

My problem is that I have not found a way to sort data on a key containing a work breakdown structure code in string format, e.g.:

1
1.1
1.1.1
1.1.2
1.1.3
2
2.1
2.2
3
3.1
3.2
3.2.1
3.2.2
...
10
10.1
10.2
11
11.1
11.2
11.2.1
11.2.2
etc.

This example gives the sequence I would like to get. Instead, sorting by this field results in a non numerical sequence starting with 1, 1.1, 1.2, etc, and then instead of moving to 2, 2.1, 2.2, etc. it moves over to 10, 10.1, 10.2, etc. based on the logic that when using a string, the correct sequence is 1,10,11,12,2,3...

I tried to go into the expression editor to somehow tweak my field into something which will sort, but I fear that this is not quite trivial. So I decided to tap into the brainpower this forum represents (and which already helped me quite some times just because someone already asked a question before - but this time, I could not get results from searching the forum).

Has anyone already solved that or can describe some solution I might overlook?

Thank you very much in advance and kind regards,
Frank

Find more posts tagged with

Comments

thuston

1.1.1 and 10.1.2 are both strings that would come before 2.1.1. 
You need either convert the strings into numbers or adjust the string so they will sort as desired. 
 
Both options require some knowledge of the data before hand. 
 
If you want sortable strings, you will have to add leading zero to each section. 
This would require you know the maximum number of characters for each section. 
EX. If the higest are (300).(26).(5) then the first would have to be 001.01.1 
 
If you want sortable numbers, I would do it as computed columns from your dataset. 
This requires you know the maximum depth of subsections 1.2.3.4.5... 
EX. If there are 5 maximum sections, you'd need 5 computed columns. 
Each would be a substring of the section converted to an Integer. 
<pre class='_prettyXprint _lang-auto _linenums:0'>//column2
var col = 2;
var loc =0;
var tmp = row["myStringValue"];
//get the desired set (col) to be the left
for ( var i = 1; i < col; i++){
loc = BirtStr.indexOf(".",tmp);
if ( loc > 0 ){
tmp = BirtStr.right( tmp, BirtStr.charLength(tmp) - loc );
}
}
//trim any remaining sets off of the right
loc = BirtStr.indexOf(".",tmp);
if ( loc > 0 ){
tmp =BirtStr.left( tmp, loc );
}
//convert to Integer and return
new Number(tmp);</pre>

thuston

Forgot to mention, once you have a computed column for each set, then you'd set up nested Group Sections in your table.
You might also need to adjust the sample to return 0 when the section does not exist (1.2 has no col=3)

FBrandau

Thanks a lot for your help. I took the approach using the additional computed columns each for one level of the work breakdown structure code. By counting the separators, I can check whether a section exists, otherwise I now return 0. There was a slight "unwanted feature" with the code regarding trimming, I corrected that, hopefully this code is doing the job now. 
 
My first code since quite some years... hope you people don't mind my attempt. 
 
Kind regards, 
Frank 
 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

// Identify the numerical value of a given section in a work breakdown structure code
var wbs = row["wbs_code"];
var section = 2
var pattern = /\./g; // Search for the '.' as a separator
var count = -1;
var location = -1;
var sectionvalue = 0;

// Count occurances of separator string in work breakdown structure code
if (wbs != null)
{
var matches = wbs.match(pattern);
if (matches != null)
{
count = matches.length;
}
else
{
count = 0;
}
}
else
{
count = -1;
}

// Number of occurances plus one must be at least equal or larger than section number
if (count+1>=section)
{
for (var i=1;i<section;i++)
{
location = BirtStr.indexOf(".",wbs);
if (location>0)
{
wbs = BirtStr.right(wbs,BirtStr.charLength(wbs)-location-1);
}
}

location = BirtStr.indexOf(".",wbs);
if (location>0)
{
wbs=BirtStr.left(wbs,location);
}

sectionvalue = new Number(wbs);
}

sectionvalue;

</pre>