Monday, March 26, 2012

Grouping dissimilar data

I��m trying to build a report that contains bank account activity.For 3 customers their activity is this:

Deposits

DateAmount

John1/2/2007500.00

1/7/2007250.00

Mary1/3/2007100.00

Withdrawals

DateAmount

John1/3/2007100.00

Mary1/2/2007100.00

1/4/2007200.00

1/6/200750.00

Sam1/6/200750.00

I would like the report to have deposit/withdrawal info, with subtotals, grouped by customer, looking something like this:

Deposit Withdrawal

Cust DateAmountDateAmount

John 1/2/2007500.001/3/2007100.00

1/7/2007250.00

750.00 100.00

Mary1/3/2007100.001/2/2007100.00

1/4/2007200.00

1/6/2007 50.00

100.00 350.00

Sam 1/6/2007 50.00

0 50.00

Because the deposit and withdrawal data is only related by customer, I��ve broken it into 2 datasets.

I��m totally stumped!Can someone get me going in the right direction on this?

Thanks

Hi,

You can't put 2 datasets in one dataregion. To do this, I think it is best to do a Full Outer Join on the two sets based on Customer and Date. This way you can have records like:

CUST DEP_DT DEP_AMOUNT WIT_DATE WIT_AMOUNT
John 1-2-2007 500 NULL NULL
NULL NULL 1-3-2007 100
1-7-2007 250 NULL NULL

Hope this gets you started.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

No comments:

Post a Comment