Suppress Duplicates Crystal Report XI 1

[ReillyC]

Hello
I need some help.
I had to create a command that combined three sql queries. The results are correct however, now I have duplicates in my crystal report.
See record index

App Type Code Event Date Status Filing date Record Index
App#2 NP MY 20-Oct-05 FLG 20-Oct-05 5,834,488.00
App#2 NP TH 18-Oct-05 FLG 18-Oct-05 5,834,490.00
App#3 DIV CN 25-Oct-05 FLG 25-Oct-05 5,997,068.00
App#3 DIV CN 25-Oct-05 FLG 25-Oct-05 5,997,078.00
App#4 PCT WO 7-Oct-05 FLG 7-Oct-05 5,925,968.00
App#4 PCT WO no date FAR 7-Oct-05 5,925,968.00
App#5 PCT WO 12-Oct-05 FLG 12-Oct-05 5,928,698.00
App#5 PCT WO 31-Oct-05 FAR 12-Oct-05 5,928,698.00
App#6 NP MY 7-Oct-05 FLG 7-Oct-05 5,852,706.00
App#6 NP TH 7-Oct-05 FLG 7-Oct-05 5,852,708.00
App#7 NP MY 5-Oct-05 FLG 5-Oct-05 5,836,466.00
App#7 NP TH 5-Oct-05 FLG 5-Oct-05 5,836,468.00
App#8 PCT WO 7-Oct-05 FLG 7-Oct-05 5,853,370.00
App#8 PCT WO no date FAR 7-Oct-05 5,853,370.00


I need to suppress the duplicates so that I can display a count for each month.


I need to suppress the following:
If the record index duplicates and the code = WO then suppress the status FAR.

Desired results
App Type Code Event Date Status Filing date Record Index
App#2 NP MY 20-Oct-05 FLG 20-Oct-05 5,834,488.00
App#2 NP TH 18-Oct-05 FLG 18-Oct-05 5,834,490.00
App#3 DIV CN 25-Oct-05 FLG 25-Oct-05 5,997,068.00
App#3 DIV CN 25-Oct-05 FLG 25-Oct-05 5,997,078.00
App#4 PCT WO 7-Oct-05 FLG 7-Oct-05 5,925,968.00
App#5 PCT WO 12-Oct-05 FLG 12-Oct-05 5,928,698.00
App#6 NP MY 7-Oct-05 FLG 7-Oct-05 5,852,706.00
App#6 NP TH 7-Oct-05 FLG 7-Oct-05 5,852,708.00
App#7 NP MY 5-Oct-05 FLG 5-Oct-05 5,836,466.00
App#7 NP TH 5-Oct-05 FLG 5-Oct-05 5,836,468.00
App#8 PCT WO 7-Oct-05 FLG 7-Oct-05 5,853,370.00

Any suggestions or guidance.
Many Thanks

Cheers
R

 

[synapsevampire]

Those aren't duplicates, the term you seek is row inflation. A duplicate means that all fields are the same, this is not the case here.

Right click the details section and select format section->x 2 next to suppress and place:

{table.record index} = previous({table.record index})
and
{table.code} = "WO"

You weren't clear on whether it's supposed to suppress them when both rows are WO (the first might be a different code, and this next one is the "WO"), so I just guessed.

You might need:

{table.record index} = previous({table.record index})
and
{table.code} = "WO"
and
{table.code} = previous({table.code})

-k

 

[ReillyC]

Row inflation. Thank you for defining my problem.
Your first suggesetion works great.
{table.record index} = previous({table.record index})
and
{table.code} = "WO".

The only issue I have is the count. The count doesn't reflect what is now displayed on the screen. It still shows 14 when there are only 12 records now. How do I correct the count?

Many Thanks
R

 

[synapsevampire]

You'll have to create a manual count using the same criteria.

if not(
{table.record index} = previous({table.record index})
and
{table.code} = "WO") then
1
else
0

Use this formula for aggregates.

-k

 

[ReillyC]

Thanks for the help
But I have problem.
I created a formula field and placed the formula inside it but Crystal XI doesn't display it as an option to create a summary on. Is it because my report is based on a union of 2 sql statements?
Cheers
R

 

[BobSuruncle]

You aren't able to do summaries on a formula that contains Previous/Next functions as these functions are 2nd pass functions.

Perhaps you could use a Running Total type field to do the summary.

Try puting NOT( {table.record index} = previous({table.record index})
and {table.code} = "WO" ) in the X2 button beside the Formula area in the Evaluate section of the Running Total field.

If you're grouping, you'd likely want to reset On Change Of that group.




Bob Suruncle