Consultor Eletrônico

Report Builder - how to create a horizontal list ( IIF )


Example:


Customer wants to build a calculated field based on contents of a
field from each detail record to be printed as a summary line.


Customer No. Order No. Carrier
___________ _________ _______

1 1 FlyByNight Courier
1 2 Walkers Delivery
1 3 Standard Mail
1 4 Standard Mail
1 5 Standard Mail

CarrierList
--------------
FlyByNight,Walkers Delivery,Standard Mail,Standard Mail, Standard Mail
|
|

- Need to find way to build the calculated field by concatenating
the contents of the each detail field to the calculated field.

Here's how to do it:

You can create a recursive calc field as follows:

1. Open a new report in report builder

2. Select the Order table

3. Select - Cust-num, order-num and carrier

4. Sort by the cust-num (this will also create the Group by)

5. Create New band line - Select GF-Cust-Num (This is where the
calculated field will go)

6. Create a calculated field called CarrierList with the expression:

Carrier

(This is so you can use the same calculated field in itself)

7. Edit the calculated field, changing the expression to:
IIF(Custnum = PREVIOUS(Custnum),(CarrierList + ", " + Carrier),Carrier)

8. Insert the calculated field on a Cust-num Group-footer bandline.

9. Change the format and width to accomodate the list.
- double click on the calculated field
- change char format to x(80)
- change width to 80

9. Save and Close the report.

10. Open the report and run.


Progress Software Technical Support Note # 16047