Sum problem 1

[munchen]

I am using crystal reports 8.5 and I am connecting via ODBC to a Microsoft Access database and I am using a stored procedure.

The stored procedure is bringing back data like this:

Date DebtID ChequeAmount
01/01/04 20101 110.00
02/01/04 20345 90.00
03/01/04 20589 70.00
05/01/04 20101 110.00
06/01/04 20589 70.00

As you can see the stored procedure is in order by the date field and whenever a debt id is displayed more than once it displays the same cheque amount (eg 110.00 for debt 20101). This is correct and I need the report to remain sorted by the date field. However how can i sum (grand total) the cheque amount field but only sum the first returned amount for each debt?

EG - i would like my sum for the above to only be for the first occurrence of each debt id:
20101 110.00
20345 90.00
20589 70.00

Result = 270.00

 

[synapsevampire]

Group by the debt id.

Create a formula for the group header:

whileprintingrecords;
numbervar mysum:=mysum+{table.amount}

Now you can use the variable in the report footer:

whileprintingrecords;
numbervar mysum

-k

 

[MrBillSC]

This idea came from Ken Hamady's Underground News.

First create an automatic running total field that is a distinct count of {table.debtID} (let's call it {#CountId}. The distinct count running total field will not count the same debtID value more than once.
Next create a formula that adds in {table.chequeAmount} only when {#CountId} increments.

// formula {@CkAmtTotal}

WhilePrintingRecords;
NumberVar Prior;
NumberVar CkTot;
If {#CountId} = Prior + 1 then
CkTot:= CkTot + {table.chequeAmount};
Prior:= {#CountId};
CkTot

MrBill

 

[synapsevampire]

Interesting technique, MrBill, although it seems like more effort and will likely prove slower.

-k

 

[MrBillSC]

synapsevampire,
Ken's technique above is a "distinct sum" formula for summing a field only once when the data is scattered because of the sort or grouping. If kgarf needs to keep her report grouped by date, then this technique will allow her to sum the ChequeAmount only one time per DebtId.
MrBill

 

[lbass]

Great idea (*).

-LB

 

[kenhamady]

Thanks MrBill. For those who want more detail on the technique, my original article is here:

http://www.kenhamady.com/news0410.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[munchen]

Thanks to MrBillSC, synapsevampire and kenhamady for all your help. I appear to be almost there.

I inserted the formula in the details section and it is only summing a field only once when the data is scattered because of the sort order involved as i wanted. However I need the final figure to be displayed in the report footer section (a grand total) and the formula I have inserted in the details section will be suppressed. is there any way of using the kenhamady formula but displaying only the total in the report footer section?

 

[lbass]

As long as you don't have a formula resetting the check amt in any group header, you should be able to use a formula like the following for the report footer:

whileprintingrecords;
numbervar CkTot;

-LB

 

[kenhamady]

Add a separate formula in the Report footer that is:

WhilePrintingRecords;
NumberVar LYS;

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[munchen]

Thanks to lbass and kenhamady for this final formual. It is working perfectly now.

Many thanks to everyone involved.

 

[samcneal]

I haven't been able to get this work. Can someone help, please?

 

[samcneal]

I was able to get it to work, disregard previous posting. Thank you guys for posting valuable information.

 

[samcneal]

I have another problem with the running total, if the results include other records it doesn't provide a grand total. It gives me $0.00. Any suggestions?

 

[kenhamady]

I suggest that you start a new thread.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com