Return data in one row 1

[tr0ubleduser]

Hi all,
I am using CR 10 with SQL Server 2000. I am trying to create a report which will display the complaints that are filed withing our complaint tracking system. I am not sure how to begin with this report. Here are the three main fields that should be in the report.

1. Complaint ID
2. Basis
3. Issues

The problem I am having is that a complaint can have multiple bases and issues. For example

Complaint ID Basis Issues
1 Sex Sexual Harrasement
1 Age Accomodation

Instead of returning the results in two rows I have to display them in one row. For example.

Complaint ID Basis Issues
1 Sex,Age Sexual Harrasement, Accomodation

I am not sure how to do this. Can someone please tell me if this is doable and if so then how can this be done? I posted this question before but wasnt able to get this resolved.
Thanks!

 

[lbass]

First insert a group on {table.complaintID}. Then create these formulas:

//@reset} to be placed in the complaintID group header:
whileprintingrecords;
stringvar bases := "";
stringvar issues := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar bases := bases + {table.basis} + ", ";
stringvar issues := issues + {table.issue} + ", ";

//{@displaybases} to be placed in the group footer:
whileprintingrecords;
stringvar bases;
left(bases,len(bases)-2)

//{@displayissues} to be placed in the group footer:
whileprintingrecords;
stringvar issues;
left(issues,len(issues)-2)

Then drag the groupname into the group footer and suppress the group header and the detail section.

-LB

 

[tr0ubleduser]

Thanks! this works great. I have another question. There are certain complaints where the basis is repeated more than once. For example.

CompID Basis Issues
1 Sex,Sex,Age Sexual Harrasement,Female,Accomodation

Is there a way to display the basis only once if its being repeated.

CompID Basis Issues
1 Sex,Age Sexual Harrasement,Female,Accomodation

I appreciate your help!

 

[lbass]

Change the accumulation formula to:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar bases;
stringvar issues;

if instr(bases,{table.basis}) = 0 then
bases := bases + {table.basis} + ", ";
if instr(issues,{table.issue}) = 0 then
issues := issues + {table.issue} + ", ";

-LB

 

[tr0ubleduser]

Thank you so much, you have made my life so much easier.