report structure/pivot query

Hi

I'm having trouble with the structure of my report. Consultations with various SQL forums have informed me that I should not try to manipulate my data in the query but do it in Crystal.

The format I want is:

BookID, Author1, Author2, Author3, Author4, BookTitle, PublisherName, YearOfPublication, ISBN

The dataset I have from my query is:

BookID, Author1, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author2, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author3, BookTitle, PublisherName, YearOfPublication, ISBN

BookID, Author4, BookTitle, PublisherName, YearOfPublication, ISBN

I need to group the data on the BookID so that the book details only show once for each instance but I can't find a way to concatenate all the author details and group it properly within the book. At the moment, the group is based on the BookID and the group header section contains BookID and BookTitle. The details section contains a formula field that concatenates the surname, forename and initials of an individual author. The Group Footer section contains all the other book details fields. This means that the output looks like this:

BookID, BookTitle
Author1
Author2
Author3
Author4
PublisherName, YearOfPublication, ISBN

Please can anyone help?Put a formula in the BookID group header to reset an author variable.
Suppress the group header.
Create a formula in the detail to concatenate the authors in a variable.
Suppress the detail.
Put everything in a text object in the group footer.

e.g.

@.reset (in group header)
whileprintingrecords;
stringvar authors := '';

@.add_author (in detail)
whileprintingrecords;
stringvar authors;
if authors <> '' then authors := authors + ', ';
authors := authors + {table.author};

@.get_authors (in group footer)
whileprintingrecords;
stringvar authors|||Thanks that really helped! It's looking better already!

Maybe you can help me a little further.... I'm also having trouble with my record selection formula. The user passes in one or more parameters to search on. The parameters relate to the author's surname, forenames and initials. The user can pass in exact details or wildcarded partial details. I want the report to show the books with one or more author that matches the criteria entered but also to show all other authors of the same book who do not match the criteria. Currently, only the authors that match are shown.

This application is being adapted from an Access database. The SQL for the Access report contains the following 'Where' clause (with 'W%' being passed in as an example parameter):

WHERE (((BookAuthor.BookID) In (SELECT [BookAuthor].BookID FROM Person
RIGHT JOIN [BookAuthor] ON Person.[PersonID] = [BookAuthor].PersonID
WHERE (((Person.Forenames) Like ('%') Or (Person.Forenames) Is Null)
AND ((Person.Initials) Like ('%') Or (Person.Initials) Is Null)
AND ((Person.Surname) Like ('W%'))))))

Currently in my record selection formula, I have:

{Person.PersonID} = {BookAuthor.PersonID}
and
{BookAuthor.BookID} = {reportview_BookAndPubl.BookID}
and
If {?SNameParam} <> "" Then {Person.Surname} like {?SNameParam}
or
if {?FNameParam} <> "" Then {Person.Forenames} like {?FNameParam}
or
if {?InitialsParam} <> "" Then {Person.Initials} like {?InitialsParam}

Can you suggest anything to make this formula work like the original sql?
Thanks.