Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Opening/running balance with transaction Post Reply Post New Topic
Page  of 3 Next >>
Author Message
mak101
Newbie
Newbie


Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
Quote mak101 Replybullet Topic: Opening/running balance with transaction
    Posted: 06 Dec 2011 at 9:55am
I need to print customer transaction report for given date range with opening balance and balance at end of each month. I have transactions beginning from 2005 but user can ask for any later dates also.
 
Same client can have transactions in different regions and I would like to print opening balance for each region and closing balance for each region.
 
I wrote stored procedure which insert opening balance for all region and opening balance for each region in transaction results. Here is the sample data returned by my report stored procedure.
 
ClientId ClientName Region Date Desc Amount
1 John Smith   8/1/2011 Op Bal 200
1 John Smith North 8/1/2011 Op Bal 125
1 John Smith North 8/2/2011 Item Desc -10
1 John Smith North 8/5/2011 Item Desc 20
1 John Smith North 8/25/2011 Item Desc 25
1 John Smith North 9/15/2011 Item Desc 152
1 John Smith South 8/1/2011 Op Bal 75
1 John Smith South 8/15/2011 Item Desc 35
1 John Smith South 9/7/2011 Item Desc -15
1 John Smith South 9/30/2011 Item Desc 10
2 Mary Smith   8/1/2011 Op Bal 325
2 Mary Smith North 8/1/2011 Op Bal 0
2 Mary Smith North 8/15/2011 Item Desc 50
2 Mary Smith North 9/27/2011 Item Desc 34
2 Mary Smith South 8/1/2011 Op Bal 325
2 Mary Smith South 8/20/2011 Item Desc 30
2 Mary Smith South 9/9/2011 Item Desc 50
2 Mary Smith South 10/4/2011 Item Desc 45

For this data I would like to design report having format like this
1 John Smith
Region Date Trans Details Amount
Op Bal 200.00
North Op Bal 125.00
8/2/2011 Item Desc -10.00
8/5/2011 Item Desc 20.00
8/25/2011 Item Desc 25.00
End of Aug'11 Balance 160.00
9/15/2011 Item Desc 152.00
End of Sept'11 Balance 312.00
North Closing Balance 312.00
South Op Bal 75.00
8/15/2011 Item Desc 35.00
End of Aug'11 Balance 110.00
9/7/2011 Item Desc -15.00
9/30/2011 Item Desc 10.00
End of Sept'11 Balance 105.00
South Closing Balance 105.00
John Smith's Closing Balance 417.00
2 Mary Smith
Region Date Trans Details Amount
Op Bal 325.00
North Op Bal 0.00
8/15/2011 Item Desc 50.00
End of Aug'11 Balance 50.00
9/27/2011 Item Desc 34.00
End of Sept'11 Balance 84.00
North Closing Balance 84.00
South Op Bal 325.00
8/20/2011 Item Desc 30.00
End of Aug'11 Balance 355.00
9/9/2011 Item Desc 50.00
End of Sept'11 Balance 405.00
10/4/2011 Item Desc 45.00
End of Oct'11 Balance 450.00
South Closing Balance 450.00
Mary Smith's Closing Balance 534.00
I hope data and report output will help you understand better. Can anyone please help me how to design this report.
 
Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Dec 2011 at 10:38am
assuming i understand your set up
group on client
group on region
group on transaction date set to month (gh is suppressed)
show detail row for transaction data
use a running total or shared variable to display your calculations
 
1 John Smith
Region Date Trans Details Amount
Op Bal 200.00
North Op Bal 125.00
8/2/2011 Item Desc -10.00
8/5/2011 Item Desc 20.00
8/25/2011 Item Desc 25.00
End of Aug'11 Balance 160.00
9/15/2011 Item Desc 152.00
End of Sept'11 Balance 312.00
North Closing Balance 312.00
South Op Bal 75.00
8/15/2011 Item Desc 35.00
End of Aug'11 Balance 110.00
9/7/2011 Item Desc -15.00
9/30/2011 Item Desc 10.00
End of Sept'11 Balance 105.00
South Closing Balance 105.00
John Smith's Closing Balance 417.00


Edited by DBlank - 06 Dec 2011 at 10:41am
IP IP Logged
mak101
Newbie
Newbie


Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
Quote mak101 Replybullet Posted: 06 Dec 2011 at 11:00am
Thanks for quick resonse and I understand the grouping but never used shared variable and its operation. Can you please explain in details how to setup this?
 
Thanks again.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Dec 2011 at 11:15am
I would use a Running Totals (many others prefer variable formuals-lockwelle will likely chime in on that code)
RT as
name=region_sums
field to summarize=amount
evaluate=for each record (unless you have dupes)
reset=on change of group (select group2-region)
 
if you place this in the detail section or group footers you will see it be your ledger without the opening balance.
create another fomrula field as
{sp.open_balance_region}-{#region_sums}
balance - the opening balance for the region.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Dec 2011 at 11:18am
your client total will be another running total that is reset at the client level and you use your opening balance for clinet - that RT
 
 
RT as
name=client_sums
field to summarize=amount
evaluate=for each record (unless you have dupes)
reset=on change of group (select group1-client)
 
display formula as
{sp.open_balance_client}-{#client_sums}
 
 
IP IP Logged
mak101
Newbie
Newbie


Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
Quote mak101 Replybullet Posted: 06 Dec 2011 at 11:45am
I undestand the grouping, but can you please explain step by step how to usethis? Also need to print Client opening balance and region opening balance in thier respective group for format purpose (bold,italic, etc...) how do I do this?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Dec 2011 at 11:57am

does your raw data set look like this (for john smith only as an example)?

date region client amount clientopenblance regionopenblance
8/2/2011 North
john smith
 -10 200 125
8/5/2011 North
john smith
 20 200 125
8/25/2011 North john smith 25 200 125
9/15/2011 North john smith 152 200 125
8/15/2011 South john smith 35 200 75
9/7/2011 South john smith -15 200 75
9/30/2011 South john smith 10 200 75
 
 
if so, you first set up your groupings. They must exist for your running totals to work (same as if you used shared variables).
from there create the Running Total feilds.
They are in the Field Explorer.
Place them in the report to test and make sure they work as expected. You can remove them later.
then create the final formula fields that use the RTs and your starting client and region values. Place these formula fields in the correct footers and headers areas you want hem displayed at.
Then you can format these fields the way you want with italics and bolding by right clicking on them and selecting format.


Edited by DBlank - 06 Dec 2011 at 12:02pm
IP IP Logged
mak101
Newbie
Newbie


Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
Quote mak101 Replybullet Posted: 06 Dec 2011 at 12:13pm

OK Grouping is done and figured out running total as you suggested. But my first record for each client set is opening balance and first record for each group is group opening balance, I see  regional opening balance is added twice in client total. Any suggestion?

Meantime I will try to use formulas in headers and footers to see result differences?
 
Highly appreciated your help and it gives me new direction.
 
Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Dec 2011 at 12:18pm
did you join the regional total on the client only (would result in 2 records for john smith example and also double all of your detail records) or the client and the region (which would result in removing duplicate rows that you see)
IP IP Logged
mak101
Newbie
Newbie


Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
Quote mak101 Replybullet Posted: 07 Dec 2011 at 5:02am

All transactions are in one table and I injected opening balance as transaction record for each client and then each region for the client. If you notice my sample data, John has opening balance 200 which includes 125 opening balance of North region and 75 opening balance for South region. I also added north and south opening balance separately. Here is the snap shot of sample data.

ClientId ClientName Region Date Desc Amount
1 John Smith   8/1/2011 Op Bal 200
1 John Smith North 8/1/2011 Op Bal 125
.
.
.
1 John Smith South 8/1/2011 Op Bal 75
Please note region is empty on first record, means its grand opening balance for the client.
 
Any advise please?
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.