total formula

[Cretin]

I am having a problem. I have a report I am creating from a customer database. The report is grouped by customer name. Within each customer there are a series of items the customer gets billed for. for our purpose we will call the fields cost1, cost2, cost 3 etc. I need to add those fields together to total it for each customer. I tried a formula cost1 + cost2 + cost3 and placed it in the group footer. The field was empty. I am not sure why.

Cretin

 

[glalsop]

Perhaps some of the costs are null? You might try setting 'Convert Null Values to Default' under File -> Report Options.

-Gary

 

[Cretin]

Very good I just figured out that was what is happening and thanks because I did not know how to fix that you were a great help

Cretin

 

[glalsop]

Glad you got it sorted out... Keep in mind that this will change ALL of the null values that you are pulling in, not just this field. An alternative is to test for the null values in a formula:

whileprintingrecords;

numbervar mytotal := 0;

if not isnull({table.cost1}) then mytotal := mytotal + {table.cost1} 
if not isnull({table.cost2}) then mytotal := mytotal + {table.cost2}

...etc

-Gary

 

[Cretin]

Thanks I think for my purposes it will work of course now I ran into another problem. When new data comes in that supersedes old data instead of editing ther record they add another record. For example lets say that in December of 2003 cost1 was entered in as $100.00. Now this year it is changed to $110.00. For some strange reason instead of editing the record they add a new record with the updated cost. Of course my report is showing both records. Is there a way to take a record with the most recent date?

Cretin

 

[lbass]

You can use a group select statement. Go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.customer})

...assuming that {table.customer} is your group field. After using a group select, you will need to use running totals for calculations since the non-selected records will contribute to the more usual summaries.

Another approach would be sort by table.date ascending and drag the cost fields and formula into the group footer and then suppress the details.

-LB

 

Thanks that helped a lot. I am sure its easy to tell I am a newbie to this have done it a while but it is getting more challenging.