• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

How to Omit Suppressed Values in Group Sum

I have a list of Materials with there associated cost. The cost is calculated based on Purchase Orders written for that material.. Problem is, I only want to capture the first PO. I have achieved suppressing these extra values with the following formula placed in the common tab for my CurrentCost as well as the detail section so that the entire line will drop off (I could probably just suppress at the detail level):

Not OnFirstRecord And {@CurrentCost} = Previous({@CurrentCost}) And {BAQReportResult.JobMtl.AssemblySeq} = Previous({BAQReportResult.JobMtl.AssemblySeq})   

Open in new window

 

I saw this issue come up in another question without a good solution to my problem so I'm bringing this up again.. Hopefully someone understands my issue and can help:)

I want to SUM CurrentCost for the unsuppressed fields only.. Simple concept but sounds from looking at a few other posts that it may be somewhat complicated..

Thanks for the help!
0
Jarred Meyer
Asked:
Jarred Meyer
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
mlmccCommented:
Not really complicated once you know the process.

In the report header add a formula

WhilePrintingRecords;
Global NumberVar myTotal;
""

In the details
WhilePrintingRecords;
Global NumberVar myTotal;
IF NOT (Not OnFirstRecord And {@CurrentCost} = Previous({@CurrentCost})
                And {BAQReportResult.JobMtl.AssemblySeq} = Previous({BAQReportResult.JobMtl.AssemblySeq}))   THEN
     myTotal := myTotal + {{NumberField};
""

IN the footer
WhilePrintingRecords;
Global NumberVar myTotal;
myTotal

mlmcc
0
 
Jarred MeyerProduction ManagerAuthor Commented:
What should I be putting in {NumberField}? Sorry as I am struggling a little with understanding the process that this is doing..

Thanks again for the help! Once it's functioning it will probably make more since to me :)
0
 
mlmccCommented:
The field you are trying to total.

mlmcc
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
musalmanERP ConsultantCommented:
How are you suppressing the Fields ? What condition are you using...?

I will try to tell you a simplest formula ...
0
 
James0628Commented:
Just to add a bit of explanation, what mlmcc is suggesting is that you create a formula that adds the field that you want to summarize to a variable (the variable is named myTotal in this case), but only under the conditions where your detail section is _not_ suppressed.  In pseudo-code, it says

 If not (conditions that suppress the detail section), add your field to the variable.

 Or, to put it another way

 If the detail section is not suppressed, add your field to the variable.


 Then you use another formula at the end (in a footer) to output the value that has been accumulated in that variable.

 If what you're trying to sum is actually the {@CurrentCost} formula mentioned in your first post, that's what you would put where mlmcc has {NumberField}.

 If you need similar totals for more than one field/formula, you'll need a separate variable for each one.

 James
0
 
musalmanERP ConsultantCommented:

Generate a Unique Identifier in SQL. using Row_Number() OVER(Partition By PONumber Order By PONumber), see the syntax.

It will generate a counter for all PO's starting from 1 for all Individual PO's.

In Crystal , Suppress the section if Counter > 1.
This will suppress the section.

Now add a Formula Field for Cost.

IF Counter = 1 THEN
 COST
ELSE
 0

Finally, Make a Sum of this Field. and show this field total Instead of Actual field total.

Are you using EPICOR ERP ???

0
 
Jarred MeyerProduction ManagerAuthor Commented:
mlmcc: I was thinking that was the case so I used my current total in that spot in the formula.. One problem I have run into with this formula is that it does not reset for each group. It is doing a running total for the entire report.. The report is seperated into Jobs GH1 & GF1, which have a total, then within the job it is broken up into another category GH2, which is not totaled, and then a final breakdown GH3 & GF3 which also have totals. So I would need to show a total for each job at GF1 and subtotals at each section of GF3. I also may add a Grand total for the entire report.

James: Thanks for the clarification to the formula mlmcc wrote.. It did help me better understand the concept, however, I definitely don't know how I would piece it together to make it work with all my different group totals..

Musalman: If possible I want to avoid SQL for now.. I am definitely far from understanding any of that at this point. But yes we are using Epicor ERP, which does make it easy to, for the most part, extract the information and bring it into Crystal..

Now, while I am impatient I did try a method of my own, but got an error message when I tried to total it.. It seemed pretty solid in my opinion but I don't understand enough of how Crystal interprets the data to know why it isn't working.. Here is what I tried.. I changed my formula @CurrentCost to read: (the section in italics is what I added to correct the double lines. This set any duplicate line cost to zero. And then I suppressed the line using section expert\detail section\suppress conditionally...)

[i]if  not OnFirstRecord 
    and {BAQReportResult.JobMtl.AssemblySeq} = Previous({BAQReportResult.JobMtl.AssemblySeq}) 
    and {BAQReportResult.JobMtl.MtlSeq} = Previous({BAQReportResult.JobMtl.MtlSeq})
        then 0
        else [/i]            if {BAQReportResult.SE-Material_Cost} > 0 and {BAQReportResult.SE-OrderedInv} = 0
                then {BAQReportResult.SE-Material_Cost}
            else if {BAQReportResult.SE-Material_Cost} = 0 and {BAQReportResult.SE-OrderedInv} > 0
                then {BAQReportResult.SE-OrderedInv}
            else if {BAQReportResult.JobMtl.IssuedComplete} = True 
                then {BAQReportResult.SE-Material_Cost}
            else if {BAQReportResult.JobMtl.BuyIt} = true
                then {BAQReportResult.SE-Material_Cost}

Open in new window


Once this section was added I tried to refresh the report but get an error message that reads "A Summary has been specified on a non-recurring field. Details: @CurrentCost" Apparently it doesn't like the way the CurrentCost formula is getting a result?

Anyways.. Let me know if my formula can be easily tweaked to make this work or we can continue the variable method to figure this out.. Whichever is easier for you guys is fine with me! :)

Once again, I appreciate your guys' help!
0
 
James0628Commented:
If you want a group total, you just need to reset the variable for each new group.  For example, if myTotal is going to be the total for group 1, create a formula like the following and put it in GH1:

WhilePrintingRecords;
Global NumberVar myTotal;
myTotal := 0;
""

 That will reset the variable at the start of each group 1.

 If you also want totals for other levels in the report, like group 3 or grand totals, you'll need to use separate variables for those.  For example, change the formula in the report header to:

WhilePrintingRecords;
Global NumberVar Group1Total;
Global NumberVar Group3Total;
Global NumberVar GrandTotal;
""


 Put a formula in GH1 that resets Group1Total to 0, and a formula in GH3 that resets Group3Total to 0.


 Change the detail section formula to:

WhilePrintingRecords;
Global NumberVar Group1Total;
Global NumberVar Group3Total;
Global NumberVar GrandTotal;
IF NOT (Not OnFirstRecord And {@CurrentCost} = Previous({@CurrentCost})
                And {BAQReportResult.JobMtl.AssemblySeq} = Previous({BAQReportResult.JobMtl.AssemblySeq}))   THEN
(
  Group1Total := Group1Total + {your field or formula};
  Group3Total := Group3Total + {your field or formula};
  GrandTotal := GrandTotal + {your field or formula};
);
""

 Then use a formula to output each variable at the appropriate place (GF1, GF3 or the report footer).

 You can, of course, change those variable names to something more meaningful than Group1Total and Group3Total.  Just make sure that you use the same name in each formula.  CR does not try to cross-check them or anything.


 As for the error that you got on that formula, you can't do a summary on a formula that uses the Previous (or Next) function.  I guess that's what CR means by "non-recurring field".  IAC, that's definitely a problem.  CR doesn't allow that.

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Thanks James, that should do the trick! I'll get back if I have any other problems..
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now