Array Tutorial
This powerful feature of the Report Writer is great for managerial
reports and scanning large volumes of data. Coding is kept to a
minimum and data needs to be read only once. This simplifies
writing and speeds up execution.
The Demonstration Database ships with a six-part
array report Tutorial in the Report Definitions. Look for " Array
Tutorial REPORTS." For your convenience, the text of these
array reports is reproduced in this document starting with
the section, Report Array Tutorial - Step 1.
What Is An Array?
An array is a variable that has multiple dimensions. These
dimensions can have values stored in them. The size of
these dimensions can be specified. You can use arrays in
reports. Instead of many lines of code in the report writer,
arrays provide a compact way to write reports, store data
and print it out.
An array created in Collect! can have unlimited dimensions
or columns. To explain this, let's start with a 2 dimensional
array with room to store 5 records.
This is how you would define this array:
@varMyArray[5][2]
When looking at an array, it helps to picture it. You can
think of it as a spreadsheet. This array would have 5 rows
and 2 columns, as illustrated below.
We can assign values to these locations in the array. To put
data in each row of the first column, you would increment the
value of the first dimension of the array while keeping the
second dimension as 1.
@varMyArray[1][1] = 1
@varMyArray[2][1] = 2
@varMyArray[3][1] = 3
@varMyArray[4][1] = 4
@varMyArray[5][1] = 5
This would produce the following results:
To specify values for the second column of the array,
we would change the value of the second dimension
in the array to 2. This means the second column.
@varMyArray[1][2] = 5
@varMyArray[2][2] = 4
@varMyArray[3][2] = 3
@varMyArray[4][2] = 2
@varMyArray[5][2] = 1
Now the array will look like this:
Each location in the array can hold a different type of
data. For instance, in our example, we could specify that we
want one column or dimension of the array to hold strings and
the other to hold numbers.
First, we define the array:
@varMyArray[5][2]
Then, when we store the values, we use the symbols
for variable types, for example, # for
integers, in our first column, and *
for strings, in our second column.
@varMyArray[1][1]# = 1
@varMyArray[2][1]# = 2
@varMyArray[3][1]# = 3
@varMyArray[4][1]# = 4
@varMyArray[5][1]# = 5
@varMyArray[1][2]* = Sam Jones
@varMyArray[2][2]* = Karen Smith
@varMyArray[3][2]* = Michael Black
@varMyArray[4][2]* = Thomas Moore
@varMyArray[5][2]* = Mary Allen
5 R o w s
| 2 Columns
1 | Sam Jones |
2 | Karen Smith |
3 | Michael Black |
4 | Thomas Moore |
5 | Mary Allen |
|
There are two ways we can print information from the array.
The easiest is simply to call the array in your report.
@varMyArray
This will print the entire array:
1 Sam Jones
2 Karen Smith
3 Michael Black
4 Thomas Moore
5 Mary Allen
To print a single value from the array, we would specify
the exact location where that value is stored in the
array. For instance, to print Michael Black in our
report, we would put the following line in our report.
@varMyArray[3][2]
For example,
Cosigner: @varMyArray[3][2]
would print out Cosigner: Michael Black,
because Michael Black is the value stored in the third row,
second column of the our array.
To illustrate this in detail, six array reports are shipped with
the Demonstration database. You can print them and
examine the results. The steps below are taken from these
array reports in the Demodb.
Report Array Tutorial - Step 1
This is the first step in a multi report tutorial on using arrays
in reports in Collect!.
Description
This example shows how an array is declared and how
variables are assigned to cells in the array. In order to
use this tutorial you should print this report and then look
at the output. Then review the report source code to see
how it's done.
Arrays are used to store analytical data such as totals
used in reports, for instance the Monthly Step/Spindown Report.
Declaring An Array
Declaring arrays is simple, as the first reference to a
variable with square brackets immediately after the
variable name declares the array to the specified size.
Array indexes are 1 based. Arrays can be
multidimensional with no practical limit to the number
of dimensions. You cannot redimension an array after
initial declaration.
The line below (varA[2][2]) initializes a 2 by 2 array. You would
declare a multidimensional array using the same syntax. For
example varX[10][10][10] declares an array with 1000 cells.
@varA[2][2] = 0
@varI = 2
Next we assign values to each array element. See how you can
use a variable to specify an index into the array. I am assigning
the value @varI< to a variable named varI and using that to
address individual elements in an array.
@varA[1][1] = 1
@varA[1][2] = 2
@varA[2][1] = 3
@varA[2][@varI] = 4
Printing
For output you can access each individual cell in your array or
you can use a single command to print your whole array
automatically.
Printing The Array
You can print the whole array with one command (varA). This is
a key feature to minimize your report code. We'll learn more
about printing arrays in later examples. Notice how a one line
command can print more than one piece of information
automatically.
@varA
Printing A Cell
In this paragraph we access an individual array element.
If you want to access some cell in the array you can do
so with the following syntax. Using the current information
in our array varA the array cell varA[2][1] contains the
value @varA[2][1]<.
@varA[2][1] = 17
Now I change the value of varA[2][1] to 17 and the report
system prints it as @varA[2][1]. Hah! Notice how the
formatting character < (or lack thereof) altered output in
the prior example.
Cell Data Types
Next we'll learn how to assign different data types to different
cells in an array. It is actually quite simple, since you just
make sure the type of data you are assigning to a cell is the
type you want. You can override that by declaring a data type
identifier when assigning a value to a
cell (e.g. varB[1][2]# = 101).
The statement varB[2][3]$ = 3456.78 below could be
changed to varB[2][3] = @de.pa and the report system
would understand that the debtor paid field requires
currency formatting because the source field, @de.pa
is a currency field. Assigning directly to a field can
eliminate the need for the $ or other format specifier
at the end of any array field assignment.
Here I declare an array varB[3][3] and I will make various
fields different types
@varB[3][3] = 0
And now I assign different values to each cell to show
how I can use labels with the array.
@varB[1][1]* = Row 1
@varB[1][2]* = Col 1
@varB[1][3]* = Col 2
@varB[2][1]* = Row 2
@varB[2][@varI]$ = 123.45
@varB[2][3]$ = 3456.78
@varB[3][1]* = Row 3
@varB[3][2]% = 12.567
@varB[3][3]% = 17.657
Now print varB without any specific formatting information:
@varB
Print Formatting
And now I'll print varB telling it to format 10 characters
wide, right justified. When using formatting commands
with an array the formatting applies to all elements of
the array.
@varB>10>
Cell Data Type Reassignment
Now to cap it off we'll assign years in the varB array
headings to more closely emulate the
monthly step/spindown report framework that we
are working to demonstrate throughout this tutorial.
@varB[1][1]# = 0
@varB[1][2]# = 1995
@varB[1][3]# = 1996
@varB[2][1]# = 1995
@varB[3][1]# = 1996
@varB>10>
OK, so that's it for this simple introduction to reports.
You can see how to declare and use basic arrays in a
report. Next in this series we'll look at using dates to
create an index so that we can use these arrays efficiently.
Report Array Tutorial - Step 2
This is the second step in a multi report tutorial
demonstrating how to use arrays in reports in Collect!.
Description
This report shows how to use dates to obtain an array index.
The goal is to calculate the array index of an item based on
the date. Date indices are important when using arrays to
accumulate and report statistical data.
In order to use this tutorial you should print this report
and then look at the output. Then review the report source code
to see how it's done.
Date Field Parsers
Here we use the little known date math parsers d, m and y.
These parsers are not documented at present but hopefully will
be shortly.
You are probably aware of the use of the <yyyy, MMM, dd>
controls when outputting dates, but those can't (for a few
reasons) be used with parts of date fields combined with
variables and math. That's where these special date parsers
come in. The results of these parsers are always numbers
and you must assign the result to an integer type
variable for these things to work properly.
Parsing Relative Dates
This first example shows how you can get the numeric month,
day and year of today's date, and how we can get the same
for listed date on 5 accounts. Notice how the standard date
commands work with the d command.
First we'll break apart today's date: @d
@varD1a# = @d<MM>
@varD1b# = @d<dd>
@varD1c# = @d<yyyy>
Month: @varD1a
Day: @varD1b
Year: @varD1c
You might have considered parsing m or y date commands but
if you think about it there are easier ways of dealing with
monthly and yearly dates.
Parsing Date Fields
Now we'll list 5 accounts with their listed dates. Notice how
the special date parsers d, m and y are used for the date field
to isolate the day, month and year components.
Date Month Day Year
@de max = 5
@varD2a# = @de.li<m>
@varD2b# = @de.li<d>
@varD2c# = @de.li<y>
@de.li @varD2a @varD2b @varD2c
@de
Index By Year
This next example deals with creating an index based on
years.
The sample code calculates a date index by year on the
Listed Date field. Index is number of years back from
today's year.
Date Year Index
@varY# = 0
@varI# = 0
@de max = 10
@varY = @de.li<y>
@varI = @(varD1c-varY)
@de.li @varY @varI
@de
Index By Month
Now an example of index by month up to 24 months back.
The following loop scans accounts and produces an index
number from 1 to 24 counting from this month up to 24
months back.
Set the reference to today's date : @d
@varY# = 0
@varYo# = 0
@varT = @d<yyyy>
@varTo = @d<MM>
varT : @varT
varTo: @varTo
Year Month Years Months
Offset Total Index
@de max = 10
@varY = @de.li<y>
@varYo = @de.li<m>
@varD = @(varT-varY)
@varS = @(varTo-varYo)
@varN = @(varD*12)
@varO = @(varN+varS)
@varP = 0
@varP = @varO if (@varO < 25)
@varY<10> @varYo<10> @varD @varN<10> @varS @varO<10> @varP<10>
@de
Minimal Month Index
Here is the same loop in a minimal configuration that you
would use in an actual report. Note how we've eliminated
intermediate variables to minimize number of lines parsed
per loop iteration.
Year Month Months Index
@de max = 10
@varY = @de.li<y>
@varYo = @de.li<m>
@varO = @((varT-varY)*12+(varTo-varYo))
@varP = 0
@varP = @varO if (@varO < 25)
@varY<10> @varYo<10> @varO<10> @varP<10>
@de
Month Index Reversed
This month index code results in totals at the top right of the
spindown grid. For historical business analysis this index
calculation is useful.
Year Month Months Index
@de max = 10
@varY = @de.li<y>
@varYo = @de.li<m>
@varO = @((varT-varY)*12+(varTo-varYo))
@varP = 0
@varP = @(24-varO) if (@varO < 26)
@varY<10> @varYo<10> @varO<10> @varP<10>
@de
Summary
This completes our introduction to creating date indexes.
You have learned how to create date indices by month
and by year both forward and reversed.
Next in this series we'll look at the plus equals operator
and then finally we will combine this technique with
using arrays.
Report Array Tutorial - Step 3
This is the third step in a multi report tutorial on using
arrays in reports.
Description
This report shows how to use the plus equals and related
math operators in reports. This technique is an efficient
way of accumulating data in a report. This is often used
with arrays in analytic reports.
Enhanced Math Operators
The enhanced math operators look like this:
Plus equals ...... +=
Minus equals ..... -=
Multiply equals .. *=
Divide equals .... /=
A typical variable assignment using these operators would look like this:
@varA# = 1
Assigns the value 1 to the variable
@varA += 1
This command adds 1 to varA to result in 2. That's all
there is to it. That beats typing varA = varA+1 and it helps
the report system run faster as less command parsing is
needed to cause the desired result.
@varA *= 4
The command above multiplies varA by 4 which results in 8.
I'll leave it to the reader to experiment with these commands.
This completes our introduction to the plus equals and other
operators. Next in this series we'll look at combining these
operators with date index math and arrays to efficiently create
a monthly step/spindown report framework.
Report Array Tutorial - Step 4
This is the fourth step in a series showing how to
use arrays in reports.
This report combines the prior three tutorials in this series
and applies that knowledge gained in practical examples.
Description
This report prompts for a client number and then scans the debtors
(up to 100 maximum) and their transactions for the selected client.
If the transaction is a payment type then the amount paid is placed
in the proper cell of the array based on the year of the charged date
and the payment date. Note how this is a 10 year spindown.
Analysis By Year
// declarations
// array for amounts
@varA[12][13] = 0
// indexing variables
@varY# = 0
@varI# = 0
@varJ# = 0
// and loop through the stuff
@cl where (@cl.cl = ?) max = 1
@cl.de max = 100
// get index by debtor's date
@varY = @de.ch<y>
@varI = 0
@varI = @(2004-varY) if (@varY = 1993 .. 2003)
@varI += 1
@de.tr
@varAmt$ = 0
@varAmt = @(tr.di+tr.tu) if (@tr.pa = X)
// get index by transaction date
@varY = @tr.pd<y>
@varJ = 0
@varJ = @(2004-varY) if (@varY = 1993 .. 2003)
@varJ += 1
// and accumulate the amount into the cell
@varA[@varI][@varJ] += @varAmt
@de.tr
@cl.de
@cl
Here we gather the dollar amounts and then print the array varA:
@varA
Adding Titles And Some Totals
What we'll do now is modify the report a little to get headings
for the rows and columns in the grid. We'll also add the
debtor principal amounts in column 2.
// declare the bigger array for amounts and headings
@varB[13][18] = 0
@varY# = 0
@varI# = 0
@varJ# = 0
// Here's a trick to show only the current client to avoid prompting
// the user a second time.
@cl where (@cl.cl = @cl.cl) max = 1
@cl.de max = 100
@varY = @de.ch<y>
@varI = 2
@varI = @(2006-varY) if (@varY = 1993 .. 2003)
// put the date into the heading cell
@varB[@varI][1]# = @varY if (@varY > 1992)
@varB[@varI][2] += @de.pr
@de.tr
@varAmt$ = 0
@varAmt = @(tr.di+tr.tu) if (@tr.pa = X)
@varY = @tr.pd<y>
@varJ = 3
@varJ = @(2006-varY) if (@varY = 1993 .. 2003)
@varB[@varI][@varJ] += @varAmt
// put the date into the heading cell
@varB[1][@varJ]# = @varY
@de.tr
@cl.de
@cl
@varB>12>
Here we printed the array varB automatically. Notice how I
used formatting control because the dollar amount and date
fields are different types and so the array wouldn't line up
otherwise.
Reversing The Order
With a small manipulation of the indexing math we can
display things in a different order. in this example both
indices are reversed.
@varC[13][18] = 0
@varY# = 0
@varI# = 0
@varJ# = 0
@cl where (@cl.cl = @cl.cl) max = 1
@cl.de
@varY = @de.ch<y>
@varI = 2
@varI = @(varY-1990) if (@varY = 1993 .. 2003)
@varC[@varI][1]# = @varY if (@varY > 1992)
@varC[@varI][2] += @de.pr
@de.tr
@varAmt$ = 0
@varAmt = @(tr.di+tr.tu) if (@tr.pa = X)
@varY = @tr.pd<y>
@varJ = 3
@varJ = @(varY-1990) if (@varY = 1993 .. 2003)
@varC[@varI][@varJ] += @varAmt
@varC[1][@varJ]# = @varY
@de.tr
@cl.de
@cl
@varC>12>
Summary
Here you have seen how to assign data into an array indexed
by year and how to print that data. You also learned about
reversing the index order.
Report Array Tutorial - Step 5
This is the fifth step in a series showing how to
use arrays in reports.
This report combines the prior three tutorials in this series
and applies that knowledge gained in practical examples.
Description
This report is a 12 month version of the report developed in step 4
of this series.
This report prompts for a client number and then scans the debtors
(up to 100 maximum) and their transactions for the selected client.
If the transaction is a payment type then the amount paid is placed
in the proper cell of the array based on the year of the charged date
and the payment date. Note how this is a 12 month spindown.
12 Month Spindown
// declare the array for amounts and headings
@varC[15][15] = 0
// and get reference dates
@varT = @d<yyyy>
@varTo = @d<MM>
@varY# = 0
@varYo# = 0
@varI# = 0
@varJ# = 0
@cl where (@cl.cl = ?) max = 1
@cl.de max = 300
@varY = @de.ch<y>
@varYo = @de.ch<m>
@varO = @((varT-varY)*12+(varTo-varYo))
@varI = 2
@varI = @(16-varO) if (@varO < 14)
@varS* = @(varY*100+varYo)
@varC[@varI][1]# = @varS if (@varI > 2)
@varAmt# = @de.pr
@varC[@varI][2] += @varAmt if (@varI > 2)
@de.tr
@varY = @tr.pd<y>
@varYo = @tr.pd<m>
@varS* = @(varY*100+varYo)
@varO = @((varT-varY)*12+(varTo-varYo))
@varJ = 2
@varJ = @(16-varO) if (@varO < 14)
@varAmt# = 0
@varAmt = @(tr.di+tr.tu) if (@tr.pa = X)
@varAmt = 0 if (@varJ < 3)
@varC[@varI][@varJ] += @varAmt if (@varI > 2)
@varC[1][@varJ]* = @varS if (@varJ > 2)
@de.tr
@cl.de
@cl
@varC[1][1]* = Charged
@varC[1][2]* = Princ
@varC>8>
Report Array Tutorial - Step 6
This is the fifth step in a series showing how to
use arrays in reports.
This report combines the prior three tutorials in this series
and applies that knowledge gained in practical examples.
Description
This report is a 24 month version of the report developed in step 5
of this series.
This report prompts for a client number and then scans the debtors
and their transactions for the selected client. If the transaction is
a payment type then the amount paid is placed in the proper cell of
the array based on the year of the charged date and the payment
date. Note how this is a 24 month spindown.
24 Month Spindown
// declare the bigger array for amounts and headings
@varD[32][33] = 0
@varT = @d<yyyy>
@varTo = @d<MM>
@varY# = 0
@varYo# = 0
@varI# = 0
@varJ# = 0
@cl where (@cl.cl = ?) max = 1
@cl.de
@varY = @de.ch<y>
@varYo = @de.ch<m>
@varO = @((varT-varY)*12+(varTo-varYo))
@varI = 2
@varI = @(33-varO) if (@varO < 32)
@varS* = @(varY*100+varYo)
@varD[@varI][1]# = @varS if (@varI > 2)
@varAmt# = 0
@varAmt = @de.pr if (@varI > 2)
@varD[@varI][2] += @varAmt
@de.tr
@varY = @tr.pd<y>
@varYo = @tr.pd<m>
@varS* = @(varY*100+varYo)
@varO = @((varT-varY)*12+(varTo-varYo))
@varJ = 2
@varJ = @(33-varO) if (@varO < 32)
@varAmt# = 0
@varAmt = @(tr.di+tr.tu) if (@tr.pa = X)
@varAmt = 0 if (@varI < 3)
@varD[@varI][@varJ] += @varAmt if (@varJ > 2)
@varD[1][@varJ]* = @varS if (@varJ > 2)
@de.tr
@cl.de
@cl
@varD[1][1]* = CHARGED
@varD[1][2]* = PRINC
@varD>8>
Report Array Tutorial - Extras
Now that you have mastered the art of creating and
using arrays here are some additional tips.
Dynamic Array
You can set the size of your array dynamically by
simply counting the number of items you will be
outputting.
The following example counts the number of operators
before declaring an array for calculating operator
totals.
// Count the number of operators so we can size
// the array properly.
@tvarArrSize = 3
@op no total
@tvarArrSize += 1
@op
// Now declare the array with the number of rows
// you just counted.
@varOpTotals[@tvarArraySize][2]
|
Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.
Please click this link to send us your comments: helpinfo@collect.org