How to concatinate several fields into one???

[vshapiro]

I have report (Crystal 9.0) with three groups. All information shows in GF3a,b and c.
GF3c I am using for Comments (from table Comments(Oracle DB)).
My problem:
table Comments looks like:
Project# Comments Date
111 xxxxxxxx xxxxx
222 xxxxxxxx xxxxx
222 xxxxxxxx xxxxx
222 xxxxxxxx xxxxx
333 xxxxxxxx xxxxx.... etc...

when I select field 'Comments', I pick up only first one. I need to get all Comments for particular project# and it goes to GF3c (field can grow)....
How can I do this????
Thank you..

 

[MattReed]

I am going to make a lot of assumptions because of my ignorance to your situation. Any differences in your situation could totally change this answer.

First thing... if you have added the Comments table to your datasource and linked it properly, then you are not getting all of the records from the Comments table because it is showing them in the detail section. If this is the case, then I would guess you are only seeing the last record.

If you absolutely cannot incorporate the detail section into your approach, then I suggest using a subreport.

Create a subreport that has one grouping and the Comment table as a datasource. This subreport would be grouped by Project#. Assuming that your main report is also grouped by Project#, and that the field G3 is grouped by is Project#, insert the subreport in GF3c and link the subreport up so it picks the data from the subreport based on the Project#.

hth,

Matt

 

[nagornyi]

I agree with Matt that subreport is the best solution on the report side. On the database side you can write a simple strored PL/SQL function that would combine all comment lines into one field. (Like: define cursor, open cursor, in loop -- bring the cursor into a variable, concatenate the variable with the result through CHR(10), close cursor). The function parameter would be Project#.

 

[vshapiro]

You right. I am getting only last record and I cannot incorporate detail section for sure.
Now - about subreport. This report goes into web application and I export it to the .pdf format for user.
As much as I know I cannot click and go sub report in pdf...??? Is it any other possibilities??

Thank you very much

 

[GJParker]

You could also use the 3 formula approach and create a formula field to concatenate the comments fields using the Previous function to compare the current comment text with the last records comment text and concatenate them together if it's a new line.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[vshapiro]

Could you please give more details??
Thank you very much

Veronica

 

[GJParker]

vshapiro

Take a look at faq149-243 by Ken Hamady follow the link and check out formula 16.

the second formula is where you would incorporate the previous function. Unfortunately I don't have CR on my PC at the moment, so am unable to generate the formula for you, but it should look something like this.

WhilePrintingRecords;
StringVar Item:= {Your.Field}; // place your field in place of {Your.Field}
StringVar Chain;
NumberVar ChCnt;

if ChCnt = 1
then (ChCnt:= 2; chain := Item)
else
if previous({Your.Field}) <> {Your.Field} then
chain := chain + ', ' + Item


Take a look and post back if you need further help.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[vshapiro]

thank you very much. I'll work with it and let you know the result