Search code examples
crystal-reportsreportformula

How to combine fixed n rows into columns in Crystal Reports

I have a Report (Crystal Reports) which is feeded records in the following way;

|FieldName|Value|Position| |-------|------|-------| |'Name'|'Anton'|01| |'Age'|15|02| |'Gender'|'X'|03| |'Name'|'Anton'|04| |'Age'|15|05| |'Gender'|'M'|06| |'Name'|'Anton'|07| |'Age'|15|08| |'Gender'|'M'|09| etc, etc,

So my recordset has only 3 columns, FieldName, Value and Position. I've created an formula which put all the rows in a variable like:

if record.Position = 1 then stringVar name_1 = record.Value
if record.Position = 2 then StringVar age_1 = record.Value
if record.Position = 3 then StringVar gender_1 = record.Value

and so on for the second, third, ... record

If I put this formula in the detail section, then all the variable are created. Then I create a new formula for each individual variable created and put for example name_1, age_1 and gender_1 in a footer section, name_2, age_2, gender 2 in a second footer section and so on.

My question is; Is it possible to put these records in some kind of an array to itterate this array as a normal recordset with 3 column? So in pseude; create array with 3 columns of first 3 records and so on? Purpuse is to use this array in the detail section to create an layout with 3 columns for the corresponding 3 records, so first row is record 1..3, second row = 4..6, third row = record 7..9 and so on.

Solution

  • As the image below shows, this Crystal formula can convert your data to the desired display as a single text object. But if you need to handle the data as a regular multi-row result set, you would need a 2-step process.

    enter image description here

    local stringvar Data_In_Rows := {@Data} ;
Data_In_Rows := Replace(Data_In_Rows, "|FieldName|Value|Position| |-------|------|-------| |'Name'|","");
Data_In_Rows := LEFT(Data_In_Rows, Len(Data_In_Rows) - 1) ;
Data_In_Rows := Replace(Data_In_Rows, "| |'Name'|", Chr(10) + Chr(13));
Data_In_Rows := Replace(Data_In_Rows, "'Age'|", "");
Data_In_Rows := Replace(Data_In_Rows, "'Gender'|", "");
local stringvar array myArray := Split(Data_In_Rows, Chr(10) + Chr(13));
local stringvar result ;
local numbervar NumberOfRows := UBound(myArray) ;
local numbervar rowNumber;
local stringvar row ;
local stringvar array rowArray;
For rowNumber := 1 to NumberOfRows step 1 do
(
row := myArray[rowNumber];
rowArray := Split(row, "| |");
IF UBound(rowArray) = 3 Then
result := result + LEFT(rowArray[1], Len(rowArray[1]) -3 ) + "," + LEFT(rowArray[2], Len(rowArray[2]) -3 ) + "," +LEFT(rowArray[3], Len(rowArray[3]) -3) + Chr(10) + Chr(13);
);
result := "Name,Age,Gender" + Chr(10) + Chr(13) + result;