Need to Grand Total a Running total in Crystal

Posted on 2011-04-27
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;

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}    
else if One
        then {BAQReportResult.JobMtl.TotalCost}
else if OneA
        then {BAQReportResult.JobMtl.TotalCost}
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}
else if TwoA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.PORel.RelQty}
else if Three
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.JobMtl.RequiredQty}
else if ThreeA
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}*{BAQReportResult.PORel1.XRelQty}
//else if  Four
//        then 4
else if Five
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}* {BAQReportResult.SE-RemainingQty} +
else if FiveA
        then {BAQReportResult.PORel1.RelQty}/{BAQReportResult.PORel1.XRelQty}*{BAQReportResult.PODetail1.DocUnitCost}* {BAQReportResult.SE-RemainingQty} +
else if Six
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}* {#XRelQtySum} +
else if SixA
        then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}*{BAQReportResult.SE-RemainingQty} +
//else if  Seven
//        then 7
else if Eight
        then {BAQReportResult.JobMtl.TotalCost}
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!
Question by:SeyerIT
    LVL 100

    Accepted Solution

    You will have to use variables.

    Grand Total
    In the report header add a formula to declare the variables
    Global NumberVar GrandTotal;

    Where you calculate the value
    Global NumberVar GrandTotal;
    GrandTotal := GrandTotal + {@YourFormula};

    In the report footer
    Global NumberVar GrandTotal;

    If you want group totals you can add a GroupTotal variable.  Set it to 0 in the group header
    Change the totaling formula to calculate it.

    LVL 2

    Author Comment

    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!
    LVL 2

    Author Comment

    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)
    LVL 100

    Expert Comment

    Change the reset like this

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

    LVL 2

    Author Comment

    Perfect! Thanks again!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now