Wonderful Crystal Report

I will say Crystal Report is the most powerful and flexible application I
have ever seen. Yesterday I was asked to implement a report as below:

Question Id Business Position Question
1 Mechnical... IT Administrator
2 Printing... Management CEO

That is, a question may have many businesses, and many positions, but user
wants to see them displayed in a single cell rather than spreaded in many
rows. But as far as I know, there is no sum() or join() function for a
varchar column.

After many unsuccessful tries(actually a few hours), I realized Running
Total with formula in Crystal Report might help and it was proved.

Follow the steps below and we'll have it.

1. Create two formula fields to display in group header with formula as:
WhilePrintingRecords;
StringVar Businesses := "";
and
WhilePrintingRecords;
StringVar Positions := "";

2. Create two formula fields to display in details with formula as:
WhilePrintingRecords;
StringVar Businesses;
if InStr (Businesses, {QuestionList2;1.business}+Chr(13)+Chr(10)) =0 then
Businesses := Businesses + {QuestionList2;1.business} + Chr(13) + Chr(10) ;
and
WhilePrintingRecords;
StringVar Positions;
if InStr(Positions, {QuestionList2;1.position} + Chr(13) + Chr(10)) = 0 then
Positions:= Positions+ {QuestionList2;1.position} + Chr(13) + Chr(10);

The InStr() function is to filter out duplicated entries as business and
position may lead to many duplications with many-to-many relationship.

3. Create another two formulas to display in group with formula as:
WhilePrintingRecords;
StringVar Businesses;
and
WhilePrintingRecords;
StringVar Positions;

4. Hide group header and details

5. Drag question id, and question to group footer.

That's it! Thanks Crystal Report!