Remove duplicate records bringing back latest data

[Former Member]

Hello,

I am fairly new to the reporting world and to Crystal Reports.  I was wondering if anyone in the community could help me with an issue I have been trying to overcome for the last week.

I am reporting on the number of calls logged through our call handling system the report is working fine but when a call has been reopened the report is showing the data twice.  We only want to see this entry once bringing back the latest resolution date.

How can I return the latest entry for this record only excluding any previous call data from the report.

Things I have tried so far:

'Select Distinct records' - does not work for this as when the call has been resolved twice their is more than one resolution date and the record is not exactly the same as the previous entry for the same record.

I tried to Suppress the details using the Not OnLastRecord to see if this would work for me but I get an error and cant save the formula.

I tried removing duplicates by using a formula to suppress if there was a duplicate call ID - {rm_request.rm_id}=previous ({rm_request.rm_id})  HOWEVER, this just hides the duplicate and the calculations in the report do not change, therefore, the data for the duplicate is still used in the report calculations.

Can anyone help with this?  I am struggling a bit here!

Many thanks in advance.

AMU

[abhilash_kumar · Accepted solution]

Hi AMU,

There a couple of ways to do this.

Using Groups:

1) Create a Group on the Call ID field or any other field that uniquely identifies a row

2) Go to Report > Record Sort Expert > Choose the Resolution Date in the sort list and sort in 'Descending' order

3) Move all fields from the Details Section to the Group Header Section

4) If there are summary fields on the report, you'd need to replace them with Running Totals so that duplicates can be ignored

Using SQL Expression (If you're reporting directly against Tables/Views):

This workaround filters records at source thereby reducing records CR needs to be read. This workaround also does not require any changes to report design or summaries.

1) Under Field Explorer, look for an option called 'SQL Expressions'. Right-click and select New > Give it a name (First Date) and use this code:

(

     Select Min("Date_field") from Table

     where "Call ID" = "Table.Call ID"

)

2) Go to Report > Selection Formula > Record and use this code:

{Date_field} = {%First Date}

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you so much for the quick reply.  I went with option 1 which does stop the duplicate records from displaying (thank you! ) but one of the calculation values do not change - this is a running total already.

This running total is used to work out the average duration of an open case.  One of the total fields seems to be reporting the correct average so I think we will just remove the additional average field.

I will get to grips with Crystal reports one day!

Amu