Conditional Data Manipulation

I have this challenge on a birt report I'm working on currently;

I have a database table called Transactions which has the following columns, Trans_id, Trans_amount, Trans_account_number, Trans_date. Now when I query the table I have records that seems to be repetitive and I would just love to aggregate them based on some conditions. SO for example say the table record looks like so

Trans_id Trans_amount Trans_account Trans_date
001 $5,070.90 Nominees 12/03/2011
002 $14,000.70 Commissions 12/03/2011
003 $55,000.00 Nominees 12/03/2011
004 $5,000.00 Expenditure 11/03/2011
005 $5,000.00 Nominees 11/03/2011
006 $25,000.00 Expenditure 11/03/2011
007 $15,000.00 Expenditure 10/03/2011
008 $105,000.00 Expenditure 10/03/2011
009 $25,000.00 Expenditure 10/03/2011
010 $15,000.00 Expenditure 10/03/2011

Now if you look at the record above you will notice that somethings just strikes you immediately; there are many transaction on the Expenditure Account for the same date. Now I don't want to display them as different line record on the report, I would rather add the Trans_amount, when the account and date are the same. So that the report would something like this;

Account Amount Date

Nominees $60,070.00 12/03/2011
Commissions $14,000.70 12/03/2011
Expenditure $5,000.00 11/03/2011
Nominees $5,000.00 11/03/2011
Expenditure $285,000.00 10/03/2011

I hope the problem is understood. I am using a scripted DataSource and I suspect this manipulation can be done on the fetch method of the dataset but I have not gotten it to work. Here the logic I tried;

Open Method

dataSources = reportContext.getPersistentGlobalVariable("dataSources");
dataList = dataSources.get("ROOT");
iter = dataList.iterator();

Fetch Method

if( iter.hasNext() )
{
myObject = iter.next();
if(row!==null && (row["Date"] === myObject.get("trans_date")) && (row["Account"] === myObject.get("trans_account"))){
row["Amount"] += myObject["Amount"];

}
else{
row["Date"] = myObject.get("trans_date");
row["Account"] = myObject.get("trans_account");
row["Amount"] = myObject.get("trans_amount");
}
return true;
}

else
{
return false;
}

And that's it. It never works. It just keep running else in the first if block.
I need help if anyone has an idea of how to deal with this please holla. I must say I am a newbie with to birt and my birt is version 2.3 (old right). Well, that's what I've got to work with now. So if anyone can help I am willing to try any idea.

Thanks guys.

Find more posts tagged with

Comments

johnw

Couldn't you add a grouping in the display table for date and account, or a computed column that is a combination of the two, and aggregate on the group level? This would take out having to do any sort of goofy calculations in your fetch and let the intrinsic aggregations and groups do their job.

yuneekguy

Hi johnw, thanks for the idea but to be honest, I'm not sure where exactly I would need to start. When you say a computed column of the date and account columns and then aggregate on the group level, can you explain please? Like I said, I'm new to birt reporting. I just got a hang on how grouping works. So for example I can group my report by say account name. But how do you group by two columns? Then the aggregate piece is another point you might help explain.

johnw

To make a Computed Column, you would either create one in the Data Set by going to the computed column tab, and setting the expression to something like:

row["Date"] + row["Account"]

This will create a logical extra column that is the concatenation of these two fields. There might be some conversion that needs to happen based on the datatypes. You can do the same thing at the Table by going to the Property Editor and Data Binding, and create a new Data Binding.

Once you have the Computed Column, you would then select the table, go to Groups, and create a new group based on the new computed column. This will group everything together that is the combination of that data and account number. Then, you can drag over an aggregation component from the Palette, set the group level to your new group level, and sum based on the amount field. Move all the data items from the detail row up to the group header row, delete the detail row, and it will just add up based on those accounts and dates.

That is the basic idea.