[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Omit Suppressed Values in Group Sum

Posted on 2011-04-20
10
Medium Priority
?
1,256 Views
Last Modified: 2012-05-11
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
Comment
Question by:SeyerIT
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 35433346
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 35433739
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35434925
The field you are trying to total.

mlmcc
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 4

Expert Comment

by:musalman
ID: 35439110
How are you suppressing the Fields ? What condition are you using...?

I will try to tell you a simplest formula ...
0
 
LVL 35

Expert Comment

by:James0628
ID: 35439217
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
 
LVL 4

Expert Comment

by:musalman
ID: 35439270

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

Author Comment

by:SeyerIT
ID: 35440214
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
 
LVL 35

Accepted Solution

by:
James0628 earned 1200 total points
ID: 35450090
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 35459212
Thanks James, that should do the trick! I'll get back if I have any other problems..
0
 
LVL 35

Expert Comment

by:James0628
ID: 35459295
You're welcome.  Glad I could help.

 James
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 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