How To Use Arrays In Reports

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.

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

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 re-dimension 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. We are 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 we change the value of varA[2][1] to 17 and the report system prints it as @varA[2][1]. 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 we declare an array varB[3][3] and we will make various fields different types

@varB[3][3] = 0

And now we assign different values to each cell to show how we 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.

SQL Queries In Arrays

Collect! can support SQL queries in the report writer. The query is sent to the server, then the results are returns to an array.

SQL arrays do not require defining (hence [][]) as the result set from SQL server will auto-define the dimensions. SQL arrays are 2 dimensional only. The number of SELECT fields will determine the horizontal axis and the number of records will determine the vertical axis. To obtain the dimension, please see the @COUNT command below.

Syntax: @varArray[][] = @SQL({query})

Example: @varArray[][] = @SQL(SELECT de_name,de_number from debtor where de_active = 'A')

If your SQL query is not inside a loop, you can place the query on multiple lines.

Example: @varArray[][] = @SQL(
   SELECT de_name,de_number
   FROM debtor
   WHERE de_active = 'A'
   )

Example: @varArray[][] = @SQL(
   SELECT SUM(de_principal),SUM(de_owing)
   FROM debtor
   WHERE de_active = 'A'
   )

Once the array is returned, you can output the array as is or you can use a loop to parse the data. Here is an example of outputting an array using a loop:

@varArrayCount# = @COUNT(@varArray)

@varArrayIndex# = 1

@while (@varArrayIndex <= @varArrayCount)

@varArray[@varArrayIndex][1]<40> @varArray[@varArrayIndex][2]<10>

@varArrayIndex += 1

@endwhile

@varArrayIndex is set to 1 before the loop in the event that @varArrayCount is 0; otherwise 0=0 and the report would output a blank line. The @varArrayIndex is incremented at the end of the loop, so the first line is not skipped.

SQL Queries Using Printable Information Codes

For ease of use, Collect! can translate printable information codes into the database forms and fields so you don't have to learn the schema. The SQL printable codes are the same as the regular codes, but start with an & instead.

Example: "&de" will translate to the table "debtor" and "&de.na" will translate to the field name "de_name"

Record relationships like debtors and transactions must be linked with the SQL field names.

You can reference regular print codes and variables in the queries, such as in the WHERE filters.

Example:
@varStatus* = ACT

@varArray[][] = @SQL(

   SELECT &de.na,&de.fi
   FROM &de
   JOIN &tr ON tr_rowid_debtor = de_rowid
   WHERE &de.mo
   AND &de.sta = @varStatus
   AND (&tr.pda BETWEEN @tsr.fr AND @tsf.to)
   )

Counting The Number Of Rows And Columns In An Array

The @COUNT command returns the number of rows or columns in an array. By default, it returns the size of the first dimension of the array, to specify the size of other dimensions, follow the name with the dimension number separated by a comma. The dimension number is 0 based. This command is not case sensitive.

Syntax: @COUNT(@varArray) returns the size of the first dimension, typically the number of rows.

Syntax: @COUNT(@varArray,0) is equivalent to @count(@varArray) and returns the size of the first dimension.

Syntax: @COUNT(@varArray,1) returns the count of the second dimension.

Example:

@varCount# = @count(@varArray)

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 indexes 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 indexes 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.

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 we 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 indexes 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]