How to Add a List or Collection of Multiple Items to a Report

Reports
Some properties contain a list or collection of items. For example, a single order may contain a list of multiple price modifiers. This one-to-many relationship may be reflected in a report in different ways, depending on the need.

Method 1: Bind the Collection to a Detail Band

This method allows for all records within a collection to be displayed easily, taking up the horizontal width of the page and expanding vertically as much as necessary to show all data.
Follow these steps to bind a collection to a detail band:
  1. Open a preview of the report to be modified.
  2. In the preview window, select the Modify button to launch the report designer.
  3. Right-click within the report design surface and choose Insert Detail Band. A child menu will appear with a list of available collections. Choose the desired collection. A new detail report will be added to the report, bound to the selected collection.

    Example: Inserting a detail band that is bound to an order's collection of price modifiers
  4. From within the Field List located on the right hand side, locate the same collection that the detail band was bound to.
    • Drag the entire collection node into the detail band to create a table containing all columns of record data.
    • Expand the collection node and drag a single field into the detail band to display a single column of record data.
  5. Apply a filter to the detail report to restrict records as needed.

Method 2: Use a Calculated Field's Expression to Retrieve a Single Record from the Collection

This method allows for a single record from within a collection, based on specified criteria, to be displayed in a field that may be placed anywhere within a report.
  1. Open a preview of the report to be modified.
  2. In the preview window, select the Modify button to launch the report designer.
  3. From within the Field List located on the right side, right-click on the Orders node and choose Add Calculated Field.
  4. Enter a name in the (Name) field located in the Property Grid below.

    Example: Naming the calculated field to: UDF_MyFieldName
  5. Right-click the calculated field you just created in the Field List and choose Edit Expression.
  6. Enter an expression using the following syntax to locate and display a single item from the collection:
    [CollectionName][[CollectionFieldName] == 'ValueToMatch'].Single([CollectionFieldValueToDisplay])
    For example: The following expression would search the user defined fields collection for a field labeled "International AWB", returning the value of that field:
    [User Defined Fields][[Label] == 'International AWB'].Single([Value])
    Another example: The following expression would search the price modifiers collection for a modifier named "White Glove Service", returning the name and cost of that modifier:
    [Price Modifiers][[Name] == 'White Glove Service'].Single([Name] + ': ' + FormatString('{c}',[Price]))
  7. Click OK to save the expression assigned to the calculated field.
  8. Drag and drop the calculated field you created from the Field List onto the report.

Method 3: Use a Calculated Field's Expression to Retrieve Multiple Records from the Collection

This method allows for one or more records from within a collection, based on specified criteria, to be displayed in a field that may be placed anywhere within a report.
  1. Open a preview of the report to be modified.
  2. In the preview window, select the Modify button to launch the report designer.
  3. From within the Field List located on the right side, right-click on the Orders node and choose Add Calculated Field.
  4. Enter a name in the (Name) field located in the Property Grid below.

    Example: Naming the calculated field to: UDF_MyFieldName
  5. Right-click the calculated field you just created in the Field List and choose Edit Expression.
  6. Enter an expression using the following syntax to locate and display one or more items from the collection:
    [CollectionName][[CollectionFieldName] == 'ValueToMatch'].Sum([CollectionFieldValueToDisplay])
    For example: The following expression searches the user defined fields collection for all fields with a value assigned, returning a formatted list of fields and their assigned values:
    [User Defined Fields][[Value] != ''].Sum([Label] + ': ' + [Value] + NewLine())
    Another example: The following expression searches the price modifiers collection for all modifiers with a price greater than zero, returning a formatted list of modifier names:
    [Price Modifiers][[Price] > 0].Sum([Name] + NewLine())
    Another example: The following expression returns a list of modifier names along with their prices formatted as currency:
    [Price Modifiers].Sum([Name] + ': ' + FormatString('{0:c}',[Price]) + NewLine())
  7. Click OK to save the expression assigned to the calculated field.
  8. Drag and drop the calculated field you created from the Field List onto the report.
Article ID: 253, , Modified: June 28 at 3:19 PM

Share this article