Newbie Crystal Report Question

[PuppettMaster001]

Using Crystal Reports 8.5 and need to show only duplicate records for a specific field.

thinking of something like

if {Customers.name}=Previous({Customers.name}) then {Customers.name} else ""

but that doesn't work. Thanks in advance.

-Gino

 

[KLin]

Limit the data to records where the name has duplicates in the SQL statement.

SELECT Customers.Name, Customers.field2 FROM CUSTOMERS where Customers.Name IN(SELECT Customers.Name FROM CUSTOMERS Group by Customers.Name HAVING Count(Customers.Name) > 1

 

[GilletteCat]

When you say it does not work, what exactly happens?
Are you using this code in the Record Selection section or in a formula that you placed in the specific spot on the report?
Make sure you do not have "Select discrete records" or "Suppress Duplicates" checked.
Even though you can not modify selection criteria in SQL, you could try to manually modify the WHERE clause with KLin's suggestion.

 

[PuppettMaster001]

GilletteCat:
When I use the code that I wrote as a formula and drop on the report it just shows up blank. If I just drop the {Customers.name} field on the report I can see all of the customers names. Today I was trying to write something like this under Format Section>Suppress :

Count(Customers.name) = 1

But it would not suppress only customers with 1 entry.

KLin:
I am not exactly sure where I should SQL statement. I tried to enter it under "Show SQL Query" but if I tried to the report I would get a .dll error. Sorry I am extremely new at SQL, Crystal, any kind of programming.

Edit *Update*

If I go to Suppress in the Section Expert it and put the code:

if {Customers.name}=Previous(customers.name}) then false else true

it shows only the duplicated names, but it only shows the second entry for that customer. I need it to show both instances.

 

[KLin]

You may want to consider using the crystal reports help file to see how you can put in a custom SQL statement as a report data source.

 

[brandonbull]

try adding whilereadingrecords to the top of your formula

 

[GeekDrew]

Originally posted by: PuppettMaster001
If I go to Suppress in the Section Expert it and put the code:

if {Customers.name}=Previous(customers.name}) then false else true

it shows only the duplicated names, but it only shows the second entry for that customer. I need it to show both instances.

Off of the top of my head, it occurs to me that you would want something along the lines of:

if (({Customers.name} = Previous({Customers.name})) OR ({Customers.name} = Next({Customers.name}))) then false else true

Also, if you want to write a custom SQL statement to return rows, connect to the database, open the database expert, and double click on the "Add Command" child of the appropriate database. Write whatever statement you want -- it'll appear just as another table in the report designer or linked table manager.

 

[PuppettMaster001]

Originally posted by: GeekDrew
Off of the top of my head, it occurs to me that you would want something along the lines of:

if (({Customers.name} = Previous({Customers.name})) OR ({Customers.name} = Next({Customers.name}))) then false else true

Awesome worked like a charm. Thanks.

Also, if you want to write a custom SQL statement to return rows, connect to the database, open the database expert, and double click on the "Add Command" child of the appropriate database. Write whatever statement you want -- it'll appear just as another table in the report designer or linked table manager.

I don't think that is available in Crystal Reports 8.5. Looks like that was implemented in CR9. But thanks, I appreciate all the help from everybody.