3 different fields to same row... 2

[wrbodine]

There are three fields that I'm using as columns in detail (class names), where the data is from a date/time field from the same table, but is different for the 3 different class names (from different records). How do I get these to give their result all on the same row? When I'm running the query, its giving back 3 rows, with null values on the 2 fields that aren't included at the time.

Any help would be appreciated!

 

[kenhamady]

Can you try explaining that again? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[wrbodine]

I have a table containing information on classes (courses), and one of the fields is start date, the date the class begins. On the report, three of these classes are column headers, and the data that I want in those columns is the start date for the classes. This means I'll be drawing from 3 different records (3 different start times), but I'm wanting them all on the same row in the report.
Example:

Table Class | Table Roster
Class StartDate | Person fkClassID
Crystal Reports 1/1/99 | John Doe 1
Visual Basic 5/5/00 | Jane Smith 25
SQL Server 9/5/00 | Tom Williams 38
| John Doe 38
| etc.

Report (Ideal Results):
PersonTakingClass Crystal Reports Visual Basic SqlServer
John Doe 1/1/99 5/5/00 not taken
Jane Smith not taken 5/5/00 9/5/00
Tom Williams 1/1/99 5/5/00 9/5/00

What I'm getting instead is three rows for each person, like this:
Bad Report:
Person Crystal Reports Visual Basic SqlServer
John Doe 1/1/99 Null Null
John Doe Null 5/5/00 Null
John Doe Null Null not taken
etc.

Thanks for your help! Hopefully this makes sense....

 

[kenhamady]

Start with the bad report, group by person and take the Maximum or Minimum of each column (Use Insert Summary), depending on how this field sorts.

Then hide the details and just display the group footer with the summary fields.

The puzzle is how you are getting both words and dates in the same column, like the phrase "not taken". Also, you have some rows with dates in two different columns which seems counter intuitive. That would lead to possibly two different dates in the same column and having to decide between them. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

An alternative:
Person Crystal Reports Visual Basic SqlServer
John Doe 1/1/99 Null Null
John Doe Null 5/5/00 Null
John Doe Null Null not taken

I presume you are already grouped on Person.
In the page header:
Person Crystal Reports Visual Basic SqlServer
In the Person Group Header, formated as Underlay following sections:
John Doe
In the first detail section (for SCR result), formatted as Underlay following section:
1/1/99
In the second detail section (for VB result), formatted as Underlay following section:
5/5/00
In the last detail section (for SQLServer result )
not taken
Because of the underlays, the following output would result
Person Crystal Reports Visual Basic SqlServer
John Doe 1/1/99 5/5/00 not taken
It is not clear to me how exactly your data is structured, so some modifications may be required. I am assuming you are getting your dates for courses by using formulas.
Malcolm
wynden@telus.net

 

[kenhamady]

Malcolm,

Since he has three records, each detail section will print three times. He would have to conditionally suppress the details for each condition.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[wrbodine]

Thanks Ken & MalcolmW,

Using insert summary and taking the maximum worked great; I'm just putting everything in the group header now.

The 'not taken' and 'Null' fields in my example were actually all 1/1/23, I just put the words there to distinguish when it legitimately didn't have anything (not taken) and when it was doing the 3 rows (Null)....

 

[MalcolmW]

Good point - thanks. Malcolm
wynden@telus.net