Member Avatar for NuGG

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

Edited by NuGG because: formatting error
  • 1 Contributor
  • 1 Reply
  • 2K Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by NuGG
Member Avatar for NuGG

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, networking, learning, and sharing knowledge.