Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Select record where maximum date and then sum Post Reply Post New Topic
Author Message
uakkumaa
Newbie
Newbie
Avatar

Joined: 28 Apr 2010
Location: New Zealand
Online Status: Offline
Posts: 4
Quote uakkumaa Replybullet Topic: Select record where maximum date and then sum
     Posted: 03 May 2010 at 5:30pm
Got a group of people who uses cards at the golf club to buy accessories and add money on their card. the transaction is recorded in sql database.
 
heres the sample of the report
 
Date   Time     transaction      debit           credit             balannce
 
John
***    ***         buy                  10.00                                20.00
***    ***         add value                            10.00             30.00
                          Total for John 10.00          10.00 
 
mary
***    ***            buy                 15.00                               45.00
***    ***            buy                 10.00                               35.00
                          Total for mary   25.00         0.00
etc
 
 
Then at the report footer we sum the totals of the debit and credits.
 
What i need to do is to find the formula for the opening balance which will be the first transaction balance plus debit minus Credit.(20.00+10.00-0.00=30.00 for John) which needs to be shown just above 20.00 for john.
The other formula Iam after is to sum the last record of the balance for each group in the report footer. eg. last record for john is 30.00 and last record for mary is 35.00  so the total in the report footer will be 65.00.
 
Iam new to crystal reports and dont know much of the basic formula. Iam working on the existing report which was created for other reasons so Iam trying to modify the reports .
I will really appreciate if I could get some help please
ak
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 04 May 2010 at 3:20am
the first formula isn't too hard:
shared numbervar first:={table.balance}+{table.debit}-{table.credit};
""//to hide the balance.
 
put the formula in the report header section, where it will calculate for the first record...john.
 
where you want the amount to print put a formula like:
shared numbevar first
 
the second...I would use shared variables, DBlank would use a running total...so my way:
in the group summary or the detail section depending on how you want to increment
summary:
shared numbervar CRtotal := CRtotal + sum({table.CR}, group);
shared numbervar DRtotal := DRtotal + sum({table.DR}, group);
"" //to hide the calculation
detail
shared numbervar CRtotal := CRtotal + {table.CR};
shared numbervar DRtotal := DRtotal + {table.DR};
 
then in you report footer 2 formulas:
shared numbervar DRtot
 
and
shared numbervar CRtot
 
HTH
IP IP Logged
uakkumaa
Newbie
Newbie
Avatar

Joined: 28 Apr 2010
Location: New Zealand
Online Status: Offline
Posts: 4
Quote uakkumaa Replybullet Posted: 04 May 2010 at 6:26pm
Iam sorry but I couldnot understand the second formula. Under the balance column I need to select the last record for each group then sum it at the end of the report.(report footer). My debit and credit columns works fine.Will the above formula do what I need. Thank for your help/
ak
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 05 May 2010 at 3:09am

Sorry, I thought that you were summing the credits and debits, not the balance. so let's revisit the second:

the second...I would use shared variables, DBlank would use a running total...so my way:
in the group summary 
shared numbervar balTotal := balTotal + rowBalance;//I don't know if it is a field or a calculation
 
then in you report footer 1 formula:
shared numbervar balTotal
 
 
what the formula is doing is just adding the last balance to a variable, and basically creating a running total
 
HTH
IP IP Logged
uakkumaa
Newbie
Newbie
Avatar

Joined: 28 Apr 2010
Location: New Zealand
Online Status: Offline
Posts: 4
Quote uakkumaa Replybullet Posted: 24 May 2010 at 1:28pm
Iam sorry but I still cant work this out. I will try to explain more in details here.
 
Heres the report which I get
 
John                                          debit           credit           balance
***    ***         buy                  10.00                                20.00
***    ***         add value                            10.00            30.00
***    ***         buy                  15.00                                15.00
 
                          Total for John 25.00          10.00 
 
mary
***    ***            buy                 15.00                               45.00
***    ***            buy                 10.00                               35.00
***    ***            add value                         20.00             55.00
 
                          Total for mary   25.00        20.00
 
etc
 
i need to add only the last record for each group in balance column. which means that i need to add 15.00 and 55.00 in the report footer.The balance is the column from the table. Please explain in detail of how to apply the formula.Iam getting confused when I use shared formula.
 
 I just tried this formula:
shared StringVar lastValue;
if (OnLastRecord) then
  lastValue := {table.Balance}
 
the problem with this formula is that it only works for the last group. I put this formula in the group footer.
 
Is there anything else I have to put in this formula.
 


Edited by uakkumaa - 24 May 2010 at 2:16pm
ak
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.