cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal report merge two records and display in one line

0 Kudos

Hello experts,
I created a report which is displaying 2 records for every student. The student ID, Name, DOB all these fields are repeated and same, the only difference is there are other fields that are not similar, 'Submit Date', 'Approved by'. I would like to merge this data into one record. Is this possible in Crystal report? Thanks in advance.

Current results:
StudentID Name 'Submit Date' 'Approved by'
1234 SAM '01-1-2019' Hary
1234 SAM '02-1-2019' John

Expected results:

StudentID Name 'Submit Date' 'Approved by' 'Submit Date 2' 'Approved by 2'

1234 SAM '01-1-2019' Hary '02-1-2019' John

Accepted Solutions (0)

Answers (10)

Answers (10)

abhilash_kumar
Active Contributor
0 Kudos

Please attach screenshots of the Design and the Preview page.

-Abhilash

0 Kudos

Abhilah,

Please find the attached.how to merge the value 12.9 to centre in the same column (its an average of the previous coulmn value).

Rows may be change by case by but required to calculate the averge and centralised.

Please help on this.

Regards,

Sajeesh

0 Kudos

Hi Abhilash,

Thanks for your help! After applying Sort on the Submit Date field in Ascending Order it fixed some issue but I still see repeating names for some of the records. Is there anything that i am missing? or is there another way to achieve this?

abhilash_kumar
Active Contributor
0 Kudos

Hi Anjan,

Could you try this please:

1. Add a Sort on the Submit Date field in Ascending Order

2. Make sure that the field under the Submit Date column is the database field and not the Submit Date 2 formula field. I hope you've placed fields on the Group Header

3. Make sure that the field under the Approved by is also the actual database field and not the Approvedby2 formula.

-Abhilash

0 Kudos

Hi Abhilash,

Let me know if you need any more info. Any thoughts why am i getting this result?

0 Kudos

Hi Abhilash,

I changed the SubimtDate2 formula and i don't see the error message anymore.The issue is i am seeing repeating value for some of the records not sure why. For eg.

Expected results:

StudentID, Name, Submit Date, Approved by, Submit Date 2, Approved by 
21234, SAM, 11/1/2019 10:01:05 AM, HARY, 02/1/2019 03:12:02 PM, JOHN

But I am getting this:

1234, SAM, 02/1/2019 03:12:02 PM, JOHN, 02/1/2019 03:12:02 PM, JOHN
abhilash_kumar
Active Contributor
0 Kudos

Hi Anjan,

Change the SubmitDate2 formula to:

If Minimum({SubmitDate},{StudentID}) = Maximum({SubmitDate},{StudentID}) Then 
CDate(0,0,0) Else
Maximum({SubmitDate},{StudentID})

-Abhilash

0 Kudos

Can you please advise?

0 Kudos

Thanks Abhilash. I was getting an error 'A String is required' for @SubmitDate2 so i casted it totext. I am not getting proper results. Some of the Approver names are repeating for both the approvers. Not sure what is wrong.

abhilash_kumar
Active Contributor
0 Kudos

Hi Anjan,

Please try these steps:

1.Insert a group on the StudentID field

2. Move the StudentID, Name, Submit Date and Approved by fields to the Group Header

3. Create a formula called @SubmitDate2 with this code and place on the Group Header under the label "Submit Date 2"

If Minimum({SubmitDate}, {StudentID}) = Maximum({SubmitDate}, {StudentID}) Then ""
Else Maximum({SubmitDate}, {StudentID})

4. Create a formula called @ApprovedBy2 with this code and place on the Group Header under the label "Approved By 2"

If Minimum({ApprovedBy}, {StudentID}) = Maximum({ApprovedBy}, {StudentID}) Then ""
Else Maximum({ApprovedBy}, {StudentID})

5. Suppress the Details and the Group Footer sections

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

Hi Anjan,

Is this consistent for all Students?

Does each Student have two rows or could someone have more?

-Abhilash

0 Kudos

They will always have two rows for each student.

0 Kudos

Hi Abhilash,

Each student will only have 2 records not more than that.