Aggregate 'duplicate' rows in a group based on visible columns

system_migrated

Hello,

I wish to create a report based on information served by a webservice. For this, I created a datasource and associated dataset. This is all functional, but I don't have the ability to do complex queries: i basically get a flat list of records and then must to my grouping/sorting in birt.

The data served consists of records that contain many different properties, among which a length.

The report I wish to make is the following:

1) Group on one property of the data
2) Within each group, add a group-footer that summarizes the total length within that group.
3) At the end of the report, a total of the length.

For flexibility, however, I have added both parameters to the report that allow the user to choose which columns to hide from his/her report, and on which property the grouping must occur.

My question: how do I ensure that there are no visible-duplicates within each group, but that those items are aggregated on their visible properties and their lengths added inside the group.

Let me give you an example to make things more clear.

I have following cars:

Name;Brand;Weight;Color;NumberOfWheels;Length

Veyron;Bugatti;2500;Red;4;4.2
Caprice;Chevrolet;3100;Blue;4;4.3
Bus;VanHooll;8900;Grey;6;12.9
MyCar;Ford;1000;White;4;3.4
MiniCar;Cheapass;489;Red;3;2.8
Chiron;Bugatti;1700;Red;4;4.2

Suppose I group on color I would get:

Red
Veyron Bugatti 2500 4 4.2
MiniCar Cheapass 489 3 2.8
Chiron Bugatti 1700 4 4.2
SUBTOTAL LENGTH: 11.2

...
(Yes, I realize it doesn't make much sense to add carlengths, my example is a bit farfetched, but my own domain is rather complex and takes away attention from the problem).

The above output is ok. However, If I hide the weight column and the carname, the report shows:

Red
Bugatti 4 4.2
Cheapass 3 2.8
Bugatti 4 4.2
SUBTOTAL LENGTH: 11.2

What I would like, however, is:


Red
Bugatti 4 8.4
Cheapass 3 2.8
SUBTOTAL LENGTH: 11.2

So basically, if all visible columns are identical within a group except for the lengths, I want those records to be merged and their lengths added. Any ideas how I can accomplish this? I've been thinking about this for a while, but I'm a bit stuck...

Thanks for your help!
Yves