# Sorting Arrays in Crystal Reports

Document Sample

```					         Sorting Arrays in
Crystal Reports
Using Formulas and Custom Array Functions
Presented by
Joseph Lum
Pres - Video Room Inc.
joelum@comcast.net
BABOCUG Meeting at Alameda County Environmental Health
in Alameda, Calif.                       Oct. 28, 2010
Agenda
 Review Arrays as used in Crystal Reports
 Review Bubble Sort of an Array
 Show Sort formula and function
 Limitations in Crystal Reports
 Sorting Arrays up to 1000 Rows using
custom functions
Q&A
Crystal Array Data Types
   Arrays are ordered lists of values of the
same data type
   These values are known as the array’s
[elements] starting with the 1st element
   Elements of an array can be any simple
data type or range type
   Arrays can be created using square
brackets [ ]
Crystal Limitations for Arrays
 Maximum of 1000 elements in an array!!!
 Only one dimensional arrays are supported
 Limited to 7 simple types and 6 range types
of Crystal variable types such as…
StringVar, NumberVar, CurrencyVar,
DateVar, TimeVar, DateTimeVar and
BooleanVar arrays (no range type)
 Maximum characters in an element in a
String array is 254 (since Crystal 8.5) to
65,534 characters (as of Crystal 9)
2-D Dimensional Array Analogy
Columns

Rows

BOOLEANVAR ARRAY
Crystal has 1-D Arrays

CURRENCYVAR ARRAY
TIMEVAR ARRAY
DELL

DATEVAR ARRAY
CRYSTAL

DATETIMEVAR ARRAY
BOBJ
SAP

NUMBERVAR ARRAY
APPLE                     STRINGVAR ARRAY
Formula Operators Tree
Formula Functions Tree
Defining Arrays in Formulas
// Initialize an array using square brackets
StringVar Array DOW :=
[“Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”];
// Initialize each array element
StringVar Array DayOfWeekArray [7] ;
DayOfWeekArray [1] := “Sun”;
DayOfWeekArray [2] := “Mon”;
DayOfWeekArray [3] := “Tue”;
DayOfWeekArray [4] := “Wed”;
DayOfWeekArray [5] := “Thu”;
DayOfWeekArray [6] := “Fri”;
DayOfWeekArray [7] := “Sat”;
Initialize Arrays
// Using REDIM to initialize an array with defaults
//       and to define the size of the array

StringVar Array StrArray;
Redim StrArray [1000] ;     // defaults to blanks

NumberVar Array NumArray;
Redim NumArray [1000] ; // defaults to zeroes
Using a FOR Loop
// Initialize a numeric array using FOR loop
NumberVar Array PointerArray;
// defaults to zeroes
Redim PointerArray[1000];
NumberVar i;
//
For i := 1 To 1000 Do
PointerArray[ i ] := i;
Using REDIM PRESERVE

// Using REDIM PRESERVE to retain the
// contents of an array
StringVar Array StrArray := [ “A”, “B”, “C” ];
//
Redim Preserve StrArray[26];
StrArray[ 1 ]; // still retains “A”
Result of a Formula <> Array
Result of a Formula with Array

TIP – Change the result!
Custom Crystal Functions
 As of Crystal Reports 9, custom functions are
available and are stored in the Repository
 Functions are in Basic or Crystal syntax
 Input arguments of a function are values
(not references) and do not affect original data
 The input argument of a function can be an
Array of unknown size
 What happens in function, stays in function
except…
 Only one output result can leave a function
 The result of a function CAN BE an ARRAY!!!
1st Outer Loop   2nd Outer Loop          3rd Outer Loop

Original String                                     Sorted String
Array                                               Array
WARNING
The Surgeon General has
determined that untested
Crystal Reports formulas may be
Any errors need to be reported
immediately to the presenter.
Ideal Bubble Sort Function in Crystal Syntax
Using Custom Sort Function
StringVar Array OldArray := [“C”, “B”, “A”];
StringVar Array SortedArray;
Redim SortedArray[3];         // initialized to blanks
SortedArray := mySortStrArr(OldArray);
SortedArray := mySortStrArr(OldArray, “A”);
SortedArray := mySortStrArr(OldArray, “A”, 1, 3);
SortedArray[1] & SortedArray[2] & SortedArray[3];
// Result is ABC
Since Crystal only has 1-D arrays
and most data will be stored in
multiple arrays…

So sorting arrays will be
problematic…
SORT 1 ARRAY…Other arrays will
be out-of-sync… USE Pointer Array
CUST CUST ID    TXN      DUE                                              DUE               LATE                  CLUB
NAME NUMBER DATETIME DATE                                                 TIME               FEE                 MEMBER
Smith  2345 1/1/11 1:11 1/3/11                                           12:00             \$1.23                 False
Obama 1234 2/1/11 1:11 2/3/11                                            18:00              \$0.00                 True

CURRENCYVAR ARRAY

BOOLEANVAR ARRAY
DATETIMEVAR ARRAY
NUMBERVAR ARRAY
STRINGVAR ARRAY

DATEVAR ARRAY

TIMEVAR ARRAY
(WITH TEMP STRING AND POINTER ARRAYS SIMULTANEOUSLY)

1st Outer Loop   2nd Outer Loop     3rd Outer Loop

Original / Temp                             Sorted Temp
String Array                               String Array

A
B
C
D

Original                                 Sorted Pointer
Pointer Array                                  Array
Ideal Bubble Sort Function Returns Pointers Instead
Using Custom Sort Functions
StringVar Array OldArray := [“C”, “B”, “A”];
StringVar Array SortedArray;
Redim SortedArray[3];          // initialized to blanks
NumberVar Array PointerArray;
Redim PointerArray[3];         // initialized to zeroes
SortedArray := mySortStrArr(OldArray);
SortedArray[1] & SortedArray[2] & SortedArray[3];
// Result is ABC
PointerArray := mySortStrArrGetPointerArr(OldArray);
Totext(PointerArray[1], “#”)& Totext(PointerArray[2], “#”)&
Totext(PointerArray[3], “#”);
// Result is 321
Sort Numeric Array and Return a
Pointer Array for Display Purposes
Array with 446 Rows Crashes   Oops!

Loop limitation!!!
Loop Limitations in Crystal Reports

 As of Crystal 9…
 A formula cannot exceed 99,999 loops!!!
 A function cannot exceed 99,999 loops!!!

 As of Crystal 8.5…
 A formula cannot exceed 29,999 loops!!!
Simple Bubble Sort
has an outer loop and an inner loop
   For a 1,000 element array, the outer loop starts
from the 1st row to the 999th row
   When the outer loop is on the 1st row (ith row),
the inner loop starts from the 2nd row (ith + 1,
one row above the current row of the outer loop)
to the 1,000th row… etc.
   No problem sorting an array of 445 rows
   Exceeds loop limitation for a 446 to 1000 row
array and the report crashes
Simulate Bubble Sort – Loop Counts
Report Crashes at the 106th Row
Need 500499/99999 = 6 passes
w/ each pass under 99,999 loops
Need minimum 6 passes to avoid
loop limitations for Bubble Sort
 Pass 1 – Outer loop 1 to 99
 Pass 2 – Outer loop 100 to 199
 Pass 3 – Outer loop 200 to 299
 Pass 4 – Outer loop 300 to 439
 Pass 5 – Outer loop 440 to 649

 Pass 6 – Outer loop 650 to 999
FOR Loops to Sort 1000 Array Rows
// the ideal sort formula would crash the report
FOR i := 1 to 999 DO         // outer loop from 1 to 999 rows
FOR j := i+1 to 1000 DO // inner loop

// separate the outer loop into multiple passes
// and place in separate formulas
FOR i := 1 to 99 DO             // outer loop
FOR j := i+1 to 1000   DO   // inner loop   Max 99,999 loops
FOR i := 100 to 199 DO          // outer loop
FOR j := i+1 to 1000   DO   // inner loop   Max 99,999 loops
FOR i := 200 to 299 DO          // outer loop
FOR j := i+1 to 1000   DO   // inner loop   Max 99,999 loops
FOR i := 300 to 439 DO          // outer loop
FOR j := i+1 to 1000   DO   // inner loop
Max 99,999 loops
FOR i := 440 to 649 DO          // outer loop
FOR j := i+1 to 1000   DO   // inner loop
Max 99,999 loops
FOR i := 650 to 999 DO          // outer loop
FOR j := i+1 to 1000   DO   // inner loop
Max 99,999 loops
6 Passes for Simple Bubble Sort
Max 99,999
loops
1st Pass formula from loop 1 to 99
has less than 99,999 total loops

Max 99,999
loops
Pass 2 formula from loop 100 to
199 has less than 99,999 loops

Max 99,999
loops
Bubble Sort producing a Pointer
Array using functions needs…
   A maximum of 1,000 loops to copy the array to
be sorted to a Temporary array (done outside
the function)
   A maximum of 1,000 loops to initialize the
Pointer array
   A set of a maximum of 999 outer loops with
inner loops for the Pointer array
   Another set of a maximum of 999 outer loops
with inner loops for the Temporary array
Need 500499/49500 = 11 passes
Use 12 passes using functions
Max 1,000 loops only for Pass 1

Max 47,999 loops
for Pass 1
or 49,999 loops
for Pass 2 thru 12
Pass 1 formula has
below
1st Pass formula from loop 1 to 49
has less than 99,999 total loops

Max 1,000 loops
Max 49,999 loops

Max 48,999 loops
Pass 2 formula from loop 50 to 99
has less than 99,999 loops

Max 49,999   Max 49,999
loops        loops
12 formulas - one for each pass in
separate Report Footers
After sorting, the Pointer Array
 Can be used along with the original arrays
for display purposes
 Can be used to export original arrays to
Excel
Formula to Display Array
Great Display of Arrays in PDF
Poor Display of Arrays in Excel
TIP to Export Arrays to Excel NEATLY
   Get your DBA to create a numeric table
   Initialize the table with 1000 rows numbered
1 to 1000
   Define the arrays as SHARED between the
main report and the subreport
   Create subreport to display the arrays
   Create formula in the main report to pass the
number of elements to display via the
   Create subreport’s record selection formula
   Create formulas to display array elements
Numeric Table w/ 1000 Rows
Subreport to Display Arrays

Group # 1 will loop from 1 to the number
passed via Parameter to the Subreport…
once for each row of the “Shared” Pointer
Array allowing the formulas in the Detail
Section to display the stored arrays
Subreport Record Selection
Formula

Formula passed as a Parameter to Subreport
e.g. Cust Name Formula
Export to Excel NEATLY
Please Sir, May I Have Some More?