Use the Derived tab to create Derived fields for use in the report. The derived fields execute in the order in which they are entered. Because you cannot use derived fields until they are created, you will find it easier to create them before you assemble the Format, Select or Sequence tabs. As you become familiar with the Derived tab you will experiment with reports in many different areas.
Tip: The tab has two columns, one for the column name and the other for the calculation. The field name can be up to 8 characters long and the calculation can contain up to 250 characters.
See also:
Advanced use of derived fields
Note: When derived column definitions are first encountered, they may look a little strange. In order for the report definition to compile properly, the column names and some of the commands must be entered using the correct syntax. The advantage of this, however, is that a very open approach can be taken when deriving calculations. Experienced users are able to enter quite advanced calculations and functions, as well as entering commands using KCML code. However, very simple calculations are achieved quite easily.
These are derived fields that are already set up in Report Generator for you to use.
Tip: Several of these standard derived fields are useful when setting up the Standard Print Banner, and S_VER is useful if you are using Version Control in Properties.
There are several basic rules concerning derived field names which should be understood before use.
All derived field names must consist of no more than eight alpha characters in the range A-Z.
If a numeric answer is required then the name is typed as alpha, using up to eight characters.
If an alpha answer will result then the derived field name must end with a dollar sign ($).
Date fields are considered numeric and do not require a dollar sign.
Derived field names may be the same as field names held on the other tables. You may want to avoid doing this, however, as it may cause confusion.
Derived field names are optional. If you do not need a derived field name for your calculation line you may press TAB when the cursor is in the name column and the cursor will move to the calculation part of the form. This is demonstrated in the advanced derived field topic of this on-line help.
Derived fields are usually 32 characters long. Longer columns can be created by inserting _xx$ at the end of the field name, where xx is a number from 1 to 99.
Before setting up an example calculation you should understand how the column names are used.
The Report Generator can process only column names which are input according to a certain convention.
!x_********
There are four parts to the column names.
!: Indicates that a column name follows
x: Signifies a table !id number. If the column used is in the prime table, the x would be replaced by the number 1. If the column name is in the second table, a 2 would be used. If the column name is in the derived field table, the number used is zero.
Table 0 Derived field
Table 1 Prime table
Table 2 Second table
Table 3 Third table
Table 4 Fourth table, and so on
Note: All prompt fields are considered to be derived columns (fields) and have an !id of zero.
_: The third part of the column name appears in two different ways. It is shown either as an underline character (_) or as a small left arrow. It is used to separate the table !id from the name of the column. On most keyboards it is generated by pressing the underline key.
********: Column name. This form of column naming is a short way of telling the Report Generator where to find the column. For example, !1_ADDRESS.... is the equivalent of: here is a column name, in table !id number 1 use the column called ADDRESS.
Note: AUTOLINE support staff and established customers have a verbal shortcut to describe this type of field name. Instead of saying exclamation mark 1 back arrow address, it is known as plink 1 blip address.
To create a derived column you must first enter the name. Follow the rules for naming the column.
Double-click the derived line in order to invoke the EDIT DERIVED FIELD form.
Tip: Alternatively, click either the Amend or Insert button.
Derived field: Name the derived field and press TAB to move into the definition field.
Definition: Type the formula in valid KCML code
Tip: Click the Insert field button for help. You can either select a column from one of the table shown, or display the Pre-defined functions form in order to use the function wizard to help you construct a formula.
In this example we will establish the amount outstanding on an invoice from the original value (BASEVAL, which is in table !1) less amount paid (BASEPAID, which is in table !1).
Double-click the line where the derived field is to be created or click Insert.
The EDIT DERIVED FIELD form is displayed.
Enter the field name (OUTSTAND), which must not end in a $ sign because the derived field is numeric.
Press TAB, or click in the Definition field of the tab and click Insert field.
A dialog box is displayed showing the currently selected tables, including the derived field table.
Select the table required (the transaction file in this case) and click OK.
A second form is displayed, prompting you for a field name. You can use the scroll facility to move up and down the list. Select the field name BASEVAL in the list.
Click OK.
If this were an occurs field, a third form would be displayed, prompting for the Occurrence.
The field name selected appears on the derived field calculation line. Position the cursor along the line beyond the BASEVAL field and type in a minus sign (-).
Click Insert field again and call the field BASEPAID in the same way as calling BASEVAL.
Your entry now appears as:
!1_BASEVAL - !1_BASEPAID : REM Amt outstanding
The example shown here also shows a REM statement. These may be added as required if there is space on the line. They enable you to add comments to explain what the derived field is doing.
If you add REM statements you must remember to precede them with a colon sign (:).
The example shown above uses the mathematical function of subtraction.
Functions which can be used include:
+ Addition
- Subtraction
* Multiplication
/ Division
^ Raise to the power of ...
Brackets ( ) can also be used to control the order of execution of the calculation. This is because the system always evaluates a formula in a certain order. The order used is always:
Brackets
Of (to the power of)
Division
Multiplication
Addition
Subtraction
Example
(!1_COLUMNA - !1_COLUMNB)/!1_COLUMNC
In this case the system subtracts COLUMNB from COLUMNA before dividing the answer by COLUMNC, thus altering the sequence of calculation which would apply without the brackets.
An additional prompt has been added for the input of numeric fields on a report format. An Average button is displayed, and when clicked changes the way the total is printed. It causes a mean average of the number to be printed by dividing the accumulated total by the number of items in the report section being totalled. The table below illustrates the results.
| Value (Accumulated) | Value (Averaged) |
| 10 | 10 |
| 20 | 20 |
| 30 | 30 |
| 60 | 20 |
It is possible to insert a field between two existing derived fields, delete a specific derived line or erase all derived lines after a specific point. This can be achieved by clicking the Insert, Delete and Erase buttons.
To Insert a field, select the line where the new derived line is required. Click Insert and then click OK. The line can then be entered as required.
To delete a specific derived field, click the line to be removed and then click Delete.
The Erase button removes all derived lines from a specific point. Select the first line to be erased by clicking the line, and then click Erase. This line and all the lines after the selected point are erased.
Warning: Use the Erase button with caution.
One of the most powerful derived field tools is the Ternary function.
It is a sort of IF...THEN...ELSE... statement.
In a Ternary a condition is checked, and one of two actions is acted upon depending on whether the condition proves to be TRUE or FALSE.
All Ternary formats must adhere to the following rules:
They are fully enclosed in brackets.
They start with a condition check.
A question mark follows the condition check.
A TRUE statement follows the question mark.
A colon (:) follows the TRUE statement.
A FALSE statement follows the colon.
( !1_BASEVAL > 100 ? "Over 100" : "Under 100" ) a bbbbbbbbbbbbbb c dddddddd e ffffffffffffffff a
Examples:
Name =..... TEST1 (!1_BASEVAL >100 ? 1 : 0) TEST2$ (!1_BASEVAL >100 ? "OVER 100" : "UNDER 100") TEST3 (!0_PROFIT > 500 ? .02 : 0) COMM !1_SALE * !0_TEST3 FLAG$ (!J(TODAY)-!1_DUEDATE >0? "Overdue": " ") ODUE (!J(TODAY)-!1_DUEDATE >0?!1_BASEVAL*.05: 0) DUE !1_BASEVAL + !0_ODUE CONT$ (!1_CONTACT = " "? "Sir/Madam" : !1_CONTACT) DAY (MOD(!0_J_TODAY,7)=0 ? 3 : 1 )
The ternaries shown above can be explained as follows.
TEST1: This checks the value of the field BASEVAL. If it is over 100 the field TEST1 is set to a value of 1. If not, it records a value of 0.
TEST2$: This does the same check as TEST1 but returns an alpha result in the form "OVER 100" or "UNDER 100" depending on the value.
Note: The name of the derived field ends with a dollar sign because the derived calculation returns an alpha value.
TEST3: This Ternary is meant to work in conjunction with the COMM field. If the PROFIT made is over 500 then TEST3 is set to .02, otherwise it is set to 0.
COMM: The COMM field works out a commission based on the result. If enough profit is made, then a 2% value is given, if not then a value of 0 is applied.
FLAG$: This sets a text description of "Overdue" to the field FLAG$ if the DUEDATE has passed. This is how you can vary wording within a letter, or apply descriptive text to a report.
ODUE: Both ODUE and DUE work together in order to increase the value of a due amount where the payment date has passed. In this case it is being increased by 5%.
DUE: Both ODUE and DUE work together in order to increase the value of a due amount where the payment date has passed. In this case it is being increased by 5%.
CONT$: This checks a contact name field to see if it is empty, If it is then it substitutes the text Sir/Madam. The field CONT$ can then be put into a letter instead of CONTACT. An empty space does not appear where a customer contact name is expected.
DAY: This checks the day of the week for batched reports which require only the previous working day's records to be included. The MOD() function is described in the advanced derived fields topic. However, all the field is doing is seeing whether the system day number divides evenly by 7. If so it must be a Monday and therefore the records of the previous three days should be checked to cover Friday, Saturday and Sunday. The selection rule will probably be as follows:
!1_DOCDATE >= !0_J_TODAY-!0_DAY
This subtracts one from today's day number during the week, but three on a Monday.
You can see from these examples that ternaries can be used in a variety of ways. The most important thing to remember is the set of rules, outlined earlier.
There are several pre-defined functions that can be used to construct derived fields. These are very useful for a variety of different things, and they are included in these notes purely as a reference. Some of them require advanced training for you to use them effectively.
The following derived field features are now included in the Report Generator. They have been grouped together to aid use.
Note: Which derived field features are available depends on which report you have open.
This function is used to turn an alpha version of the date into a day number.
"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
Other significant dates can be utilised within this routine. In the syntax example above you can see "TODAY". You may also use the following:
TODAY = returns a day number based on the system date.
NLSOP = returns a day number based on the start of the current Nominal period date.
NLEOP = returns a day number based on the end of the current Nominal period date.
SOM = returns a day number based on the start of the current calendar month.
EOM = returns a day number based on the end of the current calendar month.
This function 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.
Month offset = 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.
Starting date = A date field, either from an existing record or perhaps the current system date.
| Name | =...... |
| SOM3 | 'RG_SOM(3,!0_J_TODAY) |
| SOM4 | 'RG_SOM(4,!0_J_TODAY) |
| EOM3 | !0_SOM4 - 1 |
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.
Several functions are included to enable you to perform some basic string searches.
This enables you to obtain a TRUE or FALSE value if a field contains a certain piece of text. Type in a field name, or a piece of text, that you want to check. Then enter the text you need to find. The system turns both text values into upper case and then makes its comparison. The search is therefore not case sensitive. Once the check has been made a value of TRUE or FALSE is returned.
| Name | =...... |
| INCLUDE | 'rg_contains (!1_NAME , !0_find$ ) |
In the example above we assume that a report has been established that contains a prompt field (FIND$), that asks you for the search text. This is compared to a field called NAME in file number 1. If the search text is found somewhere within the field being checked a value of TRUE is returned. The derived field INCLUDE receives the value of TRUE or FALSE.
Tip: If the function is being used for selection purposes, you could type the routine directly into the selection form.
| 'rg_contains (!1_NAME , !0_find$ ) |
This enables you to obtain a TRUE or FALSE value if a field begins with a certain piece of text. Type in a field name, or piece of text, that you want to check. Then enter the text you need to find. The system turns both text values into upper case and then make its comparison. Once the check has been made a value of TRUE or FALSE is returned.
| Name | =...... |
| INCLUDE | 'rg_begins (!1_NAME , !0_find$ ) |
In the example above we will assume that a report has been established that contains a prompt field (FIND$), that asks you for the search text. This is compared to the start of a field called NAME in file number 1. The derived field INCLUDE receives the value of TRUE or FALSE.
Tip: If the function is being used for selection purposes, you could type the routine directly into the selection form.
| 'rg_begins (!1_NAME , !0_find$ ) |
This function right-justifies a value within an alpha field.
| Name | =...... |
| NAME$ | 'rg_right$(!1_NAME , !0_name$ ) |
The derived field name NAME$, whether used on a report format or within an RTF output, shows as right-justified. This is useful for placing text as close to a field value as possible, or perhaps for a name and address block at the top right hand corner of a letter.
This function centres a value within an alpha field.
| Name | =...... |
| DESC$ | 'rg_centre$(!1_DESC , !0_name$ ) |
The derived field DESC$, whether used on a report format or within an RTF output, shows the text as a centred value. The width of the text area is controlled by the SIZE parameter passed to the routine.
This function left-justifies a numeric value by transferring it to an alpha field and removing any leading zeroes.
| Name | =...... |
| NUM$ | 'rg_string$(!1_BASETOTL , "-#########.##" ) |
In the example above the derived field NUM$ receives the value from the field BASETOTL. The routine converts the numeric field to alpha, and then puts it into the NUM$ field using the image as a guide. All leading zeroes are removed from the value converted.
This routine is useful for left-justifying a numeric value on an RTF output, and then putting a currency sign in front of the value. An output such as £56,789.99 is preferable to £ 56,789.99.
This function returns an alpha field, containing a source number, which has been right-justified and stripped of a pre-determined number of trailing zeroes.
| Name | =...... |
| NUM$ | 'rg_justify$(!1_BASETOTL , "-#########.##" , 2 ) |
This function returns an alpha field, containing a source number, which has been right-justified and stripped of a pre-determined number of decimal places. If the number of decimal places is left as zero then the number will not be printed at all if it has a value of zero.
| Name | =...... |
| NUM$ | 'rg_blank$(!1_BASETOTL , "-#########.##" , 0 ) |
Returns supplied text as double-width and pads any remaining space in the width of area with the pad character. For example 'rg_double$("HELLO","20","*") will return ****** H E L L O *****.
There are three routines that enable you to manipulate the case of field value, 'rg_upper$(), 'rg_lower$() and rg_lcap$(). This enables the conversion of a text field to all lower-case characters, all upper-case characters or to leading capitals. They all work in a similar way. The routine is called, and the field containing the text to be converted is used as the parameter.
| Name | =...... |
| NAME$ | 'rg_lower$(!1_NAME ) |
In this example an original string value of EXAMPLE is changed to example.
| Name | =...... |
| NAME$ | 'rg_lcap$(!1_NAME ) |
In this example an original string value of EXAMPLE is changed to Example.
| Name | =...... |
| NAME$ | 'rg_upper$(!1_NAME ) |
In this example an original string value of example is changed to EXAMPLE.
This converts the content of an alpha field, or part of an alpha field, into a numeric field.
TEXT$ = the alpha field which contains the number. If the text cannot be correctly converted into a number the routine returns a value of zero.
| Name | =...... |
|
ACNUM STNUM |
'RG_CONV_A2N(!1_ACCOUNT) 'RG_CONV_A2N(STR(!1_SUFFIX,2,6)) |
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.
This converts the content of a numeric field into an alpha field.
NUMBER = the field name of the number to be converted.
IMAGE = the size and type of image to receive the converted value. If the number cannot be correctly converted into a piece of text within the image then the routine returns an empty space.
| Name | =...... |
| VAL$ | 'RG_CONV_N2A$(!1_BASETOTL,"-######.##") |
The example above converts the BASETOTL field into an alpha derived field called VAL$. If the conversion cannot successfully convert the number into an alpha field using the image given, the routine sets the field to empty spaces. A maximum field image size of 20 characters is allowed.
Note: 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 quotation marks. All of the numbers converted will fill the image supplied. If the image is too large the number is shown with leading zeroes. If leading zeroes are not required then see the use of 'rg_justify$().
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.
Julian day number = A numeric field containing a day number, ("TODAY") would return today's date in a valid date format.
Size of date = 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 fits into 20 characters
| Name | =...... |
|
REPLY DATE$ |
!0_J_TODAY + 30 'RG_date$(!0)REPLY,10) |
This example adds 30 days onto today's day number to create a new day number 30 days hence. The derived field DATE$ is named and calls 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 zero characters then the date is returned as text (for example, "3rd July 1999") although the year is not included if the date is the same year, and within 120 days of today's date.
This function is used to turn an alpha version of the date into a day number.
Date$ = The text field which contains the date to be converted.
Format = The length of the alpha date field:
8 = DD/MM/YY
10 = DD/MM/CC/YY
In this instance we will assume that for selection reasons the date we need to work on should be set to the third 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 "03" and then turn it back into a day number.
| Name | =...... |
|
DATE$
DAY |
'RG_DATE(!1_DATE,8) STR(!0_DATE$,1,2) = "03" 'RG_JULIAN(!0_DATE$,8) |
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.
This returns an alpha field which indicates the day of the week relative to the day number specified.
DAYNO = The day number for which the day of the week is required.
| Name | =...... |
| DOW$ | 'RG_WEEKDAY$(!1_NEXTSERV) |
In the example above the derived field DOW$ receives the text value of the day of the week relative to the field NEXTSERV. In this way a letter could be sent to a customer asking that a vehicle is brought in for service.
This calculation is useful for returning a percentage value indicating the margin between two numbers. In this case the first is the sales value, and the second is the cost value.
| Name | =...... |
| Margin | 'rg_margin(!1_sellprice , !1_costprice ) |
Remember that if you want to include a total for the margin column on the report you must tick the recalculate box. Otherwise you will get an incorrect accumulated total.
This function presents the discount that will have been used to calculate the net value of a field. Three values are required, the gross value, the net value and the number of decimal places to be used in the column which shows the answer.
| Name | =...... |
| Margin | 'rg_discount(!1_retail, !1_sellprice,2) |
If you want to produce output directly to Excel you can name the spreadsheet, worksheet, row and column offset, and execute macros before and after the output.
Make sure that the output type on the title tab is set to E and the export facility is excel (this must be lower case).
There are three functions you can make use of. These are input as derived fields.
This function enables you to specify the spreadsheet name you want to work with (Excel only). Three parameters are allowed. The first is the spreadsheet name complete with DOS path. This file must exist on your PC. The Report Generator does not create it for you.
The second parameter determines whether you want to save the spreadsheet once output is complete. You should normally set this to Y, otherwise the file may not perform correctly, or may be recorded as a read-only file.
The third option enables you to hide Excel while the export is in progress. This is the preferred option.
| Name | =...... |
| 'rg_export_filename("c:\apab\sheets\costs.xls","Y","Y") |
This function enables you to identify the sheet number within a previously named spreadsheet to receive the output. It also enables specification of a row and column offset from where the population of the sheet is to begin.
| Name | =...... |
| 'rg_export_position(3,2,2) |
This function is used in conjunction with the two other export options, and enables you to execute a macro at run time.
| Name | =...... |
| 'rg_export_macro("macro name","", ) |
Example derived fields
You could have two derived fields as follows:
'rg_export_macro("Before","",TRUE)
'rg_export_macro("After")
The first macro routine has a third parameter set to TRUE. If this parameter is set this way, the macro executes before the data is loaded. The second example routine is executed after.
The second optional parameter enables you to define a range in the spreadsheet covering all the data downloaded. for example:
'rg_export_macro("After","ExportedData")
This defines the range ExportedData which can then be used in the "After" macro with a command such as:
Application.Goto Reference:="ExportedData"
Or if you are recording then you can use a simple Edit/Goto command.
This option can be found when you double-click a line in the Derived Tab, insert field, and select Pre-defined functions.
Detail Selection: Enables you to click a line of a Totals Only report (or chart), and run a second report to illustrate detailed information from the first report. The initial Totals Only report could run up to five reports from the sub-total line.
A flag is included on the Title tab to indicate whether Detail Selection is supported within the report. This facility is supported for reports with output types of Display or Chart. If the Detail Selection check-box has been ticked, the user needs to complete certain Derived fields to control the report.
Once the detail report is run, the click the close button at the bottom of the form to return to the summary Totals Only report. You can execute any other line at this point by selecting the required line.
There are also facilities for you to allow variables to be passed between reports. This facilitates a wider variety of secondary reports to be run rather than relying on the sequence of the first report.
There are five options as follows:
See following basic report examples.
Enables you to drill into AK document. You can have up to five drill downs - drill down number specifies the position for in the drill down menu. Description is the drill down menu option description that is displayed from the report, for example Drill archive document. Unique reference is the AK reference for the document to drill into. The document can optionally be sent by fax if the fax number, contact and copy message are set.
Enables another report to be run from the report. The report ID must be supplied. This can be a report within the same (XXX) or a different module (MM/XXX). The subtotal level defines the level at which the report is automatically run. For example, the report can be set to run once subtotal level 5 is reached. If the report to be run has an overlay then this must be specified.
The Set and Get options enable you to control what parameters you require to go through to another report.
This explanation uses two reports to provide the first example:
Report 1 : This is the driving report. It is set-up as totals only and contains the derived fields required to initiate Report 2.
Report 2 : This is the detail report. It receives information from Report 1 to assist in its execution and to control the selection.
To allow successful detail selection, Report 1 will need specific action in three different tabs, Title, Derived and Sequence.
Report 1: Title tab.
The Title tab should have two check-boxes ticked.
First we need the report to run in Totals Only mode. This way we will get just one line for each record printed. In this example this one line represents the outstanding balance of a customer's account. This is held in the Sales Ledger Account file.
The second check-box to tick is the Detail Selection flag. This enables you to use the pre-defined drill-down functions.
Report 1: Sequence Tab.
In order for the first report to send suitable information to the second report, you must take care of the assembly of the fields used in the Sequence Tab.
When Report 2 is called to display or print the detail of a line from Report 1 (Summary report), details of the line selected are passed from Report 1 to Report 2 using the fields contained in the Sequence Tab.
Report 1 was set to have the Sequence defined as ACCOUNT, Report 2 (Detail report) would need a prompt to receive the Account Code, and it would be automatically filled in when the line is selected.
Report 1: Derived fields.
In order for Report 1 to send information to the second report, we now know that whatever is in the Sequence Tab will complete the prompts in Report 2. We need to let Report 1 know about Report 2. To do this we use the Pre-defined function parameters form.
This form is accessed through Drill-down parameters, Specify drill-down report detail. The 'rg_drill function asks for the following parameters to be filled in:
Drill number: A number from 1 to 5 to show the position on the drill-down list
Description: The description that is displayed when you right-click the line for which you want to see more information
Report ID: The report ID of the report you want to drill into
Output type: One of:
- D: Display
- P: Print
- H: HTML
Priority: The user priority needed to run the report
On the Derived Tab there is an extra report (Report A03) which shows or prints the details of the Customer.
The report IDs can be preceded by a module identifier if the report is in a different module. If Report 1 were running from Sales Ledger and Report 2 from Nominal Ledger, then Report 2 would have a prefix to the report as NL/A02.
Set-up of Report 2
The second report is set up in such a way as to complement Report 1. Attention must be paid to the PROMPT and SEQUENCE Tabs.
Report 1 automatically completes the prompts in Report 2 using the fields on the Sequence Tab in Report 1. In this case we have to set up Report 1 to break and subtotal by ACCOUNT.
Report 2 has a prompt of ACC$ that will accept the Account Code.
On the Sequence Tab, use the Prompt ACC$ in the Access Optimization section. This makes the report scan the relevant records in the Transaction file to ensure that the report runs efficiently.
Running Report 1
When Report 1 has been run, and you have right-clicked a line, the Summary form is displayed.
If you double-click, Report 2 will run. If you right-click a line, the detail menu is displayed. Choose the option that you require.
A Second Example: WIP Report.
You could run a Totals Only report on the POS Header file, subtotaling on WIPNO (the WIP number).
Two additional detail reports could be written to run off the first summary report as follows:
One report scans the logpa (parts log file), and the other scans the logla (labour log file). Both of these files have a first key-path which is primarily by WIPNO and therefore the data is easily accessible. Set up both reports to have a prompt for WIPNO and remember to add this prompt to the Access Optimization section of the Sequence Tab.
This Drill-down report displays an overview of your WIP headers with a simple way of getting more detailed information.
Warning: The Detail reports must make use of the Access Optimization option in the Sequence Tab, so that the report only needs to run through the necessary tables and not through the entire database, which would take up valuable resources on your system.
There are several controls that which can be introduced to HTML reports in order to enhance their appearance or usefulness. HTML output is becoming very popular, and is particularly useful if reports can be published straight onto a public access area of a computer where outside parties can gain access to valuable information. These reports can be branded with company logos by applying some quite basic derived fields.
| Name | =...... |
| 'rg_html_image("images/backkcc.gif" , "images/kcc.gif" ) |
This routine applies to an image to the HTML output, and uses the files specified as either wallpaper or a banner for the top of the page. Make sure that the image files are placed either into the HTML output directory, or as in the case above, in a directory within the HTML output directory.
Up to five different colours can be specified when using this routine. Any colours depend on personal choice and possibly on the corporate image you may want to maintain. The choice of colours used is based on the full range of colours currently in use within HTML applications. There are currently 200 colour names supported by Explorer and Navigator.
| Name | =...... |
| 'rg_html_colours("brown"," ","cyan","green","red" ) |
In the example above colours have been attributed to all but the heading, which therefore retains its default value.
An HTML link can be added to the top of the report output which links to another document, or location, if used. This could be a document explaining the report being viewed.
| Name | =...... |
| 'rg_html_link("Help document for viewing report", "../docs/Report_ADH.html") |
In this example there is a link to a document. We will assume that this document holds an explanation of all the fields shown on the output.
This control enables you to define a specific name for the grand total at the head of the report.
Tip: Remember - the final total appears at the top. HTML reports appear upside down.
It is a simple command, and very effective if used properly.
| Name | =...... |
| 'rg_html_all("Total of all payments") |
This control is used without parameters. It removes the banner, or column heading, from the section breaks on the output. This may be convenient if there are only a couple of columns on the output file. More complex output, however, may benefit from banner output to show what information appears on each column.
| Name | =...... |
| 'rg_html_no_banner |
The default output name for all HTML files is created by taking the two-character module ID and the three-character report ID, and putting them together with an extension of HTML. Thus report APD in the Sales Ledger module would return a filename of SL_APD.html.
Simply type in the output filename required and it is used in preference to the default name. Do not leave spaces in the file name: connect individual words together with underscores or hyphens.
| Name | =...... |
| 'rg_export_filename("Sales-Ledger-Debtors") |
This control enables you to output a maximum number of records. It is useful for testing purposes as well as allowing control of report size. This may be used to produce a league table of the top or bottom items in a particular file.
Be aware that this will not stop the report scanning a large number of records if the report is being sorted. In this case the system scans the whole database, and only stop after it reaches the required number of records at the output stage. If you want to test the report quickly and prevent the whole file being searched, you should use the 'rg_scan_only command.
You could of course PROMPT for the number of records to be either accessed or printed. The example below uses a prompt field called MAXNUM to determine how many records should be output.
| Name | =...... |
| 'rg_stop_after(!0_maxnum) |
This control enables you to scan a predetermined number of records. It is very useful for testing purposes as it enables you to scan just a few records in a very large file.
Tip: Remember that if the whole file is not being scanned only a partial result is obtained.
You could PROMPT for the number of records to be accessed. The example below uses a prompt field called MAXNUM to determine how many records should be searched.
| Name | =...... |
| 'rg_scan_only(!0_maxnum) |
Suppresses output of report summary.
Suppresses the display of report status, for example the number of reports accessed.
Suppresses output of blank lines. It can optionally set the number of characters to check. For example, if the number of characters is set to 10 and the first ten characters on the line are blank, then the line is suppressed.
This control is intended to help you to view displayed output. Some display reports are difficult to read if the output line is wider that 100 characters.
The report generator, unlike Ad-hoc enquiries, does not automatically split each item on the output line into individual columnar output. This means that unless otherwise directed the output is all within one column.
Control of the number of columns into which the output is split is passed to the user by use of a pipe symbol on the heading line of the format. If the pipe symbol is placed between each field heading, they will each adopt a column of their own on the output. This is then used by the system when the report is being viewed. As well as a scroll bar appearing on the right-hand side of the screen to enable you to move up and down the report, a scroll bar appears at the bottom of the screen to enable you to move left and right. The amount it moves with each mouse click depends on where, and how many, pipe symbols are placed on the heading line.
The addition of the derived field control 'rg_show_line also puts a faint line between the fields to show that a column break is in use. Add this in the Derived Tab as shown below.
| Name | =...... |
| 'rg_show_line |
This example report includes both options of the | (pipe sign) in the heading line and the derived field 'rg_show_line.
This option is for the advanced user. You can get more information about this function in Advance report generator.
It repeats the format line for a single record a given number of times.
Example: Warranty Claim records.
A Warranty claim record can contain up to 20 labour lines and up to 50 parts lines. This is very difficult to print because you do not know how much space to devote to the output either on a multiple-line report or on an RTF output. What is needed is to be able to do is to count the number of lines to be printed and then repeat the output line that number of times.
In this case we would count the number of labour and parts lines, take the larger of the two numbers, and pass this number to the 'rg_repeat routine. We would now be able to print three records with different numbers of lines each, and the correct number of lines would display on the format.
A feature has been included to enable you to chain reports together. Once the first report has finished it can initiate a second report, passing prompt values to the second report if required. The feature is useful if reports have to be run in a strict sequence.
There are three parameters required for the routine.
Report ID: The first one is the report ID that is run on completion of the first. If the report is within the same module as the first, all that is needed is a three character report ID. If the report being called is in another module, then the report ID should be preceded with the module identifier. Thus "PB1" and "SL/PB1" are both acceptable.
Pass Prompts: As the report being called may require completion of one or more prompts, this flag determines whether or not those prompt values can be forwarded from the first report.
If you want to pass field values through, the following points should be noted:
Set the pass prompts flag to Y
Create prompt fields in the first report to accept input as required
Create prompts in the second report with identical prompt field names
The values in the prompts on the first report are passed through to the prompts with the same field names in the seconds report.
Suppress prompts: As well as passing prompt values to the linked report you can decide whether the second report should pause and give the user a chance to overtype the prompt values before the second report runs.
This option can be used regardless of whether prompt values are being passed through. If set to a value of Y, the second report will execute without stopping for completion of the prompts. If set to N the second report will not run until the user has completed the prompt form.
Once the second report is complete, the system returns to the first report.
If there is no report which has an overlay or format stationery loaded then the output parameters would be represented thus: 'rg_link("MM/RRR","Y","Y"") Where MM is the module, RRR is the report ID, however if an overlay or format stationery is loaded for the second report then the output parameters would be represented thus: 'rg_link("MM/RRR,"Y","Y","XX/XXXX","MM/fsRRR") Where MM is the module, RRR is the report ID or the other letter report and XX/XXXX is the overlay program.
| Name | =...... |
| 'rg_notify("kcc@Autoline.com ","zzz@Autoline.com ","Subject ","Message ","N") |
The above table shows an example of this derived field that could send an email to a user to notify them that a report has been run. You also have the ability to attach a copy of the report to the email as well by changing the N to Y.
Note: 'rg_notify limit for Send to and Copy to is 80 characters.
These first three commands in print control relate to suppression. These commands suppress the banner (report column headings), form feed controls (used to throw a page), and the report prompts.
These controls would normally be used in two circumstances. Initially they would be used if the output is being sent through a printer with a KPRINT form. In this case you may not want the attributes set because they will spoil the appearance of the output.
Another occasion when the output could be stripped of these controls is when you want to re-direct the print output to a file in order to produce a flat ASCII output file for use in other applications. Obviously it is easy to achieve output to a CSV file, but sometimes formatted flat ASCII files are required.
Choose the control required and type them in as derived fields. No parameters are needed.
This suppresses production of the column headers on the report output.
This prevents the report from waiting for any user input.
This prevents form feeds being issued within the report output. This is a valuable asset if the report is going to be imported into a third-party spreadsheet or database product.
Used when carried or brought forward totals are required at the bottom of the first page, and top and bottom of subsequent pages.
Tip: When using this function and grand total formatting on same report, use !0_S_TOTAL$ on the format line to make the sub-total and the grand total print at the same column position.
Used to specify the starting page number for the report. This could be useful if the report is being sent with a covering letter, so the report can be set to have a starting page number of 2.
Enables the report to be archived in AK. Index sets DOCREF1 and the wordsearch fields correspond to those in the AK index.
String manipulation enables you to work on all or part of a text field. In order to use this sort of function it is important to know how the basic string command (STR() ) works.
STR(field name , start character , length of selection)
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. The parameters are separated by commas. If the start position is omitted, position one is assumed. If the length is omitted then the remainder of the field is used.
|
AC1$ |
STR(!1_ACCOUNT , 1 , 1 ) |
|
SNO$ |
STR(!1_SUFFIX , 2 ) |
|
PC$ |
STR(!1_POSTCODE , 1 , 4 ) |
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 characters of the suffix code into the derived field SNO$. The third example copies the first four characters of the postcode into PC$.
This command enables you to select a portion of a text field, starting at the first empty space relative to a selected position. You can select the space before or after the space.
If there is not an empty space prior to the position selected, the function returns the relative to the selected position. You can select the text before or after the position.
If we assume the following text field, and a small selection of examples, the results should be clear.
Field value = "London Small Industries Ltd"
'rg_split(field, 8 , 1 ) returns "London"
'rg_split(field, 8 , 2) returns "Small Industries Ltd"
'rg_split(field, 5 , 1) returns "Londo"
'rg_split(field, 5 , 2) returns "n Small Industries Ltd)
This function enables you to prevent information to be repeated in every line of the report. Displaying the selected string on a line only when its value changes enhances the appearance of the report output. Each string you want to suppress should have a different identifier. This is best shown in the example below. The account number has an identifier of 1, and the part number has an identifier of 2.
| Name | =...... |
| Derived 1 | 'rg_repeat$(!1_account , 1) |
| Derived 2 | 'rg_repeat$(!1_partno , 2) |
Output:
Account
Part No
Date
Price
A0001
ABC123
1/11/99
23.65
A0001
ABC234
2/11/99
26.99
A0001
ABC234
3/11/99
26.99
A0001
ABC234
6/11/99
26.99
A0001
ABC345
1/11/99
12.20
B0001
ABC123
2/11/99
23.65
In the example above the account code has been suppressed until it changes, so it shows only on the first line of each set of figures for each account. Part number ABC234 was supplied three times to account A0001, and this shows only once. Both values are displayed when their values change.
Returns a line of text from the supplied string. The string may contain carriage returns, so by setting the string number to return you can force it to return the specified line in the string.
Returns the number of lines in the supplied string. The string may contain carriage returns so it will return the number of lines that exist in the string.
There are two new additions to the system information section of the functions.
This function is only available on Revision 8. This returns values from db_parms$() variables for use within a report.
| Name | =...... |
| Otype$ | 'rg_parms$("$OUTPUT") |
Some variables are held by the system at an environmental level. This simply means that the value of the variable is available throughout the system at all times. This is handy for report generator users as they are able to access values from environmental variables from any report.
| Name | =...... |
| Country$ | 'rg_env$("COUNTRY") |
This function can be used where the user has to know which level of sub-total is being printed. It is particularly useful for adding text messages to specific sub-total lines. Advanced users can use this value to determine whether to carry out certain calculations, or to zero values at specific point in the report cycle. The routine returns an integer from 1 to 5 to illustrate the level currently being produced.
| Name | =...... |
| Sublevel | 'rg_subtotal |
You may use this value in a ternary to display different messages on different break points within the report.
| Name | =...... |
| TOTTEXT$ | ('RG_SUBTOTAL=1? "First subtotal" : " ") |
| TOTTEXT$ | ('RG_SUBTOTAL=2? "Second subtotal" : !0_tottext$) |
| TOTTEXT$ | ('RG_SUBTOTAL=3? "Third subtotal" : !0_tottext$) |
| Name | =...... |
| TOTTEXT$ | ('RG_SUBTOTAL=1? "Area code subtotal" : " ") |
| TOTTEXT$ | ('RG_SUBTOTAL=2? "Account subtotal" : !0_tottext$) |
| TOTTEXT$ | ('RG_SUBTOTAL=3? "Part number subtotal" : !0_tottext$) |
| Name | =...... |
| 'rg_concatenate_file |
This is useful when you run a concatenated file, and want to see on the report which file the record has come from
| Name | =...... |
| 'rg_company_file$ |
The company code of the file the record has come from.
| Name | =...... |
| 'rg_company_name$ |
The name of the company of the file the record has come from.
| Name | =...... |
| 'rg_record_count(Sub-total Level) |
This is only for advanced users of Report Generator and will not be discussed here. See Advanced use of derived fields.
This sets the parameters for the SMS message recipient, for example the recipient's name and number. The maximum allowed messages parameter is used to restrict the number of messages that can be sent from the report.
This displays a form for the user to enter the phone number and message to send by SMS.
The Report Generator prints an audit trail with details of the SMS messages that are sent when a report is run. The audit report has the following titles:
If 'rg_sms_suppressaudit is used then this audit trail will not be printed.
The Report Generator strips duplicate spaces from messages before they are sent. This is done to reduce the size of the message being sent. Calling 'rg_smstext_leavewhitespace prevents duplicate spaces from being removed.
This enables you to specify the presentation of chart reports by customising their appearance. You can select the type of chart (Graph, Display, Print or Spread), whether graph or pie-chart format output, and what axis to use (X or Y).
This function enables the report to specify default printer instead of prompting the user when the report is run.
This function enables the report to use the Kprint letterhead form.
This function enables the report to use the Kprint RTF definition.
This function enables the report to use the Kprint template.
This enables you to specify the presentation of the label output report by customising the number of labels per page and the spacing. The options are as follows:
Number of labels across the page
Number of labels down the page
Columns from left of one label to the next
Lines from left of one label to the next
Blank columns before the first label
Blank lines before the first label
This predefined function is enabled when the output type of the report is set to Export and a valid export definition file is entered. It enables the user to have a specific export definition and to specify a specific filename. Therefore it is possible to have a different filename for each report, even though it uses the same export definition. An example would be to have an export definition of ANDY for report AS1, with the export filename of ANDY1, and report AS2 with the same export definition and an export filename of ANDY2.
Advanced use of derived fields