Sum Nos in Reports

[PaulJK]

Hello,

Please bear with me in my explanation. I have a report which summarises appointments for salespersons.

There are headers for the Sales persons name and then the Lead Type and below this there is summary number for three types of appointment: Held, Sold & Other.

It looks like:

Name # 1
Lead Type # 1
Held: 6
Sold :7
Other: 3

The report provides a summary numbers rather than the individual appointment details.

There are footers for the Appointment Status, Lead Type and Sales Person, summarising the numbers.

My problem is that I want to calculate a percentage sold. This would be Sold/(Held & Sold)*100. I do not want to include the Other in the calculation.

I the Appointment Status footer I have created some hidden fields which shown the numbers held & sold to exclude the Other numbers ie:

HeldNum = IIf([RevApptStatus]="Held",[SumOfReVApptStatusCount],"0")
SoldNum =IIf([RevApptStatus]="Sold",[SumOfReVApptStatusCount],"0")

However, when I want to shown this is the Lead Type and Sales Person footer, I experience the problems.

I have tried HeldNum1=Sum([HeldNum]) as a hidden field & then a box which calculates the percentage i.e. =SoldNum1/HeldNum1+SoldNum1*100

I keep getting asked for HeldNum1.

I cannot see how I would be able to change the underlying query

Any guidance would be appreciated.