Crystal Reports Fomula Needed 1

[ronbowen]

Hello,

Needing help to solve this issue in Crystal Reports 9 and MS SQL Server.

Example: I have several tables linked on the report two of which are Authors and Codes. Authors has author_id and Codes had code_id with author_id as FK.
For each author_id I obtain multiple Code(code_id) records.

I have grouped on author_id showing code_id, code_desc in the detail. I would like to combine the detail values from multiple records in a single field which I could then place on the group line.

Instead of this:

Last First Code_desc
------- --------- ---------
Lesand Danny ...................Group
1 ...................Detail
2
3
Benedi Eric
7
14

Would like the result to look like this:

Last First Codes
------- --------- -----
Lesand Danny 1,2,3
Benedi Eric 7,14

I don't know if this is possible in Crystal but I think it should be. Any help would be greatly appreciated. Thanks in advance.

Ron

 

[lbass]

You can do this with the following method which displays the group fields in the group footer (with group header and details suppressed):

{@resetcodes} to be placed in the group header:
whileprintingrecords;
stringvar codes := "";

{@codesaccum} to be placed in the details section:
whileprintingrecords;
stringvar codes;

codes := codes + totext({table.code},0,"&quot + ", ";

{@displaycodes} to be placed in the group footer:
whileprintingrecords;
stringvar codes;

if len(codes) > 2 then
left(codes,len(codes)-2);

If the codes field is already a string, you can eliminate the "totext() function.

Just drag the author name fields into the group footer and suppress the details section and the group header.

-LB

 

[ronbowen]

Your prompt response was much appreciated. Everything seems to be working until I get to the group footer section.
The issue seems to be with left(codes),len(codes)-2);

If I use the -2 then the last number in the result is trimmed(as shown below 1201799 which should be 12017991). Is this because of the field length.

Further detail:
The codes field is varchar with a length of 15.

Here's what happens:
If only one record it displays fine in the group footer but trimmed.
1201799
The detail when not suppressed shows me the following:
1201799112017991,

If 4 records were to be in the detail section then nothing displays in the group footer for that author.
The detail section shows me:
1201763512017635,
1201798212017982,
1201830612017306,
k5440ttk5440tt,
while the group footer section remains blank.

I'm really excited about getting this solution resolved.
Sorry if I didn't provide enouth information the first time around.
Thanks again.
Ron

 

[Ngolem]

it should be

left(codes,len(codes)-2);

not left(codes),len(codes)-2);


You say this is Varchar(15)??? I count 16 characters in 1201763512017635

Did you make the field wide enough to display such large numbers?? Also you should enable the "can Grow" for the formula field displaying the codes in the footer.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[ronbowen]

Jim,

Yes on both of your questions.

The error was in my typing of the @codesaccum formula. I've
corrected it and I'm getting the desired result.

Thank you lbass for the solution and Jim for your reply.

Ron