Solved

Exclude some records from totals

Posted on 2016-11-09
10
52 Views
Last Modified: 2016-11-15
I had this question after viewing Crystal reports subreports as total of main report.

What is the fastest way to skip some records in this formula? If some articles is example ="Closed"


Thanks
0
Comment
Question by:Fikri XXXXXXXX
  • 5
  • 3
  • 2
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 41880595
What are you trying to do?

DO you want the "closed" records in the report?
If not simply filter them out through a RECORD SELECT statement

If you are trying to not include them in the formula (which formula)

USe an IF statement

If {YourField} <> "Closed" then
    Formula in question

mlmcc
0
 

Author Comment

by:Fikri XXXXXXXX
ID: 41880625
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41880920
Are you trying to eliminate them from the summary list at the end?

What field is tested for CLOSED?

mlmcc
0
 

Author Comment

by:Fikri XXXXXXXX
ID: 41881153
Field @mycomment. I need to eliminate it from total if this field is ="CLOSED" or empty (" ")
I added some additional function to this field like you did it on field @mycomment1, but principle is same.
Thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41881375
I a problem with that.

The buildlist formula is used in GH2 but the mycomment formula is in GF3 so the 2 are never in sync.

Can you give an example of the report that shows what you want?

mlmcc
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Fikri XXXXXXXX
ID: 41881692
Sorry, I confused you. We can work on your example, that is ok, I need idea, I will then change it later.

In your example formula for building is in GH2, and field for checking is {@ARTICLE}.
I need to exclude some {@ARTICLE} = "CLOSED" and  {@ARTICLE} = empty string.

Is it possible to disable WhilePrintingRecords in some cases or I must do changes in code for building array?


This is part of code for building : zBuildLists:

WhilePrintingRecords;
Global NumberVar numOrders;
Global StringVar Array Article_List;
Global StringVar Array OrderQty_List;

Local NumberVar index;
Local BooleanVar NotFound;

NotFound := True;
Index := 1;

// While NotFound and Index <= numOrders do     --here I tried to exclude but that is wrong!
While NotFound and Index <= numOrders and {@ARTICLE}="CLOSED"  do
(
    If Article_List[Index] = {@ARTICLE} then
    (
        NotFound := False;
        Index := Index;
    )
    Else
        Index:= Index + 1;
);

and continue...
0
 

Author Comment

by:Fikri XXXXXXXX
ID: 41883682
Easiest way here will be option to stop executing WhilePrintingRecords... I don't know is that possible?

Thanks
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 41883950
I'm not sure what you're trying to do with WhilePrintingRecords.  That just controls which report pass a formula is evaluated in, not which records it's evaluated for.  If you're interested in specific {@ARTICLE} values, I don't think that "disabling" WhilePrintingRecords will change anything.

 I haven't tried to check other formulas or anything else in the report, but if you simply want to exclude the records where {@ARTICLE} is "CLOSED" or blank from being saved in that formula, the simplest thing would seem to be to add another If statement, like this:

WhilePrintingRecords;
Global NumberVar numOrders;
Global StringVar Array Article_List;
Global StringVar Array OrderQty_List;

Local NumberVar index;
Local BooleanVar NotFound;

NotFound := True;
Index := 1;

If {@ARTICLE} <> "CLOSED" and {@ARTICLE} <> "" then
(
  While NotFound and Index <= numOrders do
  (
      If Article_List[Index] = {@ARTICLE} then
      (
          NotFound := False;
          Index := Index;
      )
      Else
          Index:= Index + 1;
  );
  If NotFound then
  (
      numOrders := numOrders + 1;
      ReDim Preserve Article_List[numOrders];
      ReDim Preserve OrderQty_List[numOrders];
      Article_List[numOrders] := {@ARTICLE};
      OrderQty_List[numOrders] := CStr({spilInvNumLines.fQuantity},0,'');
  )
  Else
  (
      OrderQty_List[Index] := CStr({spilInvNumLines.fQuantity} + Val(OrderQty_List[Index]),0,'');
  )
);

''

Open in new window


 If {@ARTICLE} is "CLOSED" or blank, it just skips everything after that.

 James
0
 

Author Closing Comment

by:Fikri XXXXXXXX
ID: 41887735
That is it. I done same things but I have few errors with ")".
Thank you.
0
 
LVL 34

Expert Comment

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

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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. …
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

16 Experts available now in Live!

Get 1:1 Help Now