Managing Dynamic Arrays in Crystal Reports: Step-by-Step Guide

Dataset of the example used

Crystal Reports is a powerful tool for generating dynamic reports, and managing arrays can significantly enhance its functionality. However, Crystal Reports doesn’t directly support returning arrays from formulas, so we must work within its constraints using global variables and carefully crafted formulas. This guide walks you through how to create, populate, and use dynamic arrays in Crystal Reports.

---

Step 1: Initialize the Array

Before using a dynamic array, initialize it. Create a formula field named @InitializeArray and place it in the Report Header:

crystalCopy codeWhilePrintingRecords;
Global NumberVar Array SalesArray;
Global NumberVar Counter;

// Initialize the counter and array
Counter := 0;
ReDim SalesArray[1]; // Start with an array of one element
""; // Return an empty string as Crystal Reports formulas cannot return arrays

This formula:

• Declares SalesArray as a global variable to store the array.
• Sets up a Counter to track the number of elements in the array.
• Initializes the array with one element to allow dynamic resizing later.

---

Step 2: Populate the Array Dynamically

To add database values (e.g., {Sales.Amount}) to the array dynamically, create another formula field named @PopulateArray and place it in the Details Section:

crystalCopy codeWhilePrintingRecords;
Global NumberVar Array SalesArray;
Global NumberVar Counter;

// Increment the counter
Counter := Counter + 1;

// Resize the array to accommodate the new value
ReDim Preserve SalesArray[Counter];

// Add the current database value to the array
SalesArray[Counter] := {Sales.Amount};

// Return an empty string to avoid errors
"";

This formula:

• Increments the counter for each record in the Details Section.
• Dynamically resizes the array using ReDim Preserve to ensure existing data is retained.
• Adds the current value of {Sales.Amount} to the array.

---

Step 3: Calculate the Total of the Array

To compute the sum of the array values, create a formula field named @SumArray and place it in the Report Footer:

crystalCopy codeWhilePrintingRecords;
Global NumberVar Array SalesArray;
Global NumberVar Counter;

Local NumberVar TotalSales := 0;
Local NumberVar i;

// Sum the array elements
For i := 1 To Counter Do (
   TotalSales := TotalSales + SalesArray[i]
);

// Return the total
TotalSales;

This formula loops through the array using the Counter as the upper limit and calculates the total of all elements in SalesArray.

---

Step 4 (Optional): Display Array Values

To display the contents of the array (for debugging or informational purposes), create a formula field named @DisplayArray and place it in the Report Footer:

crystalCopy codeWhilePrintingRecords;
Global NumberVar Array SalesArray;
Global NumberVar Counter;

Local StringVar ArrayOutput := "";
Local NumberVar i;

// Concatenate array values into a string
For i := 1 To Counter Do (
   ArrayOutput := ArrayOutput + ToText(SalesArray[i], 0) + ", "
);

// Remove the trailing comma and return the string
If Length(ArrayOutput) > 2 Then
   Left(ArrayOutput, Length(ArrayOutput) - 2)
Else
   "";

This formula builds a string representation of the array values, separated by commas, and trims the trailing comma.

---

Key Points

1. Formula Return Values: Crystal Reports formulas cannot directly return arrays. Use global variables to manipulate arrays and return processed results (e.g., totals or concatenated strings).
2. Global Variables: Use Global variables to share data across multiple formulas within the same report.
3. Placement of Formulas:
   • Place @InitializeArray in the Report Header.
   • Place @PopulateArray in the Details Section (suppress it to avoid clutter).
   • Place @SumArray in the Report Footer to display the total.
   • Place @DisplayArray in the Report Footer for array debugging or visualization.

---

Expected Output

Using the example dataset where {Sales.Amount} contains the values 150, 200, 300, 250, 100, the output would be:

• Array Values: 150, 200, 300, 250, 100
• Total Sales: 1000

---

By leveraging these steps, you can dynamically manage arrays in Crystal Reports to aggregate, display, or analyze data more effectively. Let me know if you have further questions or need clarification!