Crystal report merge two records and display in one line

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

Current result:

StudentID Name 'Submit Date'                     'Approved by' 'Approved by ID'

1234,        SAM, '10/01/2018 4:04:18 PM',     Hary,              hry243

1234,        SAM, '10/02/2018 12:30:51 PM',   John,              jhn334

Open in new window


Expected result:

StudentID      Name     'Submit Date 1'               'Approved by 1' 'Approved by ID 1'             'Submit Date 2'             'Approved by 2' 'Approved by ID2'

1234           SAM      '10/01/2018 4:04:18 PM',           Hary,            hry243,                  '10/02/2018 12:30:51 PM'      John                  jhn334

Open in new window

AJ SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike McCrackenSenior ConsultantCommented:
The only way to do that is to

Add a group on the student ID
Add a formula to the group header that declares a variable(s) for the data
Add a formula to the details to build the desired output.  This formula will have '' as the last line to avoid displaying anything
Add a formula to the group footer to display the resulting string

I'll try to build a sample report for you.

mlmcc
Mike McCrackenSenior ConsultantCommented:
Here is a report that shows the basic idea
Combine-Lines.rpt
Combine-Lines.xls
AJ SAuthor Commented:
Thanks mlmcc. That would be really helpful if you can share the report with formula.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

James0628Commented:
Will there always be 2 records for each student (no more or less)?

 If so, another option would be to group on the student ID, and then put a formula in the group header that saves the desired fields in variables.  That will save the values from the first record for that student.  Then in the group footer, you can have a separate formula for each variable, to output the saved values; and put the fields in the group footer to output the values from the last/second record for each student.

 Is the Submit Date field actually a string, as in your example, or is it really a datetime?  I'm going to assume that it's really a datetime.

 Create a formula like the following (call it whatever you like) and put it in the report header, to declare the variables:

WhilePrintingRecords;
Global DateTimeVar Submit_Date1;
Global StringVar Approved_by1;
Global StringVar Approved_by_ID1;
""

Open in new window


 The "" at the end is just so that the formula doesn't produce any visible output on the report.  You can also just suppress that field, or the entire section.

 Create a formula like the following (call it whatever you like) and put it in the group header, to save the values from the first record in the group:

WhilePrintingRecords;
Global DateTimeVar Submit_Date1;
Global StringVar Approved_by1;
Global StringVar Approved_by_ID1;

Submit_Date1 := {Submit Date field};
Approved_by1 := {Approved by field};
Approved_by_ID1 := {Approved by ID field};
""

Open in new window


 Then you'd just have 3 formulas in the group footer to output the value in each of those variables:

// Submit Date formula
WhilePrintingRecords;
Global DateTimeVar Submit_Date1;

Submit_Date1

Open in new window


// Approved by formula
WhilePrintingRecords;
Global StringVar Approved_by1;

Approved_by1

Open in new window


// Approved by ID formula
WhilePrintingRecords;
Global StringVar Approved_by_ID1;

Approved_by_ID1

Open in new window


 And, of course, your group footer would also include the student ID and name, and the 3 fields in question (Submit Date, etc.).

 If you just put the fields and formulas in the group footer, then you'll have to create your own column headings.  You could get CR to create headings by putting the fields and formulas in the detail section, and then just suppress that section.

 James
Mike McCrackenSenior ConsultantCommented:
The report was attached in my second comment

mlmcc
AJ SAuthor Commented:
Thanks Mlmcc and James for your prompt response.

Hey James,
In response to your question above, there can be 1 single record for some students at the time report is run, but eventually there will be 2 records for most of the students, currently, I see some students with a single record their approvers are getting duplicated its repeating in another column. I want the other approver column to be blank if there are no approver for that column.
James0628Commented:
For each field that you don't want to see when there is only one record:

 Right-click on the field, select Format Field, click on the formula (X+2) button beside the Suppress option on the Common tab, and enter the following formula:

Count ({StudentID field}, {Student group field}) = 1

 Replace those with your actual field names.  "Student group field" is whatever field you used to create the student group.

 That will suppress those fields when the group/student only has 1 record.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
DId you try my report?

Since it uses a manual summary to generate the display it should only show 1 record.

mlmcc
AJ SAuthor Commented:
Thanks James and mlmcc for your help.
James0628Commented:
You're welcome.  Glad I could help.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.