BusinessObjects Board

Crystal report merge two records and display in one line

building reports crystal reports
#1

Hello experts,
I created a report in which there are 2 records for every student. There are two different date fields Submit date and Joining date and due to this its showing two records for every student.
I would like to move the ‘JoiningDate’ right next to ‘SubmitDate’ so when the report is run it displays only 1 record for each student. The problem is the table that holds this information has two records for each student 1 for Submit date and the other for Joining date. Please help me out and share your ideas how to achieve this. Thanks in advance

bohelp22 (BOB member since 2011-05-14)

#2

  1. Group by student and suppress the details section.

  2. If one date will always be earlier than the other (e.g., Joining data is always before or on the same date as Submit date), you’ll do two summaries for the date fields - one will be the Maximum of the date file and the other will be the minimum of the date field. If this is the case, the data can go in either the student group header or group footer section.

  3. If there is no pattern to which date comes first but there’s a field that indicates what kind of date this is, you’ll need variables and a several formulas.


{@InitVars}
DateVar submit := Date(1970, 12, 31);
DateVar join := Date(1970, 12, 31);
""

{@checkDate}
DateVar submit;
DateVar join;
if {mytable.datetype} = 'Submit' then 
  submit := {mytable.datefield}
else if {mytable.datetype} = 'Joining' then
  join := {mytable.datefield};
""

{@ShowSubmit}
DateVar submit

{@ShowJoining}
DateVar join

The empty string ("") in the first two formulas makes sure that nothing shows on the report when the formula is placed.

Put {@InitVars} in the student group header. Put {@CheckDates} in the suppressed details section (it should still evaluate.) Put your data in the student group footer with {@ShowSubmit} and {@ShowJoin} in the appropriate places as the submit and joining dates.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#3

Thank you Dell. So have to do all the 3 steps?

bohelp22 (BOB member since 2011-05-14)

#4

Hi Dell,
Thanks for proving the solution. Date works fine, i was able to add MAX and MIN and put both the dates aside. I forgot to mention one more requirement, There is a Username field associated with each date which need’s to go on the same line. So there will Submit date, Submit Username, Join Date, Join Username. How to merge that field?

bohelp22 (BOB member since 2011-05-14)

#5

You’ll handle that with StringVars the same way that you handled the dates.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#6

Hi Dell,

I handled the Date field by adding the summaries to the date field by using MAX and MIN as there is always one date earlier than the other, I didn’t use any formula. So how to add the Username in order? I didn’t get the StringVar concept do i have to use that?

bohelp22 (BOB member since 2011-05-14)