Crystal report merge two records and display in one line

AJ S
AJ S used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Here is a report that shows the basic idea
Combine-Lines.rpt
Combine-Lines.xls

Author

Commented:
Thanks mlmcc. That would be really helpful if you can share the report with formula.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The report was attached in my second comment

mlmcc

Author

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.
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DId you try my report?

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

mlmcc

Author

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

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial