Inital Balance

[#1]

Hi People:

I need calculate initial balance for accounts, with this structure.

The Data is:
Year	Month	Date	Account	Debit	Credit
2010	4	01/04/2010	E100	500
2010	4	09/04/2010	E100		300
2010	5	08/05/2010	E100	400
2010	5	16/05/2010	E101	120
2010	7	11/07/2010	E200	80
2010	7	19/07/2010	E100		30
2010	8	25/08/2010	E200	140
And need show ..
Year	Month	Account	beginning balance	Debit	Credit	book balance
2010	4	E100	0	500	300	200
2010	5	E100	200	520	0	720
2010	7	E100	720	0	30	690
	Total	E100	0	1020	330	690
2010	5	E101	0	120	0	120
	Total	E101	0	120	0	120
2010	7	E200	0	80	0	80
2010	8	E200	80	140	0	220
	Total	E200	0	220	0	220
	Grand Total		0	1360	330	-1690

Is possible? any idea?

Thanks! regards

César Estrada

[#2]

I think you should be able to use Inter-record functions to do it. Once you take the date field out, the Year and Month will be grouped, so your Debit and Credit expressions should work as is.

For Beginning Balance, it would be something like:

Above(Debit) - Above(Credit) + Above(BeginningBalance)

You may also be able to use Accumulation and have the Beginning Balance field be:

Above(Debit) - Above(Credit)

EDIT: Actually, the first expression should probably be:

Above(Debit) - Above(Credit) +
If(Above(BeginningBalance) > 0, Above(BeginningBalance), 0)

Then set your Null Symbol to 0.

[vgutkovsky · Accepted solution]

Cesar,

See attached solution.

Regards,

[#4]

Now try with this tips, Thanks!!

[#5]

Thanks!! work perfect!!