Prev: Printing only fields that contain data
Next: Report title based on Date?
From: rickr on 22 Apr 2010 13:35
That definitely did the trick. Thank you ever so much.

Sometimes it is the simple things that really cause the most difficulties. I
truly appreciate your help.

So, for those that may want a solution recap:
If you have a report with a sub report, and need to use values from the
sub-report in order to calculate total report values, follow this procedure:
1. For each sub-report value that needs to be referenced on the main report
totals
a. Create a text-box and place it on the main detail section, name it
something useful like 'col_1_Sum'
b. In the Control Source, reference the sub-report data element like so:

=IIf([Sub-Report_Name].Report.HasData,Nz([Sub-Report_Name].Report.column1,0),0)
c. Set the text-box 'Running Sum' property as necessary (I created 2
text boxes, 1 for the primary group and the second for the 'Over All' option)
d. Optional: Make the text-box NOT Visible
2. In the Report Footer, set the text-box where the total is going to be
displayed like this (using the text box with Over All running sum):
= Sum([col1]) + [col_1_sum]

Works like a charm.

Thanks again.

"Marshall Barton" wrote:

> Sorry, I was so focused on the subreport reference that I
> missed the thee fact that you were using Sum to try to total
> the subreport values. That won't work because the aggregate
> functions (Count, Sum, etc) can only operate on revord
> source fields, they are unaware of controls in the report.
>
> Instead you should use a running sum text box (named
> txtRunTotal) in the same section as the subreport. The text
> box's expression would be like:
> =IIf([Product - Pegged].Report.HasData, [Product -
> Pegged].Report![PegStdLabor], 0)
>
> Then the report footer textbox can use the expression:
> =Sum([StdLabor]) + txtRunTotal
> --
> Marsh
> MVP [MS Access]
>
>
> rickr wrote:
> >Unfortunately, that does not work. When I run the report it prompts me for the
> >Product - Pegged]Report.HasData and also for the [Product -
> >Pegged].Report![PegStdLabor] value.
> >
> >This is the thing I have been fighting for 2 days now.
> >
> >"Marshall Barton" wrote:
> >> rickr wrote:
> >> >Here is a scenario from my report:
> >> >The report will grab data with a main query into a parent row
> >> >Depending on the data, a child row may exist, if it does the report will
> >> >display it
> >> >The child row will use data from the parent row in order to do a calculation
> >> >in the child
> >> >
> >> >All of this works just fine and the report data is correct.
> >> >
> >> >Now, what I am having issues with is doing the page/report sum calculations.
> >> >The requirements dictate that I need to create a sum of both the parent
> >> >column and also the child column in the totals.
> >> >
> >> >I have been trying to do this all day and cannot seem to get it to work at
> >> >all.
> >> >
> >> >In the page totals, I am trying get something like this:
> >> >=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
> >> >
> >> >But, when I try to execute this, it prompts me for the [PegStdLabor] value
> >> >because it is being referenced but the sub-report does not exist yet.
> >> >
> >> >I have also tried the HasData Report parameter (similar problem), and
> >> >IsObject, IsError, IsMissing, etc...
> >>
> >>
> >> If the subreport might not have any records, change the
> >> expression to:
> >>
> >> =Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
> >> [Product - Pegged].Report![PegStdLabor], 0))
> .
>
From: Marshall Barton on 22 Apr 2010 15:56
rickr wrote:
>That definitely did the trick. Thank you ever so much.
>
>Sometimes it is the simple things that really cause the most difficulties. I
>truly appreciate your help.
>
>So, for those that may want a solution recap:
>If you have a report with a sub report, and need to use values from the
>sub-report in order to calculate total report values, follow this procedure:
>1. For each sub-report value that needs to be referenced on the main report
>totals
> a. Create a text-box and place it on the main detail section, name it
>something useful like 'col_1_Sum'
> b. In the Control Source, reference the sub-report data element like so:
>
>=IIf([Sub-Report_Name].Report.HasData,Nz([Sub-Report_Name].Report.column1,0),0)
> c. Set the text-box 'Running Sum' property as necessary (I created 2
>text boxes, 1 for the primary group and the second for the 'Over All' option)
> d. Optional: Make the text-box NOT Visible
>2. In the Report Footer, set the text-box where the total is going to be
>displayed like this (using the text box with Over All running sum):
>= Sum([col1]) + [col_1_sum]
>
>Works like a charm.


Nice wrap up summary. Here's hoping a lot of people with
the same issue find it when they search for a solution for
this problem.

--
Marsh
MVP [MS Access]
First  |  Prev  | 
Pages: 1 2
Prev: Printing only fields that contain data
Next: Report title based on Date?