Print Page | Close Window

Formula Help

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1680
Printed Date: 02 Apr 2025 at 3:11pm


Topic: Formula Help
Posted By: Yield727
Subject: Formula Help
Date Posted: 13 Nov 2007 at 2:46pm

I have one field that is call special process code every time a customer comes in they are issued a special process code. The codes are as follows:

 

  1. E1
  2. C1
  3. K1
  4. Apr
  5. E2
  6. C2
  7. K2
  8. JKA1
  9. JKA2
  10. DN2
  11. FG3
  12. Dec
  13. JKB1

 

Here is what I am trying to do:

  1. I need to see only the customers with codes E1, C1 and K1 in there file not one or the other but the customers with all three.
  2. or customers C2, E2 and K2
  3. or customers K1, E1 and JKA1

 

Does anyone have any suggestions


Replies:
Posted By: Lugh
Date Posted: 14 Nov 2007 at 11:15am
If you are looking to apply this to the selection criteria, you may be out of luck.  Your best bet is to do the filtering in your data source, where a couple subqueries or self-joins should do the trick.

If you are willing to bring down all the data, and simply suppress the unwanted data, there is a trick you can use.  It's inefficient, but it works.

Create a formula.  The formula should look something like:

DistinctCount({Code},{Code},"='E1'") +
DistinctCount({Code},{Code},"='C1'") +
DistinctCount({Code},{Code},"='K1'")

The DistinctCount, in this case, should return 0 if there are no records with that code, and 1 if there is.  (This is because it is counting the number of distinct codes that are equal to a given value, which will always be either 0 or 1.)

Then, simply group the report by customer, and suppress any group in which the above formula does not equal 3.  (That will be because at least one of the codes was not present in that group.)


Print Page | Close Window