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.

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:

You must have an equals sign followed by a blank value. You do not need to specify a format line string or number, yet. That will happen later.

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.

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.

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:

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.

There are two ways we can print information from the array. The easiest is simply to call the array in your report.

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.

For example:

Would print out Cosigner: Michael Black, because Michael Black is the value stored in the third row, second column of the our array.

Array Tutorial - Step 1

This step 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 the Array

As shown above, 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.

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.

Printing the Entire 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.

Printing a Single Cell

If you want to access some cell in the array you can do so with the following syntax. Using the array cell varA[2][1] will output just the contents of that cell.

Cell Data Types

Here we declare an array varB[3][3] and we will make various fields different types.

And now we assign different values to each cell to show how we can use labels with the array.

Now print varB without any specific formatting information:

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.

Cell Data Type Reassignment

Using a different data type like whole number (#), you can change the type of a cell.

Array Tutorial - Step 2

This step 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.

Here we use the date math parsers d, m and y.

Parsing 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

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.

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.

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.

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

Index by Month - Minified

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.

Index by Month - 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.

Array Tutorial - Step 3

This step 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.

A typical variable assignment using these operators would look like this:

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.

The command above multiplies varA by 4 which results in 8.

Next, we'll look at combining these operators with date index math and arrays to efficiently create a monthly step/spindown report framework.

Array Tutorial - Step 4

This example 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.

Here we gather the dollar amounts and then print the array varA: @varA

Adding Titles and Some Totals

Now, let's 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.

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.

Array Tutorial - Step 5

This example is a 12 month version of the code demonstrated in step 4. It 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.

Array Tutorial - Step 6

This example is a 24 month version of the code demonstrated in step 5. It 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.

Dynamic Arrays

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.

WARNING: This requires your data to be processed twice, which would slow down more complex reports. For more complex reports that only require 2 dimensions in the Array, please refer to SQL Queries below.

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.

Examples:

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:

@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, such as INNER JOIN &cl on cl_rowid = de_rowid_client

You can reference regular print codes and variables in the queries.

Example:

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: