subreport/report total question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shanin - 26 Feb 2008 22:15 GMT

I've gone through past questions here and figured out how to get this to
work, but I was trying to make it a cleaner report and can't get this to work.

I have a Report with two subreports put in the detail section of the main
report. One subreport pulls schedule data, one pulls actual time worked.
The main report only pulls the "work_date" and "job", the two subreports are
linked by these two fields.

On the main report I have the report header, page header, job header, and
work_date header. Basically it sorts by job and then by date. I want the
total of subreports to be on the main report, and I have done that to an
extent.

I have the subreports having a work_date footer and totaling the daily
hours, it is called "daytotal". In the main report detail section I have a
field set to =[subreport].Report![daytotal] and set running sum to over all
and I have this field hidden. I have one for each sub-report. For the sake
of this it's named "runningtotal". I can then reference it in the Job footer
of the main report for a total for the job for the date span entered.

What'd I really like to do is get the day total in the main reports
work_date footer so that the totals from the two subreports are lined up
exactly side by side. I can get it to show the day totals if I set the
hidden field "runningtotal" in the detail section back to not calculating a
running sum and setting a field there equal to that. If I do this though, I
can't total by job anymore? Why can't I just reference the text box I made
in the main report work_date footer, say it's named "ScheduleTotal", and put
in the main reports job footer =Sum([ScheduleTotal])? This doesn't work of
course, but how can I get a total in the main report job footer if I move the
day totals to the main reports work_date footer?

I'm sure that sounds rambled, it's clear in my head but I'm sure not on paper.

Thanks

Reply to this Message

Allen Browne - 26 Feb 2008 22:26 GMT

The text box on the main report is fine.
You might want to use something like this:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)
Explanation:
http://allenbrowne.com/casu-18.html

If that one is named txt1, you could use a 2nd text box with properties:
Control Source =[txt1]
Running Sum Over All
Visible No
Name txtRS

Then in the Report Footer section, place a text box to show the grand total,
with Control Source of:
=[txtRS]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I've gone through past questions here and figured out how to get this to
> work, but I was trying to make it a cleaner report and can't get this to
[quoted text clipped - 43 lines]
>
> Thanks

Reply to this Message

Shanin - 28 Feb 2008 16:13 GMT

I've tried this, or at least something similar I believe, and I can't get the
job total to work, it just keeps a running total for every thing, it doesn't
break when the job does, and if I switch it to running sum over group, it
only shows the day total. Maybe I have the form set up wrong. Here is the
basic layout and maybe you can point me in the right direction.

Main Report: ActualvsSchedule
Report Header (Report title)
Page Header (headings/titles for data)
Job Header (contains Job and is set to break before each section)
Work_Date Header (contains work_date)
Detail (contains sub-reports Schedulesubreport and Actualsubreport, also
contains text boxes which are set equal to the hour totals in the work_date
footer of the sub-reports and are hidden)
Work_Date Footer (contains 3 text boxes for each sub-report, CN (day hours),
ON (night hours), DayTotal (all hours) which are set equal to those hidden
text boxes)
Job Footer-I've tried many different things here to get an job total, none
have worked so far

Sub-Reports
Report Header (blank)
Page Header (blank)
Detail (contains employee, time-in, time-out, department, hours, pay-type)
Work_Date Footer (contains 3 text boxes to total hours, CN (day), ON
(night), DayTotal)

I have tried adding a job footer in the sub-report as well as the job header
and Work_Date header and adding the 3 text boxes to total the hours by job,
and when opening the sub-report by itself, it will total correctly in the
sub-report, breaking by job. When I go to open in it the actual report, the
job totals always match the Work_Date totals because it gives me a job total
for each day.

I can get the day totals fine in the main report, but it's like it can't
figure out where the job ends since it will either mimic the day totals, or
it will just keep a running sum of everything.

I should note, this pulls off of a union query which combines the dates and
jobs from the schedule table (empschd) and the actual time worked table
(tktimcrd)

> The text box on the main report is fine.
> You might want to use something like this:
[quoted text clipped - 59 lines]
> >
> > Thanks

Reply to this Message

Allen Browne - 29 Feb 2008 00:26 GMT

The core idea is to accumulate each total at each level. Something like
this:

1. In the subreport, move your DayTotal text box into the Report Footer
section, so it collects all values in the subreport. (Set the report
footer's Visible property to No if you don't want it to take any space.)

2. In the main report, place a text box in the Detail section (i.e. the same
section as the subreport itself), and set these properties:
Format General Number
Visible Yes (I think you want so show this?)
Running Sum No
Name txtSub1Total
ControlSource =IIf([Sub1].Report.HasData,
Nz([Sub1].Report.RunningTotal, 0), 0)

3. Add another text box to the Detail section to accumulate the total.
Properties
Format General Number
Visible No
Running Sum Over Group
Name txtSub1TotalRS
ControlSource =[txtSub1Total]

4. Add a text box to the Work_Date Footer. Properties:
Format General Number
Visible No
Running Sum Over Group
Name txtSub1WorkDateRS
ControlSource =[txtSub1Total]

5. Add a text box to the Job Footer. Properties:
Format General Number
Visible Yes (I thinkg you want to show this)
Running Sum Over Group
Name txtSub1JobRS
ControlSource =[txtSub1WorkDateRS]

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I've tried this, or at least something similar I believe, and I can't get
> the
[quoted text clipped - 128 lines]
>> >
>> > Thanks

Reply to this Message

Shanin - 29 Feb 2008 15:25 GMT

I gave this a try but am still not having any luck. I kept all the boxes
visible so I could see what they were doing. If I set what you called
txtSub1TotalRS to Over Group, it gives me the exact same totals as
txtSub1Total. It's not summing them up. It will sum them if I change it to
Over All, but of course then it just keeps adding everything. The text box
in the work_date Footer will give a total, adding the previous day before it,
and then restarting on the next job (it's basically doing what a sum box in
the job footer should do). The text box in the job footer is giving me a
running sum over everything, it's not breaking at each job (it's basically
giving me a report sum). I don't understand why the text box in the detail
section set to sum over group (txtSub1TotalRS) is not summing over group but
just mimicking the one pulling the day total.

> The core idea is to accumulate each total at each level. Something like
> this:
[quoted text clipped - 166 lines]
> >> >
> >> > Thanks

Reply to this Message

Shanin - 29 Feb 2008 16:54 GMT

I decided to just make a query that totaled by job and created a sub-report
based off of that to put in the job footer. I'm still not certain as to why
the sum over group wasn't summing. Thanks for your help though

> The core idea is to accumulate each total at each level. Something like
> this:
[quoted text clipped - 166 lines]
> >> >
> >> > Thanks

Reply to this Message