We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Can a running total that is placed in a group footer be used in a formula in the Group Header?

Jarred Meyer
Jarred Meyer asked
on
Medium Priority
789 Views
Last Modified: 2012-05-11
Basically I have a group header with three detail lines below that each have an order qty.
In the Group Footer I have a running total that is adding up those three qty's. (I have to use a running total because I am evaluating using the Previous function to eliminate a few duplicates.)

Now, I have this formula which is developing a total cost based on many different things:

if {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > 0
        and {BAQReportResult.PORel.XRelQty} < {BAQReportResult.SE-RemainingQty}
        and {BAQReportResult.PORel1.XRelQty} = 0;        
then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}* {#XRelQtySum} +
                {BAQReportResult.JobMtl.TotalCost}


One of the things it needs to take into account is the running total (in bold in the above formula). However, I need to place this formula in the Group Header, in which case the running total is not accumulating all of the data in the detail sections..  (If I place the formula (it's called CurrentCost) in the Group Footer, it does calculate out correctly)

So is their a way for me to modify the formula to tell it to calculate the running total from the number it ends up with at the group footer rather than the number it calculates out to in the group header?

Thanks for the help!
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
In a word NO.

Running totals are calculated as the report is built so the value available in the group footer isn't fully calculated until all the records for the group are processed.  Thus the value is not available when the group header is built because the records for the group haven't been processed.

WHat is it that requires you to use a running total for that calculation?

mlmcc
Jarred MeyerProduction Manager

Author

Commented:
I have to use a running total because I am evaluating using the Previous function to eliminate a few duplicates.

I guess I can just hide the Detail section and make the GH4 and GF4 appear as though they are one section :( That won't be too much trouble, but I like to see what my possibilities are..
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
The only other way is to add a subreport to the group header that does the calculation.  It could return the value or do the full calculation and show it.

mlmcc
Jarred MeyerProduction Manager

Author

Commented:
I know how to add a subreport but thats about it..  Would you be able to tell me how I would setup the subreport? Do I need to get my formulas from my main report into my subreport somehow?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jarred MeyerProduction Manager

Author

Commented:
Wow James.. Thanks for the rundown on this!
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.