Sum a calculated field on a report

[ChrisSedgwick]

Hi,

Is there a way I can sum a calculated field on a report?

I have a report that calculates the cost of miles travelled. Under the Detail section is a text box which totals the miles using

=Sum([Appointed Charge]

However, I'm looking for a way I can include a text box in the Report Footer that total each of the above fields.

Is this possible?

 

[pbaldy]

I'm not clear what you're trying to do. The same formula will work in the report footer.

 

[ChrisSedgwick]

It doesn't appear to. Gives me an 'Error' result.

I have a report that shows me the cost of the deliveries based on the mileage. It's a pretty basic report that works by listing all the jobs, the total miles travelled for each and gives me the appointed charge for each job (which is the cost per mile x total miles).

Under the detail footer I've used the Totals function to Sum the total appointed charge for each job. However I now want to calculate the total of all the Appointed Charge Totals so I have a Total figure at the bottom of the report.

As I said I select the totals text box and click the totals function from the toolbar, however the Sum function is greyed out and I cannot select it. That's what promoted me to find out if it was possible as I'm sure I've seen it done.

 

[pbaldy]

If it works in a group footer it should work in the report footer. Are you sure you have it in the report footer rather than the page footer? It won't work in the page footer.

 

[ChrisSedgwick]

Nope, it's still not letting me do it. It allows me to insert the text box and include the formula in the source. However when I open the report the text box shows 'Error'. I'm definitely inserting it in the report footer.

Could it be because the Appointed Charge field is calculated in the report? I could try calculating it in the query, however I'm not too sure how to create calculated fields in queries.

 

[pbaldy]

If Appointed Charge is a textbox containing a calculation, like Price * Quantity, then you certainly can't sum it. You'd have to do

=Sum(Price * Quantity)

but I guess I'm thrown off by it working in a group footer. You can move the calculation to the query. In design view:

Appointed Charge: Price * Quantity

 

[ChrisSedgwick]

Hi pbaldy,

After playing around with it last night, I managaed to successfully get the report to total up the Total Appointed Charge.

However, when I've run the report and calculated the figures myself, the total for the Delivery Allowance seems to be incorrect. I know what the issue is but I'm not sure the resolution.

I've attached a couple of screenshot so you're able to see the report. What's happening is that where a job has more than one entry, it's calculating the allowance for each entry.

For example, take the top job on the screenshot which is County Hospital Stafford. This job has a total allowance of £160 for the full delivery of that job. However the issue lies with having more than one entry, which in many cases would happen. So we delivery some parts of the job and then deliver the second or even third part later on. There is still only ever £160 for the delivery no matter how many times we deliver for that job. It's already been cost for at the quote stage. However if you look at the final figure at the bottom of the report, it adds up to more than what is shown on the report. Here I believe its multiplying the Group total by the number of entries on the job.

How can I get the Total Delivery Allowance in my Report Footer to calculate the entry shown in the Total Allowance (Group Footer) only once. Rather than for each entry on any one job?

Thanks,
Chris.

 

[pbaldy]

Is it possible to attach the db here?