Trailing Grand Summary Problem

[Courtney]

I have a trailing grand summary that is giving me an astronomically ridiculous number, and I can't even figure out what is being added to come to this total.

My two relevant tables and the fields I'm working with in this report are:

Rolling Projections

RP_Month (date field, validated by value list, only allows user to choose/input the first day of any month, i.e., 8/1/2010)

RP_Dollars (unstored calculation field, multiplies user-input hours by a client's hourly rate found in another table)

RP_Dollars_Sum (Summary field = total of RP_Dollars)

OverUnder (Unstored calculation field = RP_Dollars_Sum - Billable Targets::BT_Sum

OverUnder_Sum (Summary field = total of OverUnder

Billable Targets

BT_Month (date field, validated by value list, only allows user to choose/input the first day of any month, i.e., 8/1/2010)

BT (Number field, input by project manager)

BT_Sum (Summary field = total of BT)

Related by RP_Month = BT_Month (one BT record to many RP records)

My layout, based on the Billable Targets table, has a sub-summary by BT_Month, which displays the BT_Month, RP_Dollars_Sum, BT, and OverUnder. All of these calculate and display properly.

My trailing grand summary part holds the OverUnder_Sum, which is not even close to correct, and I can't even find a calculation that comes to the number it's displaying.

One report's example:

BT_Month: August 2010

RP_Dollars_Sum: 729,635

BT: 611,068

OverUnder: 118,567

BT_Month: September 2010

RP_Dollars_Sum: 534,545

BT: 641,114

OverUnder: -106,569

OverUnder_Sum: -29,626,182!!

Should be: 11,998

Any ideas? I've been working on this for a week, and every permutation brings me back to that crazy negative 29 million number. :)

[comment]

I don't quite understand your description - perhaps you could post a simplified demo file showing the issue. Offhand, it seems you are up against a problem similar to the one here:

http://fmforums.com/forum/showtopic.php?tid/209850/

I believe you'd be better off producing the report from the "many" table.

[Courtney]

Thank you - I believe you are correct that my problem lies in the total number of underlying records. I was just starting to suspect something like that when you replied. I will dig around a little more and see if I can make this work.

(I'm always very hesitant to post examples due to my employer's confidentiality stuff, and making an entire dummy file is not terribly feasible, because so much basic info would need to be filled in to even get to the point of it being a useful example.)

[comment]

making an entire dummy file is not terribly feasible, because so much basic info would need to be filled in to even get to the point of it being a useful example.

That's true - but in order to get to the bottom of the issue, I'd have to reproduce the problem myself...