remove or supress duplicate

[ituser2008]

How can I suppress duplicate records at the detail section??
The report is group by customer id and below is an example. Any help will be greatly appreciated. Thanks

customer id Productid transdate

1234 a50 5/10/07
1234 a50 5/10/07

 

[dgillz]

What are your table structures and linking? These are probably not duplicates in the true sense of the word, you probably have a one-to-many table relationship, hence the 2 records.

You can his the details section and drag the fields you want to see to the group footer or header.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[ituser2008]

Yes, it is a 1 to many relationship but the records needs to be at the detail level. Is any other way to do it??

 

[dgillz]

Did you try what I suggested? What is wrong with my solution? Please post sample data and explain why this will not work.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

You can concatenate the fields in the detail secton in a formula {@Concat}:

{table.customerID}&{table.productID}&{table.date}

Then go to the section expert->details->suppress->x+2 and enter:

{@concat} = previous({@concat})

-LB

 

[ituser2008]

Thanks for everybody’s help. I was able to suppress the duplicate record by adding the formula to the section detail. However, when I tried to sum the trans amount and place at the report footer section, the trans amount for the duplicate record was also being calculated into the total. Any idea how to correct this??

 

[dgillz]

Use running totals, evaluate on change of field, reset as needed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbassil]

Hello lbass, I just want to thank you for your reply. I tried your proposed solution, but I am getting a wrong behavior whenever I have only one row, which means the data are not duplicate, so this only row is being suppressed too (but this should not be the case). How can I fix this issue?

Many Thanks.

 

[lbass]

In the section expert, remove the check mark for suppression, and just leave the formula in the formula area for suppression. When the formula does not execute (e.g., when there is no previous record), the condition of the check box will be the default.

-LB

 

[lbassil]

Hello lbass,

Thanks for your valuable reply. The above mentioned solution solved my problem, but not fully. I am still facing a problem due to the following scenario:
1- When any of the fields that constitute the concat string is null or empty, the records are not being suppressed. Is this a normal behavior?
2- So I have created 2 different concat1 and concat2 formulas that contain 2 different combinations under which I need to suppress duplicate records.
3- And then I added a second condition to each of them (i.e (@concat1 = previous(@concat1) and x = y) or ((@concat2 = previous(@concat2) and x = z)). But this didnt work because only the first condition is the one being applied.
4- When I use the second condition alone, it works fine.

Do you have any thoughts/suggestions regarding this issue and please tell me if the behavior is correct or am i missing something.

Thanks.

 

[lbass]

Change the concat formula to:

(
if isnull({table.customerID}) //number field
then "" else
{table.customerID}
) &
(
if isnull({table.productID}) or//string
trim({table.productID}) = "" then
"" else
{table.productId}
) &
(
if isnull({table.date}) or
{table.date} = date(0,0,0) then
date(0,0,0) else
{table.date}
)

Or instead, you could just go to report options and check "convert nulls to default values.

-LB

 

[lbassil]

Hello lbass,

I just want to thank for your helpful replies and solutions. I already did the needed fix from the database output, but your solution will for sure help me later on. Thanks again and keep up the good work.

Rgds.