Summing a subreport field on main report header

[Gerbers404]

Hi there,

I'm wondering if it is possible to sum a field from a subreport on the main report's header. I haven't had any success with my attempts.

Here's where I'm at now. The source for the textbox in the report header is "=Sum([Selected Quotes by Date2 subreport].[Report]![Revenue])"

When I open the report, it asks for a parameter value for[Selected Quotes by Date2 subreport].[Report]![Revenue]. I truly believe I've referenced the field correctly.

If I enter a value in this parameter value request, the field displays a multiple of the number of records in the report (for example, if there are 25 records in the report, and I enter 1 for the parameter value, the text box displays 25 when the report opens, if I enter 10, 250 is displayed).

Also, If I try to sum with a field within the subreport, it sums the correct info for each record, but I cannot add the sums of all the records.

To explain what I'm trying to do, here's what this report is doing. This is a report of sales quotes written within a specific time period. When these quotes turn into jobs (are booked), they are assigned a project number and a booked amount. I currently am using this report to compare the amount of quoted $ that is actually booked. This part of the report is working fine.

The next step is to compare the amount of booked $ that is actually invoiced. I have added a subreport onto the report that list the invoice number, invoice amount, and invoice date per every project number in the main report. This is also displaying fine.

However, when I try to sum the invoice amount field in a text box in the header, the problems that I described above occur.

Any help would be appreciated,

Thanks


Gerbers404

 

[estrassm]

I'm not exactly an expert here but I had a similar application with a PO system where the subreport total for the PO detail needed to be displayed and summed with some additional values on the main report.

Like you I used the Sum function for the detail lines to a variable named "text15" on the subreportand then used the following formula to reference that field on the main report (adding in tax and freight)

=Report!rptPODetail!Text15+[PFreight]+[PTax]

Hope this helps.

 

[Gerbers404]

Etrassm,

Thanks for you reply! I finally got back to working on this for a while today. I tried your suggestion, and it worked the same as most of the other things I tried, so I need to go back to the drawing board.

I have also tried to sum this with code. I set up a global report variable and attempted to add the invoice amount for every invoice to the variable, and then transfer it to a textbox on my header. This also met with no success. When I try to add the data in the textbox to the variable, the code breaks with an error because it says that the feild has no value.

Thanks again estrassm. Any more ideas would be appreciated.

Gerbers404

 

[ehreider]

I found the following link on the Microsoft support page

http://support.microsoft.com/default.aspx?scid=kb;en-us;129721

It explains adding totals to a main report based on subreport totals. You have to put the subreports in the header section of the report for it to work