Is there any way to do this?

[ConfusedChap]

Hi all

I have a table that i'm using Crystal to report on. It basically holds each persons unique reference and whther they were emailed or phoned. People can be emailed and phoned so they can appear repeatedly in the table.

What i'm trying to do is create some sort of formula that can tell how many were ONLY emailed and how many were ONLY phoned.

In SQL you would use an exclude query like

select count(REFERENCE)
from TABLEA
where CONTACT = 'EMAIL'
and REFERENCE NOT IN (select REFERENCE
from TABLEA
where CONTACT = 'PHONE')

Does that make sense?

AS far as i know there is no way of using the 'Not in' option.
Does anyone know of any way this can be done??

Any help is much appreciated

 

[IanWaterman]

If you are happy using SQL why not just report off a command.

When adding datasource connect to DB and select command instead of a table.

Alternatively
Group report by reference

Build report and then add two formula below to details but suppress details

@email
If contact = email then 1 else 0

@phone
If contact = phone then 1 else 0

In Reference header

@emialTot
If sum(@phone, reference) = 0 then sum(@email, reference)
else 0

@phoneTot
If sum(@email, reference) = 0 then sum(@phone, reference)
else 0

In reference header section suppress formula

@phoneTot + @emailtot = 0

Ian

 

[lbass]

Another approach would be to go to report->selection formula->GROUP and enter:

distinctcount({table.contact},{table.reference}) = 1

Then add two running totals to the report that do a distinctcount of {table.reference}, reset never. In the evaluation section of one, use:

{Table.contact} = "Phone"

Use e-mail in the evaluation section of the other. Place the running totals in the report footer.

-LB