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

Need to Grand Total a Running total in Crystal

Jarred Meyer
Jarred Meyer asked
on
Medium Priority
548 Views
Last Modified: 2012-05-11
I think this is probably more complicated than it sounds in the title but here goes..

To try and start from the top, I have created a running total for an Order Quantity which shows up in my detail section. Because I have some duplicates that show up in my detail section I had to create a formula within the running total which reads;

Not(Not OnFirstRecord
And {BAQReportResult.JobMtl.AssemblySeq} = Previous({BAQReportResult.JobMtl.AssemblySeq})
And {BAQReportResult.JobMtl.PartNum} = Previous({BAQReportResult.JobMtl.PartNum})
And {BAQReportResult.PORel.PORelNum} = Previous({BAQReportResult.PORel.PORelNum}))


This Formula is called XRelQtySum.
So to sum this one up, it basically adds up all the order quantites for a particular part and skips the duplicate entries.

Next, using the XRelQtySum Running total, I have another rather complex formula that gives me the CurrentCost for the Part and it uses the XRelQtySum in some sections of the formula. The CurrentCost formula looks like;

WhilePrintingRecords;
Global BooleanVar Rework;
Global BooleanVar One;
Global BooleanVar OneA;
Global BooleanVar Two;
Global BooleanVar TwoA;
Global BooleanVar Three;
Global BooleanVar ThreeA;
Global BooleanVar Four;
Global BooleanVar Five;
Global BooleanVar FiveA;
Global BooleanVar Six;
Global BooleanVar SixA;
Global BooleanVar Seven;
Global BooleanVar Eight;
Global BooleanVar Nine;
if      Rework
        then {BAQReportResult.JobMtl.TotalCost}    
//1
else if One
        then {BAQReportResult.JobMtl.TotalCost}
//1a
else if OneA
        then {BAQReportResult.JobMtl.TotalCost}
//2
else if  Two then
    if {BAQReportResult.PORel.RelQty} = 0 or {BAQReportResult.PORel.XRelQty} = 0
        then 0
        else {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.PORel.RelQty}
//2a
else if TwoA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.PORel.RelQty}
//3
else if Three
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.JobMtl.RequiredQty}
//3a
else if ThreeA
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.PORel1.XRelQty}
//4
//else if  Four
//        then 4
//5
else if Five
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}* {BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
else if FiveA
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}* {BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
//6
else if Six
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}* {#XRelQtySum} +
                {BAQReportResult.JobMtl.TotalCost}
//6a
else if SixA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.SE-RemainingQty} +
                {BAQReportResult.JobMtl.TotalCost}
//7
//else if  Seven
//        then 7
//8
else if Eight
        then {BAQReportResult.JobMtl.TotalCost}
//9
else if Nine
        then {BAQReportResult.JobMtl.TotalCost}
else 0


The formula takes into account nine different variables which determines how the CurrentCost needs to be achieved.

So I have this CurrentCost which give me each materials current cost. All the materials are grouped into Jobs. Now I need to get a total of CurrentCost in the Jobs Footer. Crystal is not allowing me to select my CurrentCost formula to place in either a running total or a sum though.
So back to my original question, How or Can I create Grand or sub Totals of my CurrentCost Formula based on the scenario I have?

Thanks for any help you can provide as well as all the help I have received so far! You guys are great!
Comment
Watch Question

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jarred MeyerProduction Manager

Author

Commented:
Ah! I got it! Makes more since now and I didn't have to change any of my existing formulas either like I thought I was going to need to do! The Variables seem to work regardless of what you have used in existing formulas.. Very nice!

Thanks a bunch!
Jarred MeyerProduction Manager

Author

Commented:
Oh.. I see one slight issue.. Is there a way to get the Variables to reset at the beginning of each group but not reset at a page break?.. (I currently have the group names to repeat on each page and that seems to reset the variable)
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Change the reset like this

WhilePrintingRecords;
Global NumberVar GroupTotal;
If Not InRepeatedGroupHeader then
   GroupTotal := 0;
""

mlmcc
Jarred MeyerProduction Manager

Author

Commented:
Perfect! Thanks again!
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.