This is problem that I have struggled with for some time, but im sure the answer is very simple...
I have a sub report that performs calculations on 2 fields ( [Rate] [Hours Worked] ) from a table called [Hours].
The report is GROUPED by [Rate] and is as follows:
| | | | [Rate] | [Total Hours Worked] | [Total Billings] ============================================================ | =[Rate] | =Sum | =Sum | | ([Hours Worked]) | (([Hours Worked]* | | | [Rate]))
So some example data would look like:
| | | | | Rate | Total Hours Worked | Total Billings | =================================================== | £10 | 3 | £30 | | | | | =================================================== | £20 | 2 | £40 | | | | | =================================================== | £25 | 2 | £50 | | | | |
...And that all works fine, providing a summary for the TOTAL hours worked and thus TOTAL billings for each different rate of pay...
... But the problem is getting a total of [Total Billings] i.e. [GRAND TOTAL BILLINGS] on the sub report in a non-visible box so that it can be shown on the main report.
| | | | | Rate | Total Hours Worked | Total Billings | ======================================================== | £10 | 3 | £30 | | | | | ======================================================== | £20 | 2 | £40 | | | | | ======================================================== | £25 | 2 | £50 | | | | | ======================================================== | Grand Total Billings | | | | $120 | RED = Hidden
I have tried using a running sum Over Group =Sum(([Hours Worked]*[Rate])) for [GRAND TOTAL BILLINGS] but when i reference to it from the main form it will only show the total for the first rate group and not a true grand total of all the rate groups. I have also tried placing the same expression in the report footer (with and without running sum) but that gives the same result. Its a shame you cant just use =Sum([Total Billings]) like you can on a form, that would me much more simple!
Anyone got any ideas how I could modify this expression to give a grand total? :-/