How to Concentrate Items in a Field Into a Group Summary & Crystal Report

Written by barry kaye
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Concentrate Items in a Field Into a Group Summary & Crystal Report
Display field items in a Crystal Report group summary. (John Foxx/Stockbyte/Getty Images)

Displaying all items of a given field into a Crystal Reports group summary is not straightforward. Even a single report level summary requires a series of dependent formulas placed in sequence to account for Crystal Reports inherent order of operations. Adding a defined Group forces another formula to reset collected values so a grouping does not contain the values of the prior grouping. You can avoid array variables to capture field items. Though use of the JOIN(Array) function will string arrayed items together, arrays have a 1,000 item limitation and are more difficult to use with Groups.

Skill level:
Moderate

Other People Are Reading

Instructions

    Creating Formulas and Defining Variables

  1. 1

    Create initial report with desired fields and groups.

  2. 2

    Create first Join formula (ex: Join1) to include:

    Note: // Formula Comments, not processed.

    Shared stringVar Y:= Table.Field

    // (Table.Field contains the items for display in Group Summary

    Shared stringVar X;

    Shared stringVar Z;

  3. 3

    Create second Join formula (ex: Join2) to include:

    EvaluateAfter ({@JOIN1});

    shared Stringvar X:={@Join1}; // Previous Record

    Shared stringVar Y:=Table.Field; // Field Data

    Shared StringVar Z;

    IF Z=""

    THEN Z:=Y

    ELSE Z:=X & "," & Y;

    // "," is an optional delimiter for stringed items.

    Trim(Z)

  4. 4

    Create Formula for Group Header (ex: Join-Header) to include:

    shared StringVar Z := "";

    // Placed in the Group Header, prevents a group from displaying a prior

    groups items.

    // Can be suppressed (via formatting options)

  5. 5

    Create Formula for Group Footer (ex: Join-Footer) to include:

    shared StringVar Z;

    trim(Z)

    // Displays all field items in the Group Footer, as delimited in Join2.

    Placing Formulas and Fields on Report

  1. 1

    Place and Suppress formula Join2 in Detail Section.

    Note: Join2 must exist in Detail Section, but can be suppressed.

  2. 2

    Place Table.Field in Detail Section

    Note: Table.Field displays database items, but can be suppressed. Can also suppress entire Detail section.

  3. 3

    Place formula Join-Header in Group Header

    Note: Join-Header can be suppressed. Can also suppress entire Group Header section.

  4. 4

    Place formula Join-Footer in Group Footer.

Tips and warnings

  • The technique used is a "Running Total." When place in the details, Join2 (concatenating for strings or calculating for numerics) is a ever-increasing value (running total) for each successive item found in the field of interest. When Join2 is also placed in the Group Footer (which automatically calculates after all details rows are read), it can draw on the final running total stored from the last detail row, which it displays.
  • The final running total value only exists because of its row-level processing, which is why Join2 can't just be placed in the Group Footer. If it was, it would only see the last value of the field, and not the running total of all field values.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.