subreport/report total question

  • Thread starter Thread starter Shanin
  • Start date Start date
S

Shanin

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
 
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]
 
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)




Allen Browne said:
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]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Shanin said:
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
Click to expand...
Click to expand...
 
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]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Shanin said:
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)




Allen Browne said:
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]

Shanin said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Allen Browne said:
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]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Shanin said:
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)




Allen Browne said:
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]

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
Click to expand...
Click to expand...
Click to expand...
 
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

Allen Browne said:
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]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Shanin said:
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)




Allen Browne said:
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]

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top