Calculating page/report sums using calculated data from subquery

  • Thread starter Thread starter rickr
  • Start date Start date
R

rickr

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...

I am confused to no end. Any help is appreciated.
 
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 said:
rickr said:
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...
Click to expand...


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))
Click to expand...
 
I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

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


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))
Click to expand...
Click to expand...
 
Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').

CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub

END CODE

Any help is appreciated.
 
Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').
CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub


Any help is appreciated.

rickr said:
I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

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

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