Solved

Duplicates in Address Label Crystal Report

Posted on 2004-10-05
5
295 Views
Last Modified: 2008-02-01
I have a crystal report that is using ODBC to link a number of tables.

The report itself is an 'address label' style report, so the data that gets pulled back MUST go in the details section of the report and NOT a group (othwerwise I wouldn't be asking this question) ie. The group section does not have a 'layout' tab which would enable me to replicate the address label etc

The table structure is as follows (TABLE - Columns):

CONTACT
ID, Prefix, First_Name, Last_Name

LIST
ContactId, List_Name

(There are other tables but these are not causing the dupes)
-----------------

One contact can belong to many lists. At runtime, the user is prompted to select one or more lists. If they select two or more lists that the contact is a member of, the contact is returned multiple times in the details section.

Unfortunately, Crystal places the List_Name column in the SQL Query even though it is not output on the report itself, as it is a parameter.

Is there any way of removing that column from the SQL query?

I cannot write a view or sproc to replace the report as they can select n number of lists when the report is run.

HELP!
0
Comment
Question by:Optevia
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 12226724
Is your question about how to eliminate the duplicates?  If so, I would sort the records by the contact ID and then simply suppress the details section whenever it repeats.  Basically a conditional suppression formula of:

  ({contact.id} = previous({contact.id}))

frodoman
0
 
LVL 2

Author Comment

by:Optevia
ID: 12226889
When I try to add your line to my RECORD SELECTION formula I get an error:

" The Function cannot be used because it must be evaluated later "

Here is the rest of my formula:

{lists.listname} = {?List} and
(if {?Party} = 88 then true
else if {?Party} = 99 and isnull({contact.affiliation}) then true
else false) and
{contact.gender} = {?Gender} and

({contact.id} <> previous(contact.id))
0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 12226960
This doesn't go into record selection, it needs to go into conditional suppression.

Report -> Section Expert.  Click "Details" then click the formula button (X+2) to the right of the word "Suppress".  Enter the formula I gave you in this section.

This will suppress the details whenever the contact id is equal to the previous contact id.  It doesn't prevent the record from being read into Crystal but will prevent it from being visible or printed.

Again though, this is only effective if you're sorting by the contact id to insure that duplicates are next to each other.  If the duplicates aren't adjacent then they won't be suppressed.

HTH

frodoman
0
 
LVL 2

Author Comment

by:Optevia
ID: 12227029
Thanks frodoman...
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12227072
Glad to help - frodoman
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Break apart and rearrange field, Crystal Reports 9 65
Crystal Reports remove alpha characters only 5 51
Crystal Reports - Trim Data 7 48
Sum of multiple formulas 6 16
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

816 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

9 Experts available now in Live!

Get 1:1 Help Now