Group consecutive row number

praposo

I had a query which I wanted BIRT to group if it was a consecutive row, else it work break and start a new group.

Find more posts tagged with

Comments

mwilliams

Hi pt99,

Can you explain further what you're looking for? Maybe include a small sample of what you're looking for.

praposo

I have a sql query and with birt I added a computed column that acts a row number. Then I have a staff id, I would like to group by staff id, but only if the row numbers are consecutive

Row id
===============
1 500
2 500
3 610
4 610
5 500

result

group A
1 500
2 500

group B
3 610
4 610

group C
5 500

mwilliams

pt99,

What you'll want to do is create another computed column that the output is initialized to 0 and increments everytime the current staff id is different than the last staff id. Then you can group by this column. This way you'll end up with the following as your data:

Group Row ID

=================

0 1 500
0 2 500
1 3 610
1 4 610
2 5 500

And this as your result of the grouping:

group A
0 1 500
0 2 500

group B
1 3 610
1 4 610

group C
2 5 500

Obviously you wouldn't have to show the "Group" information.

Hope this helps.

praposo

Would I be able to that inside of birt as a computed column, and if so how would I go about it?

I tried doing something similar on the report table, but was unable to get it working.

thanks

mwilliams

pt99,

Yes, you'd do it within a computed column. In the BeforeOpen method of the dataSet, put:

last = null;
groupCount = 0;

Then, in a computed column, choose type integer and put the following as the expression:

if (last == null){
last = row["CUSTOMERNUMBER"];
groupCount;
}
else if (row["CUSTOMERNUMBER"] == last){
groupCount;
}
else{
last = row["CUSTOMERNUMBER"];
groupCount++;
groupCount;
}

This should work. Hope it helps.