CODEWITHSUNDEEP
crystal-reports
tacotime
tacotime

Reputation: 1

Crystal Reports - Removing duplicate rows based on fields

Struggling on Crystal Reports XI R2

I am trying to capture only one row per ID, based on the earliest timestamp.

Example:

ID Time

1 7:00

1 9:00

2 11:00

2 11:30

Would return

ID Time

1 7:00

2 11:00

I have tried to suppress duplicates, but since it is looking at multiple fields that will not work. I wonder if I can group on ID, and then sort on time, removing the later entries?

***I think I may have figured this out, by going to section expert, Selecting Details, Suppress, and then adding the function: {LOG.id} = Next ({LOG.id});.

Would love to hear any other opinions on this before I mark as Answered. Thanks

Upvotes: 0

Views: 11224

Answers (3)

tacotime
tacotime

Reputation: 1

I was able to group by ID, then sort by the time. I had tried to add a min to my SQL query, but it was a beast of a query (by my standards). The part I included in here was a small section of it. Thanks for all the tips.

Upvotes: 0

Dog Ears
Dog Ears

Reputation: 10015

Depending on your data you may get better results if you filter at the source of your data, but without knowing what you're reporting against it's impossible to know.

If you are filtering what's displayed at the report you maybe dragging lots of data across the network only to supress it in the report.. why not just filter at source?

In sql server you could do the top N function something like this (test data included)

create table t3 (id int, supplierId int, description varchar(max), value decimal(5,2), created datetime default getdate())

insert into t3 values 
(1, 1, 'test', 180.0, '20101001'),
(1, 1, 'test', 181.0, '20101003'),
(1, 1, 'test', 182.0, '20101002'),
(1, 2, 'test', 183.0, '20101005'),
(1, 2, 'test', 184.0, '20101002'),
(1, 2, 'test', 185.0, '20101001')


;with cte as 
(select 
      t.id
    , t.supplierId
    , t.description
    , t.value
    , t.created
    , rank() over (partition by t.supplierId order by t.created desc) as Position
from t3 t)
select * from cte where Position = 1

Upvotes: 1

Josh Weatherly
Josh Weatherly

Reputation: 1730

If you have access to the query/stored procedure being used to return the data you could do your grouping there, ala:

SELECT ID, MIN(Time) as Time 
FROM Table GROUP BY ID

Upvotes: 1

Related Questions