And Not Or 2

[Deleco]

Hi i have a little problem i would like some help with.

I have been asked to create a report of customers that have all 3 specific codes.

The report will consist of two tables Customer and CustomerCode which has a one to many link between Customer and CustomerCode.

I can easily create a report using the is one of statment and this brings back any customer with any one of the codes, i need to bring back customers who have all 3 codes.

So for example, using the is one of statement i get

Customer1 0001

Customer2 0001
Customer2 0002
Customer2 0003

Customer3 0002

What i need the report to produce is just Customer2 as it has all 3 codes

Customer2 0001
Customer2 0002
Customer2 0003

It sounds so easy doesn't it? but i am STUCK
Many thanks for any help

Deleco

 

[GMcNamara]

If you are using CR 9, create the report with an SQL Command that is similar to:


SELECT * FROM CUSTOMER
WHERE CUST_ID IN
(SELECT CUST_ID FROM CUSTOMER
WHERE CUST_CODE_ID = 1 AND
CUST_ID IN (SELECT CUST_ID FROM CUSTOMER
WHERE CUST_CODE_ID = 2 AND
CUST_ID IN (SELECT CUST_ID FROM CUSTOMER
WHERE CUST_CODE_ID = 3)))

If you are using an earlier version of Crystal, I doubt the solution will be this easy.

 

[dtempleton]

Create a formula for each of the codes. For example, {@has0001}:

{Table1.code} = "0001"

Repeat for the other two.

Create a group by the customer.
Insert the following group selection:

Maximum ({@has0002}, {Table1.customer}) and
Maximum ({@has0003}, {Table1.customer}) and
Maximum ({@has0001}, {Table1.customer})

 

[Ngolem]

a couple of questions

1. You don't want to report the codes do you? I doubt it since the customer must have all 3 codes to be reportable, yes?

The reason I ask...is that your "problem" is not really about the report display...rather the qualifying of the data tobe displayed.

2. You state there are 3 codes involved...is this always so? are new codes EVER ADDED...or...FORESEEN in the future?

To hardcode these values makes this a future maintenance problem.

3. What is the record size of this report? Are we talking about hundreds of customers, thousands of customers...more?

You should tell us...since the approach probably could vary with the expected number of records returned.

As far as the codes are concerned...I would probably design a small subreport placed in the Report header that returns all distinct code values...they are then stored in a shared array...PERHAPS you need these particular values later for display...you don't really say.

Now since you have the total possible number of Codes then your comparison now becomes easier...in order for a customer to qualify for the report...they just need to have the same number of codes as the Ubound() of the array you created in the subreport.

now...in another subsection of the Report Header....create another subreport that collects the number of codes for each customer and using the Ubound() of the previous subreport...save the customer ID's of the "qualifying" customers to another array

NOW in your main report....generate the report for all customers

the group/detail sections would be suppressed or not depending on the following formula in the conditional suppress for each section

WhilePrintingRecords;
Shared string array CustID;

not( {Table.CustomerID} in CustID);

That should work in a reasonalble time

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lyanch]

Add the table three times and link it to itself on the customer ID.

Set up the selection formula:

table1.code = code1 and
table2.code = code2 and
table3.code = code3

You can display the fields from just the first instance if you want.

Lisa

 

[lbass]

I like lyanch's solution(*)--it's probably the simplest. Another method would be to create three formulas:

//{@0001}:
if {custcode.code}= "0001" then 1 else 0

//{@0002}:
if {custcode.code}= "0002" then 1 else 0

//{@0003}:
if {custcode.code}= "0003" then 1 else 0

Group on {cust.custID} and then go to report->edit selection formula->GROUP and enter:

sum{@0001},{cust.custID}) > 0 and
sum{@0002},{cust.custID}) > 0 and
sum{@0003},{cust.custID}) > 0

-LB

 

[Deleco]

Thanks for all your help.

I eventually got it working running reports on the data that other reports produced, seeing as this was a one off report this worked...

I did re-try it using lyanch's method and this works brilliantly, i will remember this method cause im sure something like this will crop up again.

One thing though. The formula method is very clever and this is how i first tried to get round the problem, Crystal however would not let me do a Sum on a formula field, does anyone know why??? Im using a Oracle database, connecting with ODBC.

Thanks again

Deleco

 

[GMcNamara]

What version of Crystal?

 

[lbass]

There was an error in my group selection formula--missing parens. It should look like:

sum({@0001},{cust.custID}) > 0 and
sum({@0002},{cust.custID}) > 0 and
sum({@0003},{cust.custID}) > 0

If you used my formulas for {@0001}, etc., then if you were unable to create the above formula, it is because either you did not have a group inserted on {cust.custID} or you tried to add the formula to the wrong formula area--it must be in the GROUP selection formula area, not the record selection formula area.

-LB