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? :-/

Success! I knew it had to be simple!

It turns out if you move everything on the report into the Rate Header (not the page header), and then put the [GRAND TOTAL BILLINGS] "=Sum(([Hours Worked]*[Rate]))" in the Detail frame it works a treat.

Just set the hight to 0 on the [GRAND TOTAL BILLINGS] text box properties and then shrink the frame until it disappears. Then you can reference to it using: =IIf([SUB Hours].Report.HasData,Nz([SUB Hours].Report.Total4,0),0) on the main report.

Marked as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.