Advanced Use of Derived Fields

This topic assumes you understand the basics of Report Generator.

Advanced use of derived fields is easier for somebody who has an understanding of programming but does not mean that you need to be a programmer.

Note: An important feature is the Report Generator library routines that accomplish the same results as global library routines. Security considerations dictate that only priority nine AUTOLINE users have access to global library routines. However, some of the global library routines can be called via Report Generator routines that mirror their functionality. An example of this is the global routine called 'gb_date() which is called via the Report Generator routine called 'rg_date(). Both routines have similar names and require the same parameters. The difference is that only priority nine users will have access to 'gb_date().

What do you want to do?

• Read Points to Remember
• View Useful KCML Commands
• View Useful Report Generator Library Routines
• Loop Around Multiple Records in a Secondary File

See also:

Basic Use of Derived Fields

Points to Remember

• Derived field names can be up to eight characters long.
• The calculation can be up to 64 characters in length.
• Derived fields are executed in order. Do not include a derived field value in an earlier derived field. Only do so in a later derived field.
• You may use your own local field names, however remember they will always have a zero value until set by you, and then they will need to be zeroed before they are used for the next record loaded.
• If you create your own alpha variable, it will assume a dimensioned length of 16 characters. You must dimension it yourself if it needs to be longer. (It is better to use a properly named alpha derived field in these cases as they are automatically dimensioned to 30 characters.)
• Derived fields may execute several times for each record, the number of times will vary depending on many different things such as the table linking, the selection and sort sequence.
• Derived fields do not have to have names. If a derived field is being used simply to progress a few lines of KCML code then do not bother to name it.

Sub-Routines

The KCML language uses subroutines in a standard way. You may call a subroutine by name, the name is preceded by a single apostrophe mark ('). Depending on how the routine has been written, it will expect one or more parameters to be passed to it when it is called. These extra parameters are entered in brackets after the subroutine name, separated by a comma where more than one parameter is being sent. An example of this would be the subroutine which turns a day number into a text version of the date.

The subroutine is known as 'rg_date(), and will expect three parameters to be sent to it. The first is a day number, the second is an alpha field which will receive the text version of the date and the third is the length of the subsequent text field.

gosub 'rg_date( !0_daynum , sym(!0_date$) , 10 )

As can be seen here the rg_date subroutine name starts with a single apostrophe. The name is followed with brackets and holds three values, the field name containing the day number to be converted, the field name which will receive the text version of the date and finally the length of the subsequent text version of the date field. This is representative of a standard subroutine call syntax.

In most cases you will want to collect the value of a field variable on the return from the subroutine call. Where subroutines are explained in this topic, the details and name of the return variables are explained.

Tip: There is no substitute for practice. Get to know the Report Generator well and things will start to drop into place. Take each stage carefully and logically, above all make sure that you have worked out what you are trying to achieve before writing the report.

Useful KCML Commands

The examples used here are all imaginary and only serve to illustrate the functions. You need to adapt the use of each function according to the report being written.

The Report Generator enables you to enter KCML commands in place of derived field calculations. These simple commands are often used by the more advanced users of Report Generator and will prove very useful.

This topic will mention the following functions or key words.

• ABS()
• AND
• CONTINUE
• CONVERT
• DATE
• DIM
• DO - END DO
• ERROR
• FIX()
• FOR - TO
• INT()
• LEN()
• MAX()
• MIN()
• MOD()
• REM
• ROUND()
• STR()

Knowledge of programming is an obvious advantage in this instance but is not essential. The section that follows will outline a few of the more useful KCML commands and will provide some possible examples as to their use. Do not take the examples too seriously, they are used merely to illustrate the use of the KCML commands.

Why would I use a KCML Command?

Most reports that are written do not need them, however in some instances they will provide a quick, easy and efficient way to carry out a function that would take many more derived fields.

Back to heading

KCML - FOR - TO Function

In the Daily Rentals module there is a field which has 20 occurrences. This means that the field can store up to 20 values. The field in question holds details of up to 20 deposit payments that may have been made. This report requires all 20 of these values to be added up and the total shown on the report.

An occurs field is named differently in derived fields, rather than just the field name preceded by the table !ID, !1_PAYVALUE, it is followed by a pair of brackets showing which of the 20 occurrences is being used. Therefore the third payment that was made would be referred to as !1_PAYVALUE(3)

Example 1:

This set of nine derived fields will accomplish the desired result. However, it could be done much more efficiently with KCML commands as follows.

In this case we have reduced the calculation down to one derived field. Here is an explanation of the new calculation.

0: FOR N=1 TO 20 : !0_PVAL1+=!1_PAYVALUE(N) : NEXT N

The first part of the calculation is 0: and all this does is set the value of the derived field PVAL1 to zero before the calculation which follows. It is very important not to forget this part of the line. Every derived field that is written will have a program line generated by the COMPILE function, and in this case that program line will start by setting the derived field called PVAL1 to zero. The colon that follows allows us to add extra statements to the end of the line being generated.

The next part of the calculation allows us to set up the counter. FOR N=1 TO 20 : This is the start of a FOR-TO loop which we will use. These loops require the three keywords FOR, TO and NEXT as well as a counter to be set up to allow execution of the loop a given number of times. In this case we are setting a counter variable (N), and a counter range (from 1 to 20). We are therefore preparing to execute the body of the loop 20 times.

The main body of the loop follows. In this case it is only executing one statement, in other cases you may enter several statements before the final part of the loop. Here we simply use !0_PVAL1+=!1_PAYVALUE(N):

This will add the value found in the Nth element of the field !1_PAYVALUE on to the derived field PVAL1. The += symbol is a quick way of writing !0_PVAL1 = !0_PVAL! +

Each time we go around the loop, the value of N will increase by 1 and therefore the element used within the field !1_PAYVALUE will increment.

The final part of the loop (NEXT N), will actually increase the value of the loop variable N, and unless N has reached its maximum value (in this case 20), the program will return to the start of the loop.

Back to heading

KCML - ABS() Function

The ABS() function will provide an absolute value of a numeric field or variable. The easiest way to think of it is that it will always provide a positive number.

It will provide a foolproof method of expressing a field as a positive number. Obviously a field multiplied by (-1) will turn a negative number positive, however where a field may be either side of zero it will also turn the positive ones negative.

Assuming the following values of BASEVAL, the derived field PSVAL would be assigned the values shown.

• BASEVAL PSVAL

•  -1223.56 = 1223.56

•   450.67 = 450.67

•   -0.89 = 0.89

Back to heading

KCML - & (concatenation) Function

This function is used to concatenate alpha fields or variables. The example shown will join three alpha fields which are dimensioned as 10, 30 and 35 characters respectively. Empty spaces at the end of fields are ignored, so they have to be built into the assembly routine.

Note: If the length of the derived field needs to be bigger than 32 characters then use the special version of the derived field name which is described in the derived field section of the Format Stationery topic.

Back to heading

KCML - DATE Function

The DATE function simply returns the current date as an alpha variable in the form YYMMDD. The current day number within the month would therefore be easily stripped out using a string function.

Back to heading

KCML - TIME Function

Following on from the date function, KCML also offers a time function to determine the current system time. It is an alpha variable which holds the time in hours, minutes and seconds in the format HHMMSS. The time can be manipulated in a similar way to the example shown above by using a STR() command.

Back to heading

KCML - ERROR Function

The ERROR function allows you to take control of a calculation should an error occur. The obvious use for this command is to trap and deal with division by zero errors.

If a normal profit percentage calculation is looked at we can see that division is included in the formula. In this case we take the profit, divided by the sale price, multiplied by 100. If the profit is zero, the system will normally error during the calculation and the progress of the report is halted. In order to avoid this, the author of the report must guard against such an eventuality. There are two ways of doing this.

The first is to use a simple ternary, this will check for zero values and only do the calculation if both fields have a non-zero value.

The second way is to use the ERROR function to trap and deal with the error as it occurs.

Both of the above deal with the problem quite comfortably. The first one simply multiplies the fields together, if one of them has a zero value then the answer has to be zero, in which case the second ternary answer is selected. The second example simply deals with the error if it occurs by setting the field to zero.

Back to heading

KCML - FIX() Function

The FIX() function will truncate a number toward zero to form an integer. (An integer is a whole number.) The FIX() function is a sister to the INT() function which will also provide an integral result, however there is one main difference. FIX() will always truncate the number toward zero. Thus a FIX() function performed on the following values would result in the given answers.

• FIX(90.993) = 90

• FIX(-89.876) = -89

Back to heading

KCML - INT() Function

The INT() function will truncate a number by taking the largest whole number that is less than the number you started with. (An integer is a whole number.) The INT() function is a sister to the FIX() function which will also provide an integral result, however there is one main difference. INT() will always truncate the number toward downwards. Thus a INT() function performed on the following values would result in the given answers.

• INT(90.993) = 90

• INT(-89.876) = -90

Back to heading

KCML - ROUND() Function

The round function will modify a value by rounding it up to a specified number of decimal places, or to a specified type of whole number. The round function requires two parameters, the field or number to be rounded, and the rounding factor.

Thus ROUND(1274.567 , 2 ) would round 1274.567 to two decimal places and give 1274.57.

Rounding a number with a rounding factor of zero would result in a whole number. Thus ROUND(1274.567 , 0 ) would give 1275.

Rounding a number with a negative rounding factor would result in a whole number. The whole number would be rounded to the nearest ten, hundred, thousand, hundred thousand and so on. The following results would be given.

• ROUND(1274.567 , -1 ) gives 1270.00

• ROUND(1274.567 , -2 ) gives 1300.00

• ROUND(1274.567 , -3 ) gives 1000.00

• ROUND(1274.567 , -4 ) gives 0.00

Whether the ROUND() function rounds to decimal places, or to whole numbers, it will round up at .5 and above, otherwise it will round down.

Back to heading

KCML - LEN() Function

The LEN() numeric function is used to determine the length, in characters, of an alphanumeric argument. The result is returned as a numeric value. The LEN() function is valid wherever a numeric function is legal. Trailing spaces are not considered to be part of an alpha variable. If a variable only contains spaces, then the value returned is 1.

If we needed to know how many characters there were in a particular field then the LEN() function could be used as follows.

Back to heading

KCML - MAX() Function

The MAX() function enables you to choose the largest of a series of fields. Commonly this are a choice of two fields, but it may be more. If, for example, you had three prices - a retail price, an average selling price and a recommended selling price - you may want the report to show the largest of the three.

The MAX() function will examine each of the values it is given and return the largest. Each value within the brackets must be separated by a comma.

It is often used to remove negative values from a report.

In this case it would return the following values given these original values for the field !0_BALANCE:

•  34.77 = 34.77

• -30.00 = 0

•  15.33 = 15.33

•   0 = 0

Back to heading

KCML - MIN() Function

The MIN() function enables you to choose the smallest of a series of fields, it is the opposite of the MAX() function. Commonly this are a choice of two fields, but it may be more. If, for example, you had three prices - a retail price, an average selling price and a recommended selling price - you may want the report to show the smallest of the three.

The MIN() function will examine each of the values it is given and return the smallest. Each value within the brackets must be separated by a comma.

If we reverse the example used above for the MAX() function in removing negative values the results would be as follows.

In this case it would return the following values given these original values for the field !0_BALANCE:

•  34.77 = 0

• -30.00 = -30.00

•  15.33 = 0

•   0 = 0

Back to heading

KCML - MOD() Function

The MOD() function will carry out a MODULUS calculation on a given value. The modulus is a remainder, if 16 were divided by 7 the modulus would be 2. We give the MOD() routine two parameters, a number and a divisor. While a Modulus function is of limited use it is a very handy tool to have, one popular use is to find out the day of the week.

Back to heading

KCML - STR() Function

The STR() function enables you to examine, extract or work with part of an alphanumeric string. The syntax is shown in the example below, it asks for three parameters - the field name, the start position and the length. Each parameter is separated by a comma. If the start position is omitted position one is assumed. If the length is omitted then the remainder of the field is used.

The first example above sets the derived field AC1$ to the first character of an account code.

The second example puts all but the first character of the suffix code into the derived field SNO$.

The third example copies the first four characters of the postcode into PC$.

Back to heading

KCML - DO-END DO Function

A DO group is a statement, or series of statements, which are executed only if the program enters the group. Each DO must be paired with an END DO to resolve the group.

The requirement for the DO, END DO function is difficult to explain in the context of straightforward examples. The following is intended as a guide only, you will need to develop the concept as required.

In many cases it would be tempting to utilise a DO, END DO function where a couple of ternaries would be sufficient. Consider the following situation.

Example One

In the first of two examples for the DO, END DO option we can see where it should not be best used.

Problem

You have to set three derived field values, but only if a certain test proves to be true. There are two ways to do this as shown below.

Solution one - Ternaries

One test is carried out three times, we are checking the classification of a record to see if it is a company record or a private record. If it is a company record then the three fields required are set to Commercial, Sirs and Fax. Where the record is found to be other than Commercial, the three fields should be set to Other, a person's salutation and Phone.

Solution two - DO, END DO

In this case the test is carried out once. If it is found to be true then the values of three fields are changed.

Which is best?

Both of the solutions shown above achieve the same end. The second example however involves unnecessary complication. The ternary solution is more efficient and requires less thought. In this case the first option should be used.

Example Two

This second example shows that if the tests were a little more complex a DO,END DO solution may be more efficient. In this case the string of tests would be too long for a ternary function.

There are three tests involved. As they cannot fit onto one line we enter a DO group after the first two tests and then carry out the third test. If this third test is passed, another DO group level is entered and the field values are reset. In this case there are two END DO statements in order to close both groups.

In this case, only a DO group would solve the problem because of the length of the tests. Users may also find a study of the WHILE DO group useful. This is included in the 'RG_BUILD, 'RG_FSTART and 'RG_READ_NEXT examples shown toward the end of this topic.

Back to heading

Useful Report Generator Library Routines

Note: A full list of KCML functions is contained in the KCML Language Reference Manual.

• 'RG_BUILD(handle, path, recsym)

• 'RG_FSTART(handle, rec_key$, path)

• 'RG_READ_NEXT(handle, path, rec_sym)

• 'RG_READ(handle, rec_key$, path, rec_sym)

'RG_EXPAND_FAXNO$(name$, number$)

If a mail output report is being written which requires that the output is sent to a customer by fax, this function can be used. Obviously this assumes that the system is fitted with a fax box. The routine will return the correctly formatted fax address in the format "name@faxnumber.fax" which is recognised as a valid fax number by the administrator.

Syntax - 'RG_EXPAND_FAXNO$(A$,B$)

• A$ = An alpha field containing the name of the recipient.

• B$ = An alpha field containing the fax number.

The fax number address is returned to the derived field which called the function and should be checked for validity by making sure it is not blank. If the fax number or customer's name is missing, FAX$ is set to empty spaces.

The fax number address is returned to the derived field which called the function. You must make sure it is checked for validity by making sure it is not blank. If the fax number or customer's name is missing, that portion of the field is set to empty spaces.

The second field assigns the fax number address to the variable email_to$, if the function return variable is blank then a default user is assigned.

'RG_DATE(j_date, date_sym, format)

The 'rg_date() function is used to turn a day number into a valid alpha date format. Commonly an 8 or 10-character format is used. This function is useful where a report has altered or reset a date field and it has to be displayed on the report in its new form.

Syntax - 'RG_DATE(a,sym(B$),C)

A = A numeric field containing a day number.

B$ = An alpha field which will receive the formatted date field.

C = The length of the date display in the receiver alpha variable. 8 = DD/MM/YY. 10 = DD/MM/CCYY. 0 = Text version of the date which will fit into 20 characters.

RETURN VARIABLE = whichever field is named within the sym() part of the function.

This example will add 30 days onto today's day number to create a new day number 30 days hence. The derived field DATE$ is named and then initialised by setting it equal to an empty space. A colon (:) is then used to allow another KCML statement to be added to the line, in this case the 'RG_DATE routine. The derived field REPLY contains the day number and the derived field DATE$ is named as the receiver variable. The length of the receiver variable is set to be ten characters.

If the length of the receiver variable is set to be zero characters then the date is returned as text (that is "3rd July 2010") although the year will not be included if the date is in the same year and within 120 days of today's date.

'rg_julian("TODAY",10)

This function is used to turn an alpha version of the date into a day number.

Syntax - 'rg_julian( "TODAY",10 )

"TODAY" or field = The text field which contains the date to be converted.

B = The length of the alpha date field, (8=DD/MM/YY and 10 = DD/MM/CCYY)

RETURN VARIABLE = gb_day

In this instance we will assume that for selection reasons the date we need to work on should be set to the first day of the month concerned. In order to accomplish this we will turn the target date into a string, set the first two characters to "01" and then turn it back into a day number.

The final derived field, DAY, now contains the day number which corresponds to the first day of the month relative to the original date field, !1_DATE.

'RG_UPPER$(text_sym)

The rg_upper routine will turn text into upper-case.

Syntax - A$ = 'RG_UPPER$( B$)

A$ is the text field which will receive the converted text.

B$ is the field which contains the original text.

The first example uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. We say that TITLE$ is equal to the result of the 'rg_upper$() routine, which is the content of the field TITLENAM converted to all upper-case characters.

The second example also uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. In this case the field !0_TITLE$ is first initialised by assigning an empty space to the field, then the result of the 'rg_upper$() routine is assigned to the variable.

'RG_LOWER$(text_sym)

The rg_lower$() routine will turn text into lower-case.

Syntax - A$ = 'RG_LOWER$( B$)

A$ is the text field which will receive the converted text.

B$ is the field which contains the original text.

The first example uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. We say that TITLE$ is equal to the result of the 'rg_lower$() routine, which is the content of the field TITLENAM converted to all lower-case characters.

The second example also uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. In this case the field !0_TITLE$ is first initialised by assigning an empty space to the field, then the result of the 'rg_lower$() routine is assigned to the variable.

'RG_LCAP$(text_sym)

The rg_lcap$() routine will turn text into lower-case but leave the first letter of each word as a leading capital letter.

Syntax - A$ = 'RG_LCAP$( B$)

A$ is the text field which will receive the converted text.

B$ is the field which contains the original text.

The first example uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. We say that TITLE$ is equal to the result of the 'rg_lcap$() routine, which is the content of the field TITLENAM converted to all lower-case characters with leading capital letters.

The second example also uses the derived field TITLE$ to receive the amended contents of the field called TITLENAM from the prime table. In this case the field !0_TITLE$ is first initialised by assigning an empty space to the field, then the result of the 'rg_lcap$() routine is assigned to the variable.

'RG_CONV_A2N(TEXT$)

This will convert the content of an alpha field, or part of an alpha field, into a numeric field.

Syntax - A = 'RG_CONV_A2N(B$)

A = the field name which will receive the converted number.

B$ = the alpha field which contains the number.

If the text cannot be correctly converted into a number the routine will return a value of zero.

The first example above converts the whole of the account number from an alpha field into a numeric derived field called ACNUM. If there are any non-numeric characters in the account number field then the derived field ACNUM is set to a value of zero.

The second example sets the derived field called STNUM to the numeric value of the six characters in the field called SUFFIX, starting from character position two. This second example makes use of the STR() function which is explained separately in this topic.

'RG_CONV_N2A$(NUMBER,IMAGE)

This will convert the content of a numeric field into an alpha field.

Syntax - A$ = 'RG_CONV_N2A$(B, IMAGE)

A$ = the field name which will receive the converted number.

B = the numeric field to be converted.

If the number cannot be correctly converted into a piece of text within the image specified then the routine will return an empty space.

The example above converts the BASETOTL field into an alpha derived field called VAL$. If the conversion cannot successfully convert the number into the alpha field using the image given then the routine will set the field to empty spaces. A maximum field image size of 20 characters is allowed. Notice that the image includes a minus sign, a number of whole numbers, a decimal point and a number of hash symbols, to indicate the number of decimal places to use. The image is surrounded by quote marks.

All of the numbers converted will fill the image supplied. If the image is too large then the number is shown with leading zeros. If leading zeros are not required then see the use of 'rg_justify$() below.

'RG_JUSTIFY$(NUMBER,IMAGE,DECIMAL PLACES)

This will convert the content of a numeric field into an alpha field but suppress the leading zero's in the unused part of the alpha image. It will optionally suppress the trailing zero's on decimal places as well.

Syntax - A$ = 'RG_JUSTIFY$(B, IMAGE,C)

A$ = the field name which will receive the converted number.

B = the numeric field to be converted.

IMAGE = The type of image to use in the conversion.

C = The number of decimal places.

If the number cannot be correctly converted into a piece of text within the image specified then the routine will return an empty space.

The first example above converts the BASETOTL field into an alpha derived field called VAL$. If the conversion cannot successfully convert the number into the alpha field using the image given, then the routine will set the field to empty spaces. A maximum field image size of 20 characters is allowed. In this first case the routine will always remove trailing zero's from the first two decimal places, this is because of the second parameter, which is a two.

The second example will convert the number but allow trailing zeros to be included because the second parameter is a zero.

The second parameter in the routine will have the following effect:

'RG_JUSTIFY$(!1_BASETOTL,"-######.##",X)

Where the original number is 16341.00

If X = 0 the printed result is - 16341.00

If X = 1 the printed result is - 16341.0

If X = 2 or more the printed result is - 16341.

'RG_SOM(MONTH,ORIGINAL DATE)

This will return a day number signifying the first day of the month specified. The day number returned can be used in other derived fields or as part of the selection process.

Syntax - SMONTH = 'RG_SOM(MONTH,DATE)

MONTH = A number relative to this month. 3 would indicate a month three months hence, -2 would mean a month number two months ago. A zero would indicate the current month.

DATE = A date field, either from an existing record or perhaps the current system date.

Let us assume that we want to select items for a report which all have an expiry date that falls within the month three months ahead of today's date. This are a regular report and is run as part of the end of day report batch automatically. Prompts for the dates will therefore not be convenient. We need the Report Generator to select the dates for us with no user intervention. In order to select records properly, we need to make sure that the date on the record is greater than, or equal to, the first day of the month in question, and less than the first day of the following month.

The example above simply retrieves the day number from the first day of the month which is three months ahead - based on today's date. It then gets the day number of the first day of the following month. The selection rules are then easy to set.

If the first and last day of the month have to be printed on the report we simply have to take one day from the second field and this will give us the day number at the end of the preceding month. These can then be converted to proper dates for printing purposes using the 'rg_date() function.

'RG_WEEKDAY$(DAYNO)

This will return an alpha field which will indicate the day of the week relative to the day number specified.

Syntax - DOW$='RG_WEEKDAY$(DAYNO)

DAYNO = The day number for which the day of the week is required.

In the example above the derived field DOW$ will receive the text value of the day of the week relative to the field NEXTSERV. This could enhance the appearance of a letter being sent to a customer asking that a vehicle is brought in for service.

'RG_RIGHT$

This will return a string right-justified within a given field width.

SYNTAX rg_right$(field$80, size)

Field$ =The string variable to be right justified. This field has a maximum length of 80 characters.

Size =The number of bytes over which the field is required to be right-justified.

The converted number is returned both in the variable RESULT$ and as a return to the rg_right$ subroutine.

That is derived field field$ = rg_right$(!0_field$, 40)

This will right-justify the contents of the string variable !0_field$ over forty bytes.

'RG_CENTRE$

Returns a string centred within a given field width.

SYNTAX rg_centres$(field$80, size)

Field$ =The string variable to be centred. This field has a maximum length of 80 characters.

Size =The size of the field over which to centre the given field.

The centralized text is returned both in the variable RESULT$ and as a return to the rg_centre$ subroutine.

That is derived field field$ = rg_centre$9!0_field$, 40)

This will centre the contents of the string variable !0_field$ over forty bytes.

'RG_STRING$

Returns the given number as a string using the given image, left-justified no leading zeros.

SYNTAXrg_string$(field, image$)

Field$ =The numeric variable to be converted to a left-justified string.

Image$ =The image of the required string variable.

The converted number is returned both in the variable RESULTS$ and as a return to the rg_string$ subroutine.

That is derived field num$ = rg_string$(!0_value, -#####.##)

This will convert the numeric variable !0_value into the string variable !0_num$, using an image of -#####.##.

'RG_BLANK$

Returns the given number as a string using the given image, right-justified with no leading zeros.

SYNTAX rg_blank$(number, image$, decimal places)

This performs the same function as RG_JUSTIFY$ but returns a blank string if the number supplied is zero.

'RG_MARGIN$

Returns the gross margin percentage calculated from the given sales and cost values. If the sales value is zero then zero is returned.

SYNTAX rg_margin(sales, cost)

Sales =Sale value

Cost =Cost value

That is derived field margin = rg_margin(100,50)

This will return the result of the calculation in the form (sales - cost) /sales * 100.

'RG_DISCOUNT$

Returns the discount which would in the given net value from the given gross value, to the specified number of decimal places.

SYNTAX rg_discount(gross, net, dp)

Gross =Gross value

Cost =Cost value

DP =Decimal places

That is derived field discount = rg_discount(100, 75.2)

The function attempts to provide a tidy discount - so rg_discount(0.82, 0.74, 2) will return 10 as opposed to 9.76.

'RG_SPLIT$

Returns the first or second portion of the given string, split at the nearest space preceding the split point.

SYNTAXrg_split$(split$, split, element)

Splt$ =The string variable which is to be split

Split =The number of bytes at which to split the string.

Element =A flag which determines which portion of the split string is to be returned. 1 will return the first portion, 2 will return the second portion.

The split portion is returned only as a return to the rg_split$ subroutine.

That is derived field text$ = rg_justify$(!0_text$, 20, 1)

This will return the left 20 characters of the field !0_text$ back into itself.

'RG_ENV$

Returns the assigned value of an environment variable.

SYNTAX rg_env$(parameter$)

Parameter$ =The environment variable whose contents are required to be returned.

The result of the enquiry is returned in both the variable VALUES$ and as a return to the rg_parms$ subroutine.

That is derived field user$ = rg_env$($LOGNAME)

This will return the ID of a the operator running the report into the variable !0_user$.

'RG_PARMS$

Returns the value of a parameter from the parameter section of the menu definition for the report.

SYNTAX rg_parms$(parameter$)

Parameter$ =The menu parameter whose contents are required to be returned.

The result of the enquiry is returned in both the variable VALUE$ and as a return to the rg_parms$ subroutine.

That is derived field parm$ = rg_parms$($OUTPUT)

This will return the output type of a menu driven report into the variable !0_parm$

'RG_BRANCH

Returns the current branch number of the table being scanned in multi-branch reports.

SYNTAX rg_branch()

NO PARAMETERS

That is derived field branch = rg_branch()

'RG_REPEAT

Returns blank if the given string matches the previous call to this routine with the same identifier.

SYNTAX rg_repeat$(field$, register)

Field$ =The field which is to be checked for duplication.

Register =The register element holding the text with which the field$ text is to be compared.

The result of the comparison is returned both in the variable RESULT$ and as a return to the rg_repeat$ subroutine.

That is derived field text$ = rg_repeat$(!0_text$, 17)

If the content of the 17th register element is the same as the content of our string variable !0_text$ then !0_text$ is initialised to spaces. This routine is of use to compare the contents of fields on a report for which only the display of the first occurrence is required.

For example where a report is scanning the Accounts Receivable transaction table and the account code is only required to be displayed when the account code changes.

Looping Around Multiple Records in a Secondary Table

There is times when you will need to examine records found in a secondary table which cannot be directly linked, even with derived field links. In this sort of case, looping around a number of records until the correct one is found could provide a solution. Alternatively users may want to run through a number of records in the secondary table to accumulate values found within the records.

This section will examine some examples of such multiple record access within secondary tables using three Report Generator library functions. The three functions involved are 'RG_BUILD, 'RG_FSTART and 'RG_READ_NEXT. Before we look at the functions in any detail it is important that the reader is able to recognise some the phrases that is utilised.

Handle - this is the handle number (from one to nine), which represents the table number. The prime table would be represented by handle number one. The second table on the Tables tab would have handle number two, and so on. They are referred to in the following format - rg_handle(x) where x is the handle number. Therefore table number 2 is referenced as rg_handle(2).

Path - this is the key-path that is used to access the table. We have already discussed the key-paths earlier in this on-line help. The path is simply referred to as a number.

Rec_sym - this is the record that is loaded by the Report Generator. In the same way as the handle number it represents the table number you are looking for, indeed it will match the handle number. The record symbol buffer is referred to in the following manner - sym(rg_recordx$()) where x is the table number. Therefore we would load a record from table number 2 as sym(rg_record2$()).

Key$ - this is the index key for the record required. It is generated by the rg_build() function and used to access the record.

Ki_status - this is an error code returned after each routine. An error code of zero represents success. Other error codes are explained as required.

DON'T PANIC!

If the notes above look a little strange do not panic. Controlled record access is not difficult, KCML routines will do all the work for you. All you have to do is utilise the routines that have been developed for you, take a little care in making sure they are phrased correctly, and it will work.

There is only one thing to beware of. The operations explained here are disk intensive, they will put extra pressure on the system and make the report run a little slower than normal. Use them wisely and if you have written a particularly complicated report, do all of your testing on the training company provided so that only small tables are involved. Do not let yourself loose on large tables until you are sure that the report works as efficiently as possible.

Each of the functions outlined above will now be explained in a little more detail before their use is illustrated in a couple of examples.

'RG_BUILD(handle, path, rec_sym)

The 'RG_BUILD( ) function enables you to build an index key for a record which is specified in the named symbol buffer. Once the routine is executed it will return a variable called rec_key$ which holds the index key required for the specified key-path. This index key is used by rg_fstart() or rg_read().

Parameter Description

handle table handle number

path key path to utilise

rec_sym symbol index for record buffer

The routine will return two values: rec_key$ will hold the key-path value required while ki_status will hold an error value. If ki_status has a value of zero the build was successful. The only other value in this case would be seven which indicates that the table in question is not open, probably because it is not named in the Tables tab - so check the table number you are using in rg_handle and sym(rg_recordx$()) commands.

'RG_FSTART(handle, rec_key$, path)

This routine will make use of the rec_key$ variable returned from the rg_build() routine. It will position the table pointer next to the record you wants to read.

In normal use it will position the pointer just in front of the record specified (which need not itself be in the index). Thus a subsequent read next will read the first record with a key greater than or equal to the supplied key. If however the specified path is a negative number, the pointer is positioned at a point after the required key on the specified key-path. In this case you will specify that the subsequent read will go backwards through the path so that the last record with a key less than or equal to the supplied key is read with a subsequent read next.

Parameter Description

handle table handle number

key$ key$ value from rg_build()

path key path to utilise

The routine will normally work without problems, therefore ki_status will always be zero. The only other values probable are three, this represents an invalid key-path, check and amend it, or seven if the table is not open. In the latter case check the handle number being used.

'RG_READ_NEXT(handle, path, rec_sym)

This will read the next sequential record in the path specified.

Parameter Description

handle table handle number

path key path to utilise

rec_sym symbol index for record buffer

This routine can only be used after the rg_fstart() function has positioned the table pointer to a specified starting place in the index. If the path is positive the next record in the table is read. If the path is negative the previous record in the table is read.

Once the record has been read successfully, the routine will also modify the contents of rec_key$ to the record read and reposition the table pointer accordingly so that the next record can be read.

Possible ki_status errors are as follows.

• 0 - Success

• 2 - Already at end of index

• 3 - Path not started, (check rg_fstart() command)

• 7 - Table not open, (check handle)

'RG_READ(handle, rec_key$, path, rec_sym)

This routine, although not used much in the Report Generator, will read a record directly into the report. It follows a rg_build() command and assumes that you are confident that the key you have exists and is accurate for the record required. This routine will not modify the table pointer so the next record may not be subsequently read.

Parameter Description

handle table handle number

rec_key$ key value from rg_build()

path key path to utilise

rec_sym symbol index for record buffer

Possible errors from this routine are as follows.

• 0 - Success

• 1 - record not found

• 3 - invalid key-path, (check rg_fstart() command)

• 7 - Table not open, (check handle)

Record Access Examples

The following examples will show how to use the routines explained above to control the reading of records from the system. The first one enables you to read through the Sales Ledger transaction table, accumulating the balances into various derived fields depending on the age of the item found. The second one enables you to read through both the parts and labour elements of a WIP in the Point-of-Sale system. The final example enables you to check the last contact on a marketing record, establishing what it was and how old it is.

Example One

• Reading sales transactions

With the Sales Ledger accounts table as table number one, and the transaction table as table number two, we are going to develop a type of aged debt report. The age of the debt is defined by comparing the document date with a prompted date. The balance is calculated on each of the items found, it is aged and then placed into the correct derived field.

• Tables

• 1 SL.accts

• 2 SL.trans

The connections area of the tab may be left blank. We are going to link the tables and read the records so connections on this tab are irrelevant. If a user does not have a high enough priority to leave the link blank then enter 0 and choose any key-path from the list provided on the key-path search routine.

Derived

Some of these derived fields do not need names, therefore they are not specified. Only derived fields which need to be named will appear. Some derived fields are numbered - this is so that notes can refer to those lines, do not create derived fields with a number as a field name.

The example can be explained as follows. The first field, which need not be named, sets the accumulating derived fields to zero. This line cannot be put in until the named derived fields have been created further down, so leave it until last. It is necessary to have this line because otherwise we will keep adding values to these fields each time we pass through derived fields.

The second line sets the values which is used for the rg_build() routine. This field has been highlighted with *1 for reference here. The key-path we is using is number two which is made up of three fields, the account code, the document date and the document number. The values of these fields are set so that the build command will assemble the correct key. We have set the correct account number but the document date and document number have both been set to zero. This will result in a key-path value which is the lowest possible for the account code specified.

The next three derived lines call the build, start and read next routines. Notice they all specify table number two and key-path number two.

The field marked *2 checks the error value in ki_status to make sure the read was successful, it also makes sure we have loaded a record for the correct account by comparing the account code with the current account code from table number one. As long as both of these checks are positive we enter a WHILE -WEND loop which will continue to execute as long as we keep successfully reading records for the same account.

The age and balance are then calculated (age is calculated using a prompted age as the reference point). We then make use of these fields in a series of five ternaries which will allocate the balance to the correct field. Note that we are adding the result of the ternary. This is required because otherwise the field value would merely be set to the last value which matched the required age for each record.

The next record is read in using the read_next() routine in field *3, then the WEND command (*4), sends the program back to the WHILE statement which will check to see if this record is for the same account. If it is then the loop is repeated, if not then the program will return to the first derived field following the WEND statement, in this case the field called TOTAL.

Thus, each time a new account is read in table number one, the Report Generator will loop around each of the transaction records in table number two accumulating the values into the correct age field. Once they are all found it will drop out of the loop and add them up to give a total.

Example Two

• Looping around two secondary tables

The Point-of-Sale module utilises three tables quite heavily. They are the WIP header table, the WIP parts table and the WIP labour table. The problem is that for each record found in the WIP header table, there may be a number of entries in the labour table, and a number of records in the parts table. There are rarely the same number of items in the parts table as there are in the labour table for each WIP. What we will do here is calculate the total value of the WIP by looping around both the labour and parts table, using the WIP header table as the prime table.

Tip: Do not take the calculations here too seriously, they are for illustration only. To correctly value any parts or labour, full attention should be paid to discounts, and so on. This example shows only two calculations. One assumes a parts total by multiplying the order quantity by the selling price, the other calculation assumes a labour value to be the allowed time multiplied by the rate. We also include a count of the number of lines found.

• Tables

• 1 SO.headr

• 2 SO.labor

• 3. SO.parts

The connections area of the tab may be left blank. We are going to link the tables and read the records so connections on this tab are irrelevant. If you do not have a high enough priority to leave the connection blank, then set up a derived link to any key-path.

Derived

Once again some of these derived fields do not need names, therefore they are not specified. Only derived fields which need to be named will appear.

The example can be explained as follows. The derived fields within the loop are being set to zero a different way here. Note that the field names LABC, LABV, PARC and PARV are duplicated. The first time they are named they are set to zero. After this is done, the rest of the space on the line is being utilised for the loop statements. Note that a colon (:) separates the two statements on the first four fields.

We loop around the labour table first, incrementing the line count field, LABC, each time we find a labour line on the WIP in question. Then we add the value of the part to the labour value field, LABV. As soon as we load a labour line with a different WIP number we come out of the loop and move to the next derived field, which in this case does the same kind of loop around the parts table.

The labour count and value are accumulated in a similar way, and once this loop is complete we add the total of the parts to the total of the labour.

Example Three

• Examine a secondary table for particular information

In the Marketing system a lot of contacts are generated. Each of these has a follow-up code which should be actioned. However, some may slip through the net. What we will do here is use an imaginary example to analyse the contact table for a particular contact type in a particular position.

The Sales Manager has asked how many people have had a quote for a new vehicle, and how many of these people have never been contacted since. We will run down the contact table (in contact sequence), and see if the last one was for a quote. Furthermore, we need to see how long ago the quote was to see if we should have followed this up yet.

The parameters are simple.

• Is the last contact record for a quote

• Is the contact more than seven days old

The contact records have a key-path which is in contact sequence for each record. We keep the date of the contact on each of the contact records. In this case we only want to look at the last contact, so a means of ignoring the others would be beneficial. The way we will handle this is to go through the contacts backwards, therefore reading the last record first, and ignore any other contacts for the same target record.

Tip: Think carefully and adapt this sort of table access to your particular needs. The records found on this report could be identified very easily using Ad-hoc enquiries and the NODUPS function, however we are assuming we need detailed analysis on the associated records which Ad-hoc cannot provide.

• Tables

• 1 MK.targt

• 2 MK.conta

The connections area of the tab may be left blank. We are going to link the tables and read the records so connections on this tab are irrelevant.

Derived

Once again some of these derived fields do not need names, therefore they are not specified. Only derived fields which need to be named will appear. Some derived fields are numbered. This is so that notes can refer to those lines, do not create derived fields with a number as a field name.

The example can be explained as follows. The derived fields start with the field called INC. This is set to zero and will only be reset if the right sort of contact code is found. The path value in the rg_fstart() has been set to a negative value, this means that when it returns with the index key it will have a value greater than the one required. When we tell the rg_read_next() command to read the previous record (also with a negative path number), it is reading the last record of the contact chain if any contacts exist for this target.

We carry out the status check as usual and then make sure we have a matching magic number. Notice that we do not enter a WHILE - WEND loop (we know we do not want to read multiple records for each target - only the last one), therefore a DO - END DO routine is all that is required. Because the checks we need to make are too long for one line, we split the third check and put it onto the next line, this looks for the contact type.

Now that we have two DO - END DO statements we need two END DO statements at the end of the check. The ternary in the middle sets INC to a value of 1 if the record is older than it should be.

The derived field INC is used in the Select tab to include any records with a value of 1.

Summary

Advanced derived field use is not for Report Generator novices. However, once a thorough knowledge of the software has been gained, there is no reason for you to avoid some of the more complex facilities that Report Generator provides.

The more practice you get, the more confidence you will build up. The most important thing to bear in mind is that a good working knowledge of the data you are working with is a pre-requisite of efficient analysis.

A working introduction to some of these functions can be gained on the Advanced Report Generator course.

Related Topics:

Basic Use of Derived Fields