Totaling Multiple Subreports

[Pam]

Hi,

I have a main report with four subreports and three of those have subreports
on them. All are linked by salesperson and I need a grand total of each
department from subreport1, subreportof2, subreportof3 and subreportof4.
I'm not sure how to go about this as everything I've tried has not worked -
name error, same total repeated for each department, etc. The fields on each
summary report in the department group are deptname and deptsumgp07,
deptsumgp08 and deptsumgp09. For clarity (I hope), I 've liste the
following setup:

Main report: Groups Salesperson, Customer, Department
Subreport1: Summary totaling Department group

Subreport2: Groups Salesperson, Customer, Department
SubreportOf2: Summary totaling Department group

Subreport3: Groups Salesperson, Customer, Department
SubreportOf3: Summary totaling Department group

Subreport4: Groups Salesperson, Customer, Department
SubreportOf4: Summary totaling Department group

If anyone can help with this, I would greatly appreciate it. I've spent a
considerable amount of time and am at a loss how to pull all this together.
Thanks in advance,
Pam

 

[Marshall Barton]

I have a main report with four subreports and three of those have subreports
on them. All are linked by salesperson and I need a grand total of each
department from subreport1, subreportof2, subreportof3 and subreportof4.
I'm not sure how to go about this as everything I've tried has not worked -
name error, same total repeated for each department, etc. The fields on each
summary report in the department group are deptname and deptsumgp07,
deptsumgp08 and deptsumgp09. For clarity (I hope), I 've liste the
following setup:

Main report: Groups Salesperson, Customer, Department
Subreport1: Summary totaling Department group

Subreport2: Groups Salesperson, Customer, Department
SubreportOf2: Summary totaling Department group

Subreport3: Groups Salesperson, Customer, Department
SubreportOf3: Summary totaling Department group

Subreport4: Groups Salesperson, Customer, Department
SubreportOf4: Summary totaling Department group

If anyone can help with this, I would greatly appreciate it. I've spent a
considerable amount of time and am at a loss how to pull all this together.

You lost me about what's in the main report and where you
want the total to appear.

You should be able to get a value from your subsubreports in
a main report yrccy box by using this kind of reference:
=IIf(Subreport1.Report.SubreportOf1.Report.HasData,
Subreport1.Report.SubreportOf1.Report.report1totaltextbox,
0) + IIf(Subreport1....) + ...

 

[Pam]

Marsh,

Thank you for replying and I'm sorry for the confusion. I tried your
reference in a couple of footers and cannot make it calculate as I need it
to be. I will try again to explain what I need to do and hopefully you will
have time to look at it again.

I have a sales db where info is grouped by salesperson, customer for
salesperson and then department for each customer for salesperson where
tinvoice customer and shipto match tacctlist customer and shipto. Ex:
Main Report: Salesman: Bob
Customer: ABC Co.
Dept: Units GP07 ($$)
Parts GP07 ($$)
Repairs GP07 ($$)
Customer XYZ Co.
Dept: Units GP07 ($$)
Parts GP07 ($$)
Repairs GP07 ($$)

I have a summary subreport in the salesman footer that totals only the
departments for Bob.
Main Report Summary:
Units SumGP07 ($$)
Parts SumGP07 ($$)
Repairs SumGP07 ($$)

Under this summary, I have three subreports with same layout as above
including summary for each.
1st is CommissionSales1
2nd is CommissionSales2
3rd is where tinvoice shipto does not match tacctlist shipto

I need to have an overall total for each department from the four reports.
Below is expression I used on the main report. I tried it in several
different footers and still can't get it to calculate as needed. Can you
please tell me if what I'm trying to do is even possible or if there is a
better way to go about this?

=iif([MainReportSummary].Report.HasData,
[MainReportSummary].Report.[SumOfGP07],0)+iif([CommSales1].Report.[CommSales1Summary].Report.HasData,
[CommSales1].Report.[CommSales1Summary].Report.[SumOfGP07],0)+iif([CommSales2].Report.[CommSales2Summary].Report.HasData,
[CommSales2].Report.[CommSales2Summary].Report.[SumOfGP07],0)+iif([NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.HasData,
[NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.[SumOfGP07],0)

Thanks again for your time and help.
Pam

 

[Marshall Barton]

Thank you for replying and I'm sorry for the confusion. I tried your
reference in a couple of footers and cannot make it calculate as I need it
to be. I will try again to explain what I need to do and hopefully you will
have time to look at it again.

I have a sales db where info is grouped by salesperson, customer for
salesperson and then department for each customer for salesperson where
tinvoice customer and shipto match tacctlist customer and shipto. Ex:
Main Report: Salesman: Bob
Customer: ABC Co.
Dept: Units GP07 ($$)
Parts GP07 ($$)
Repairs GP07 ($$)
Customer XYZ Co.
Dept: Units GP07 ($$)
Parts GP07 ($$)
Repairs GP07 ($$)

I have a summary subreport in the salesman footer that totals only the
departments for Bob.
Main Report Summary:
Units SumGP07 ($$)
Parts SumGP07 ($$)
Repairs SumGP07 ($$)

Under this summary, I have three subreports with same layout as above
including summary for each.
1st is CommissionSales1
2nd is CommissionSales2
3rd is where tinvoice shipto does not match tacctlist shipto

I need to have an overall total for each department from the four reports.
Below is expression I used on the main report. I tried it in several
different footers and still can't get it to calculate as needed. Can you
please tell me if what I'm trying to do is even possible or if there is a
better way to go about this?

=iif([MainReportSummary].Report.HasData,
[MainReportSummary].Report.[SumOfGP07],0)+iif([CommSales1].Report.[CommSales1Summary].Report.HasData,
[CommSales1].Report.[CommSales1Summary].Report.[SumOfGP07],0)+iif([CommSales2].Report.[CommSales2Summary].Report.HasData,
[CommSales2].Report.[CommSales2Summary].Report.[SumOfGP07],0)+iif([NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.HasData,
[NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.[SumOfGP07],0)

I would expect that to work, but ONLY in the same section
(salesman footer?) as the subreports. If you want that
somewhere else, please explain.

 

[PamH]

Marsh,

I have tried putting the calculation in the salesman footer and in the
department footer and neither will give a break down of the department groups
as they are on the summaries. I need a grand total of each department from
each summary. I tried using another report with just salesman and department
descriptions as the groups, placing just the summaries in the department
footer and then inserting the calculation (mainsummary.report.has data,
mainsummary.report!gp09,0) in the footer, but it only returns the first entry
in the department group in that particular summary.

Is this even possible? Is there another alternative I should try?

Pam

Thank you for replying and I'm sorry for the confusion. I tried your
reference in a couple of footers and cannot make it calculate as I need it

[quoted text clipped - 38 lines]

[CommSales2].Report.[CommSales2Summary].Report.[SumOfGP07],0)+iif([NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.HasData,
[NoMatchAcctList].Report.[NoMatchToAcctListSummary].Report.[SumOfGP07],0)

I would expect that to work, but ONLY in the same section
(salesman footer?) as the subreports. If you want that
somewhere else, please explain.

 

[Marshall Barton]

I have tried putting the calculation in the salesman footer and in the
department footer and neither will give a break down of the department groups
as they are on the summaries. I need a grand total of each department from
each summary. I tried using another report with just salesman and department
descriptions as the groups, placing just the summaries in the department
footer and then inserting the calculation (mainsummary.report.has data,
mainsummary.report!gp09,0) in the footer, but it only returns the first entry
in the department group in that particular summary.

Is this even possible? Is there another alternative I should try?

Well, I still don't understand what you want so I can't say
if it's possible or not.

Originally, you said you had the four subreports in the
salesman footer and the text box with the long expression in
the same footer. I would expect the long expression you
used to work to get a total for the salesman.

Now, you are talking about some kind of department summary
and I have no idea what that means. If you want some kind
of thing in the salesman footer that lists all departments
with their subtotal, then that's a different problem that I
have not been able to deduce any details about.

OTOH, if you have a department subreport in the
salesman-department group footer, then another text box in
the department footer can use a similar expression to bring
the department total to the main report. Then you can use
that text box's RunningSum property to add up the total for
all the departments for the salesman.

 

[PamH via AccessMonster.com]

I'm sorry my explanations aren't clear enough. You've provided some excellent
help with past problems and I really appreciate it. Please let me try once
more to explain. Originally, I had a summary in the salesman footer that
would summarize by department all his sales. I had only one summary and it
worked well. Things have changed and now I have three additional summaries
grouped by department for three different types of sales. What I need is a
grand total for each department from all the summaries regardless of type of
sale is falls under. The reason I said I had it in salesman footer is
because that is where the subreports of summaries were originally. The total
would not give me the department totals I needed there, so I tried putting
them in department footer. It doesn't work either. The calculation 'if
report has data, return data from field' gives only the first department
amount.
I hope this makes sense.
Again, I thank you for your time and help.
Pam

I have tried putting the calculation in the salesman footer and in the
department footer and neither will give a break down of the department groups

[quoted text clipped - 6 lines]

Is this even possible? Is there another alternative I should try?

Well, I still don't understand what you want so I can't say
if it's possible or not.

Originally, you said you had the four subreports in the
salesman footer and the text box with the long expression in
the same footer. I would expect the long expression you
used to work to get a total for the salesman.

Now, you are talking about some kind of department summary
and I have no idea what that means. If you want some kind
of thing in the salesman footer that lists all departments
with their subtotal, then that's a different problem that I
have not been able to deduce any details about.

OTOH, if you have a department subreport in the
salesman-department group footer, then another text box in
the department footer can use a similar expression to bring
the department total to the main report. Then you can use
that text box's RunningSum property to add up the total for
all the departments for the salesman.

 

[Marshall Barton]

Let's stop talking about what you used to have and only
discuss what you are trying to achieve. Your use of the
word "summary" is not definitive enough for my mind to get a
grip on what you are trying to display in the various footer
sections.

I suspect that you need a new subreport in the salesman
footer to display the totals for all the departments.
Remember that the total calculations can be done in a query
used as the subreport's record source. This would be
similar to the subreports you already have, just grouping by
fewer fields.

I'm sorry my explanations aren't clear enough. You've provided some excellent
help with past problems and I really appreciate it. Please let me try once
more to explain. Originally, I had a summary in the salesman footer that
would summarize by department all his sales. I had only one summary and it
worked well. Things have changed and now I have three additional summaries
grouped by department for three different types of sales. What I need is a
grand total for each department from all the summaries regardless of type of
sale is falls under. The reason I said I had it in salesman footer is
because that is where the subreports of summaries were originally. The total
would not give me the department totals I needed there, so I tried putting
them in department footer. It doesn't work either. The calculation 'if
report has data, return data from field' gives only the first department
amount.

I have tried putting the calculation in the salesman footer and in the
department footer and neither will give a break down of the department groups

[quoted text clipped - 6 lines]

Is this even possible? Is there another alternative I should try?

Well, I still don't understand what you want so I can't say
if it's possible or not.

Originally, you said you had the four subreports in the
salesman footer and the text box with the long expression in
the same footer. I would expect the long expression you
used to work to get a total for the salesman.

Now, you are talking about some kind of department summary
and I have no idea what that means. If you want some kind
of thing in the salesman footer that lists all departments
with their subtotal, then that's a different problem that I
have not been able to deduce any details about.

OTOH, if you have a department subreport in the
salesman-department group footer, then another text box in
the department footer can use a similar expression to bring
the department total to the main report. Then you can use
that text box's RunningSum property to add up the total for
all the departments for the salesman.

 

[PamH via AccessMonster.com]

Marsh,

I finally have it working as needed!! Thanks for the tip about the query
calculations. I usually try to do all my grouping calculations on the report,
but in this case it had to be done thru a series of queries. You made me
think outside the box (or report)!!
Sorry for the confusion - I guess what may appear clear to one, really isn't
to another.
Thanks again.
Pam

Let's stop talking about what you used to have and only
discuss what you are trying to achieve. Your use of the
word "summary" is not definitive enough for my mind to get a
grip on what you are trying to display in the various footer
sections.

I suspect that you need a new subreport in the salesman
footer to display the totals for all the departments.
Remember that the total calculations can be done in a query
used as the subreport's record source. This would be
similar to the subreports you already have, just grouping by
fewer fields.

I'm sorry my explanations aren't clear enough. You've provided some excellent
help with past problems and I really appreciate it. Please let me try once

[quoted text clipped - 36 lines]