L7 - User Defined Reports

User defined reports are one of the most powerful and flexible types of report with a large number of parameters and permutations available. Essentially the definition consists of building blocks which the report processor joins together to create the actual report. Building blocks consist of page headers and footers, master file blocks, detail file blocks, and sub-total and total blocks. Any of these can be one or more lines in length and are called printing blocks.

As well as defining printing blocks, we also define the conditions under which they are to print.For example, the first header and footer blocks may be different to subsequent ones; a master file block may print in (say) three completely different ways depending on what type of master record it is; different types of detail record may print differently depending on certain data within them.

The report definition, therefore, includes a series of control blocks which define how to print blocks, followed by the printing block (if any) associated with it.

An example ProFileruser-defined report definition is shown below, as it appears on screen when designing the report. Within the design area are control blocks which have a name preceded with a $ sign. These show as coloured blocks – nothing on the control block itself actually prints – this line controls how to print its related block below.

The set of lines between control blocks is the printing block which prints each record (subject to configuration) as defined in the control block immediately above it. You don’t need to specify blank lines between blocks for formatting or readability purposes as these are defined by the control blocks. If you delete a control block in a definition, the printing block immediately below it is also deleted.

Starting a New Report Definition

When you create a new definition file, and select the User Defined Report type you are taken into the General Parameters section of the report.Each time you open the report after this you will be taken directly to the print mask.

General Parameters

The General Parameters control some of the basic structure requirements of the report – defining the page size (lines down / characters wide), etc. Some of the parameter options change, or are omitted, depending on whether the driving database of the report is defined as a master file, a detail file or an independent file.

Type in the heading for the printed report here – the name you gave in the menu option is offered as the default name. You can print the name you give here directly onto the report as a constant (see below).

This refers to the sets of print styles already set up for the printer under the CONFIG user sign-on.The print styles defined are shown on the F4-Select key. Key in a number, between 1 and 8, for the print style you require.

This represents the full length of the page. The maximum number is 132 but usually this is set to 64 or 66 lines (note that this should match the lines down of the print style selected).

This represents the width of the page. The maximum number is 255 characters but you are likely to restrict this to either 80 or 132 characters (again matching the characters width as set in the print style).Generally speaking the wider the report the smaller the text detail will be printed.

Leaving blank instructs the system to pause the print after each page to allow you to insert another sheet of paper. This option is usually set to automatically sheet feed.

If printing to a Direct Windows alias then this option will automatically select the landscape print orientation – leave blank for portrait print.

There are occasions when you may want to produce multiple copies of the same page – e.g. delivery notes, packing notes, action letters, labels and so on. This option allows you to specify at run-time how many copies you require.

It is usual for this option to be used where the report prints a page per record – repeated blocks of text on a single page would be an uncommon requirement. One particular use allows you to print a user-defined report to label stationery (a single label being a page). This could offer you greater flexibility over the standard label type of report – allowing use of accumulators. However, only one label can be printed across the page with this option.

If you wish to prompt for more than one copy of each record as it is printed then select this option.You will be asked for the number of copies as part of the report preamble, unless you have also chosen the "allow list” option (see below) in which case the number of copies to print is asked for each individual record chosen.

Note on Copies

When defining report items, there are two items that you may wish to print for each record. You are able to print, via the Report Constants, the current copy number and the total number of copies being printed for this particular record.

As an alternative to specifying the number of copies at run-time you can ask that the number of copies be taken from a data item in the record itself. This allows you to print a different number of pages or labels for each individual record. Enter the data item which holds the number of copies (if select an item which includes decimal places – i.e. K type – then the decimals are ignored). This option could be used if printing labels for delivered goods – one label per individual item ordered.

This option allows you to nominate a data item on the record which will be updated with the number of copies printed for later enquiry.

This is a powerful feature to check for duplicate records in the file. If you select this option you are asked for some extra parameters to test for duplicates. Note that, if selected, the report only prints records which are duplicates of each other.

Applications such as mailing list management systems need a technique to discover and erase any duplicate records in a file. A user-defined report provides the first step in the process, printing and marking apparently duplicate records.

First the records are sorted into a sequence which allows the process to check if any adjacent records have exactly the same information in the fields concerned. You can ask that duplicate records are marked, so a later process can delete the marked records. If you have chosen this option then the pop-up on the left is displayed asking for some specific parameters.

Specify here up to five fields which are to be examined to see if they contain the same information – only if they contain the same information are the records printed.

The longer a field the more chance that a typing error or extra text will mask duplicates. For example a surname might be "Huntington-Smythe” in one record and "Huntingdon Smyth KCB” in another. You can tell they are probably the same person but the computer would reject the match – if you truncated the field to some 10-12 characters they would match. Leave the truncate option as zero if you do wish to compare the whole field.

Note

Only alphabetic and numeric characters are compared – spaces and punctuation marks are stripped out. Alphabetic characters are also treated as single case. This helps in that "32, St. Martin’s Road” will match against "32 ST MARTINS ROAD”

Unless you separately define a report print sequence for this report (see later) then the file is sorted by the five comparison items you specify here. If you define a separate report sequence then it should include the items specified here or you are unlikely to find duplicates.

The procedure can also flag the second and further duplicates with the value of a given item if you complete the final parameters. For example, if the file has a prime-index you could define an extra item in the file to hold the value of the prime-index of the first record in a group of duplicates.A subsequent global update could then delete all records which had a non-blank or non-zero value in this item.

As an alternative to a duplicates check you may wish to check for missing items. Say you import sales transactions from another system into Datafile you may wish to look for missing invoice numbers in the import file which may indicate a problem with the file. Selecting the option gives a pop-up with further parameters.

Select the item you wish to search for missing references within – note the data within this item must all be the same length. This item must also be set as the primary report sequence item and defined within the selection criteria as a range check (where you define the first and last value expected – i.e. REF range 000105 to 000366)

Select the size of the item to check, for example if the REF was an X(10) field but the value being checked is only six characters enter 6 here.

This option adds to the report preamble the option to select the first and last of a range of records to be printed – say a range of account or stock codes, or a range of order references – separately from the usual selection criteria.

This option adds to the report preamble the option to ‘Report Totals Only’ allowing the report to be printed showing the totals only (omitting the $RECORD and $DETAIL blocks) to provide summary information but also allowing the report to print the detail when required.

This option allows, at run-time, the option to select the records – by record number for a non-prime-indexed file or by the prime-index – the records to be processed on the report (<F4-Select> will be available when ‘pick’ items are defined within the Data File Profiles).

If selecting a list of records to report set this option if only one record is to be processed for any single report.

Option to reference a data item, on the driving (main report) file, to be marked as updated when the record is processed on the report. This allows for the use of selection criteria to ensure that a record is not processed more than once or a check to see that the record has been reported.The flag data item can be either a ‘?’ type (in which case it is updated with ‘Y’) or a ‘D/E’ type date item (in which case it is updated with the date the report was run).

Slots are counters held in the database header which can be used to speed the printing of reports where records are only to be printed once – for example a day book style listing.Up to 10 slots are available per database; though note that application databases have several slots reserved for their own use. Setting this option gives a pop-up with two parameters.

Set the slot number you wish to store the highest record number that the report prints in, and, if required set the report from slot (these would usually be the same slot).

Set to lock records on a record-by-record basis rather than locking out the entire file. File/records will be locked only if set to flag records as processed or writing back to the file in other means. Note locking on a record-by-record basis is slower than locking on a file basis.

If the report is based on file 1, the master file, you can include records from an associated detail database within the report. Enter the file number for the detail database here (<F4-Select> is available if required).

Set to omit the main file record from being processed / printed if it has no detail records – for example on a statement style report this would allow you to omit the account if it has no transactions.

Set to print the detail records in reverse order (latest first). Note this option is only applicable if the Data File Profiles have the ‘PREV-POINTER’ option configured.

This allows the option to restrict the number of detail records printed on a report (max 999).For example, if printing a contacts summary you might restrict this to the last 10 contacts (assuming printing details in LIFO order above).

Set these options to define selection criteria for detail records to be printed, whether to allow the operator to change the criteria at run-time, and whether to save any such changes as the new default.

If the report is based on the detail file then these options are available to allow selection criteria based on the master file. If the master record does not match the defined criteria then the associated detail records will be omitted.

In addition to selection criteria to select master records this option allows the operator to key in the prime-index of the master record (e.g. the account code) of the details they wish to print.

These two options allow you to restrict the number of records reported either on a page-by-page basis or on the report as a whole.

When printing a report the totals are only available after all the records have been printed. In some cases, such as when you wish to calculate the percentages of a total, you need the total value before the record is printed. This option allows you to nominate one item on the main report file to be totalled and available throughout the report print via the Report Constants.

Note for this item to be available the report must be set to be sorted in a user-defined sequence.

Page Set-up / Converting a Report

If creating a new report (or updating an older style user-defined report) then before displaying the print mask you will be prompted to convert to the new style report.Whilst the new style report does not have a WYSISYG designer at present it does allow for the control of print fonts for text and data items. Select to convert the report as required.

This option offers a drop-down list of paper sizes available. Select the paper size required or, if not listed, select the Custom page size and define your own.

If you select a pre-defined page size these are set automatically for you, if you select a custom page size you can key in the page size (in millimetres) required.

Set whether the report is to be printed in landscape, leave blank to print in portrait.

This defaults to the values previously defined on the report (or if a new report to 132 columns across / 66 lines down). If converting a report you would typically leave these as set.

The values can be amended up to 255 columns across and 132 lines down. The columns and line values form a ‘design grid’ on the report with the number of columns set controlling the number of grid start points across the page, the lines down controlling the grid points down the page (think graph paper).Text and data items must start at a grid point.

Before discussing the specifics of designing a user-defined report you should review the design toolbar.The toolbar has the options – File, Edit, Tools, Parameters and Controls. Each of these offers a list of actions available.

The File menu allows you to save the report layout, amend/view the page set-up, preview the design to screen (with fonts applied) or to a printer and to exit from the report designer (you will be prompted to save if you haven’t already done so).

The Tools menu allow you to restructure a database, refresh the current display, switch between line and text mode, suppress the data item display (you may want to temporarily switch this off so that you can enter text easily behind the item), define links to MS Office (Excel), configure CSV/TXT file generation, print the report definition and hide/display the report navigator.

The Parameters option allows access to the General Report Parameters, define selection criteria and report sequences, plus define accumulators and accumulator constants.

The Controls option allows you to insert, delete, replicate, move and change control blocks and also allows you to define the fonts available within this report.

Designing a Report Definition

The process is in essence straight-forward, you generally design the report in two steps.

Essentially you can type whatever text you want to print on the report into the defined print blocks on the screen, and use the <F4-Key> to drop data items on to the report. The screen shows a window on the report and you can move the screen using either the navigator displayed on screen and use the arrow keys or the mouse to move the cursor to where you wish to enter text / display a data item.

When the report mask is first displayed on a newly created report a screen such as above is displayed where the only control block entered is the $END. You insert control blocks above this position by either:

Using the mouse to right-click on an existing control and choose one of the options, or

Select the Controls pull-down off the main toolbar and choose one of the options here.

Insert Control allows you to insert a new control above the current active control (i.e. where the cursor is currently). Delete Control allows you to delete the current control block and is associated printing block. Change Control allows you to change options on the current control.

Move Control allows you to move the current control and printing block from its current position to another, and Replicate Control allows you to copy a current control and printing block as another.

The Print Properties and Background Colour options allow print formatting sections for the current control and printing block.

The Controls option off the main tool bar offers much the same options except that Change Control also allows you to change the current ‘type’ of control and View Control displays the additional settings on the current control.

The Font Settings option allows you to define the default font and the fonts to be used on this report.

Inserting a Control Block

When you select to insert a new control block you are asked for the type of control you wish to insert.Options available include:

This control block is printed, if specified, at the top of each page and is used for the print of report titles, report dates, any selection criteria used, etc. Sub-options available allow for:

FIRSTONLY – this control block is printed for the first page only but omitted from subsequent pages. You might do this as you need to print a large amount of detail on the report but don’t require this repeated on each and every page.

OMITFIRST – this option can be used in conjunction with the above in that you have a large $HEADER which is printed on the first page only and then a smaller $HEADER is printed for each subsequent page.

BLANKBEFORE – ‘prints’ a set (defined by the operator) number of blank lines before print of the control block (you may do this on a $HEADER if printing on headed stationery for example).

BLANKAFTER – ‘prints’ a set number of blank lines after print of the control block

SKIPBEFORE – skips to a set line on the report page before printing the current control block.

SKIPAFTER – skips to a set line on the report page after printing the current control block.

INCCOUNT – only available if the application uses a constant db this only allows you to increment a counter field each time this current control is printed (the counter is usually a N-type or Y-type data item) and could be used, for example, as a reference number for a day-book style report. When you select this option the system asks which counter and this corresponds to the data item on the constant file you wish to use.

CLRCOUNT – this option resets a counter on the constant file back to zero when this control block is printed.

Only applicable if the report ‘driving’ file is a detail file. This option allows you to design a section of the report in which ‘master’ information is printed. For example, if designing a statement style report this could include the account name and address details. If a page break occurs when printing the record detail then the $MASTERHEAD is reprinted (after the $HEADER detail) on the next page.

The sub-options available on this control include the BLANKBEFORE, BLANKAFTER, SKIPBEFORE, SKIPAFTER and INCCOUNT / CLRCOUNT options. These work as above.

This control block allows you to design a ‘title’ section of the report for the following details.For example, say you were listing sales invoices for salesperson and the report was grouped to list the invoices for each salesperson in turn. You could use the $SUBHEADER block to print the salespersons name and other details about them before showing the breakdown of the sales invoices they have recorded.

You can have multiple $SUBHEADER blocks breaking on different items – it would be standard practice to define the report sequence items (discussed later) in the same order as the break items.

Several sub-options are available, some of which were discussed above, but in addition the following options are available:

BREAK – the most important option for a SUBHEADER block (and mandatory). This asks for the data item on the driving file for which the SUBHEADER is being printed – i.e. the salesperson on our example above – the SUBHEADER is printed each time this value changes. You would normally define this item as one of the report sequence items also (so that like records are reported together).

EACHPAGE – this option, if set, reprints the SUBHEADER block if a page break occurs when printing the records for this group.

TEST(1-3) allows entry of a condition which must be true before the sub-header block is printed.If more than one condition is applied then all must be matched. When defining the condition you are first asked for the data item to test (this is usually an item on the driving file but occasionally you may wish to select an accumulator item (see later) or a constant item to test. Next you are asked for the Comparison Type – whether the check is to be Equal, Not Equal, Greater or Less than another item/value – before being asked for the Value. If the value is a data item <F4-Select> is available to choose, otherwise if specific text then enter the value in quotes – i.e. "1.00”

This control block prints for each valid record in the driving file and shows the main data being reported. You can have multiple $RECORD blocks on a report and each block is processed (and printed) before moving on to the next record. Were the driving file is, for example, the master file you may have a $RECORD both before and after the detail block. Alternatively you could use TEST conditions so that different details print depending on the data involved.

Several sub-options are available and the majority have been discussed earlier but in addition are options for:

TEST(1-3) allows entry of a condition which must be true before the record block is printed.If more than one condition is applied then all must be matched. When defining the condition you are first asked for the data item to test (this is usually an item on the driving file but occasionally you may wish to select an accumulator item (see later) or a constant item to test. Next you are asked for the Comparison Type – whether the check is to be Equal, Not Equal, Greater or Less than another item/value – before being asked for the Value. If the value is a data item <F4-Select> is available to choose, otherwise if specific text then enter the value in quotes – i.e. "1.00”

NOPRINT allows the control block to be processed but omits print of the actual block. You typically use this option when saving or clearing items within the report accumulators.

FLAG allows you to record a value back on the record currently being printed – you might record a reference number on a day-book style report back to the transaction.

This control block is only available in master / detail style reports where the report is based on file 1 (the master file) and the general parameters of the report definition defines the detail file to be used. You can have multiple $DETAIL blocks using test conditions to control which blocks print based on the detail record content. The $DETAIL blocks are repeated until all (appropriate) details are printed for the current master record.

Several sub-options are available for this block including TEST(1-3), PAGEBEFORE, PAGEAFTER, BLANKBEFORE, BLANKAFTER, SKIPBEFORE, SKIPAFTER and NOPRINT which have been discussed above.

This control block prints when a break occurs and at the end when all records have been printed. It is generally used to print total information for a section of records – perhaps total sales and costs for a salesperson. You can have multiple $SUBTOTAL blocks (up to 20) but if more than one specified then the order in which they are defined becomes important – if a break occurs for a sub-total then all defined sub-totals before it will also print. $SUBTOTAL blocks are usually defined in the reverse order of the report sequence.

Several sub-options are available including the mandatory BREAK option which controls when the sub-total prints (on change of value in this item). Additional options include TEST(1-3), PAGEBEFORE/AFTER, BLANKBEFORE/AFTER, and SKIPBEFORE/AFTER which have been discussed above.

This control block is only a problem when the report is based on a detail file. It is printed at the bottom of each page in the same way as the $FOOTER discussed next when the page becomes full but it is also printed when the value of the master code changes – i.e. when the next record to be printed from the driving file has a different master. Note that you would typically have two $MASTERFOOT blocks – one with a CONTINUATION attribute set, one without so you can have a different footer where the detail continues on the next page. If a $MASTERFOOT prints then any $SUBTOTAL blocks defined will print before this.

CONTINUATION – if set the control block is only printed where a page break occurs and the next page will continue printing records from the driving file relating to the same master code. If not set the control block is printed on change of master code.

PRINTAT – specifies the line number on the page that the control block is to print at.

This is printed at the bottom of each page when a page break occurs. The line at which it prints is determined by the PRINTAT attribute. You can also control whether the footer prints on the first page only, or have different $FOOTER blocks for first/last page and the other pages.

Attribute options available include FIRSTONLY, OMITFIRST, OMITLAST and PRINTAT which have been discussed earlier.

This is the control block that signals the end of the report block and is inserted automatically for you.

Defining Report Items

Setting the control blocks defines the structure of the report; the next stage is to insert the text and data items you wish the report to contain.

Text Labels and Titles

You can insert a text label by positioning the cursor with the mouse and then simply type the details you require. To clear text you can press the delete key but this moves all text items on this line one character to the left, more commonly you would use the <SPACE> character to clear (provided you are in OVERTYPE mode).

When entering new text if the INSERT mode is active then any existing text to the right of the cursor moves one position to the right for each character entered, if not active then any existing text is overwritten. The status line at the bottom of the screen shows whether INSERT or OVERTYPE modes are active and you can switch between the two by pressing the INSERT key.

Line Drawing

To insert lines you need to swap from text mode to line mode using the Tools pull-down option. Once in line mode then use the mouse to click the starting position and then, holding down the left mouse button, drag the line vertically or horizontally to when you want it to end.

To change the length of an existing line, shorter or larger, click the line so it becomes active (turns blue) and then move the cursor to the end of the line (the mouse pointer icon will change) and then, holding the left mouse button, drag the line shorter or longer as required. To move a line, select it with mouse so it becomes active and then drag it using the mouse to where you want it to be. To delete a line, select it with the mouse and then press the <DELETE> key.

Data Items

To insert a data item you can either move the cursor to where you wish the item to be printed and press <F4-Select> or right-click the mouse at that position. When selected a pop-up lists the available categories from which an item can be selected.

The categories available show all the application databases plus "Report Constants” and "Accumulators”. If you choose an application database, or the accumulators option, then the file structure is shown so that you can choose an item from it. If you move to the end of the file structure an extra item *CALC allows you to create a special calculated field. If you choose the "Report Constants” option then a list of possible constant items is offered from which you can select an item.

Whatever you select a shaded block will appear at the cursor position representing the item selected and the print properties for the item are displayed.

The print properties are typically in two forms – depending on whether the item being printed is a value or not. For text and items such as dates you are prompted with the following:

Print in Full – set to print the content of the data item in full. For text items (such as X or Y type data items this has no impact) but for Date items or Table / Constant type data items then this has an effect on what is printed. For a Date field (D/E type) then if set to ‘Print in Full = Y’ then the date is printed as text description – i.e. 25^thDecember 2007 – whereas if set to ‘Print in Full = N’ then the date is printed as digits – 25/12/2007. Similarly for a Table (T type) or Constant (C type) data item then ‘Print in Full = Y’ prints the descriptive associated with the table/constant whereas ‘Print in Full = N’ prints the content of the data item.

Offset to Print From– defaults to ‘1’ and is usually left as such to start printing the content of the field from the beginning. Sometimes however you may only be interested in a selected portion of the field – i.e. for a date you may only be interested in the month/year but not the actual day so that setting the offset to ‘4’ starts the print at the month portion.

Size to Print – controls the number of characters from the starting point that is to be printed. This defaults to the maximum size of the print item and will, if increase the offset position, reduce accordingly but you can override this if required to make the print shorter if trailing information is not required.

Omit Printing – this option allows you to omit printing of the set item. You may ask why you would place an item to print but not wish to actually print it (!), this however relates to the next parameter which allows you to save the item value into an accumulator where it may be totalled / manipulated as required.

Move to Accumulator– the accumulator ‘file’ is primarily used for total items but you may often move text or date items into this file for use in flagging back to the main reporting file.

Print in Full – primarily on value items this controls whether any leading spaces are to be ‘printed’ when the value is printed.Usually this is left as ‘Y’ so that decimal points align when reading down the report. In addition you could set this parameter as ‘B’ so that instead of printing ‘0.00’ when a value is 0 the print is omitted instead.

Full Format Printing– only applicable if set to print in full, this option is typically only used where the user-defined report is creating a TXT or CSV file for interface with another system – perhaps a BACS update.When set for full format printing the value is printed as a 22-character data item (subject to other settings) with 16 significant figures, the decimal point, 4 decimal places and the potential minus sign.

Omit Decimal Point– only applicable if using full format printing this option omits the decimal point from the full format value – i.e. prints the 16 significant figures followed by the 4 decimal places and the potential minus sign.

Offset to Print From– operates the same as discussed above when printing text/date items the offset defaults to ‘1’ to start printing from the first character but can be increased as required. It is more common to increase the offset on value items so that the size of the item printed is reduced by omitting the leading ‘blank spaces’ – i.e. a L-type data item covers 14 characters – 10 significant figures, the decimal point, two decimal places and the potential minus sign but if the maximum value held is in the thousands then printing the first 4/5 characters can be wasted space to be omitted on a particularly detailed report.

Omit Printing – flag to omit print of the item. This is used when the item has to be used in an accumulator but is not required for printing in its own right.

Append ‘-‘ if negative– if the size to print is set so that any potential minus sign would not be printed (perhaps you’ve omitted the decimal places) then this option allows you to add the negative sign at the end. If size to print would include the minus sign then this option is N/A.

OR print ‘-‘ at start– again if the size to print is such that any potential minus sign would not be printed then this option allows you to prefix the value with the minus sign.

Print Leading Zeroes– where a data item has less significant figures than is set to print a blank space is usually printed. This option allows a 0 to be printed instead.

Add to Accumulator– allows selection of a data item on the accumulator ‘file’ which the current data item value is to be added to.

Accumulator Items

When printing an item from the accumulator ‘file’ an additional prompt asks ‘Zeroise/Clear After Print’ which if set will reset the value to zero/blank after printing the item. This becomes particularly important when using these items in $SUBTOTAL blocks when you wish to clear the value after printing the first sub-total so that the next record block begins again at zero.

$SUBTOTAL Items

When printing an item within the $SUBTOTAL you will be prompted ‘Display or Total’ – if printing a text data item or an accumulator you would usually respond with ‘D for Display’ as this prints the value from the last record processed (in the case of a text data item) or the current value of the accumulator. When printing a value item then D-display again prints from the last record processed but it would be more common to respond T-Total so that the total value for all records processed for this $SUBTOTAL block is printed.

*CALC Fields

As mentioned earlier when printing from a application database or the accumulator file the final item after the database structure is *CALC which allows you to design a free-form calculation based on the current record.The calculation is defined as per the standard database options – i.e. +D11 *D15 /C1 etc.

When printing a *CALC item within a $SUBTOTAL or $TOTAL block then two additional print parameters are prompted. Firstly you are prompted ‘Average Calculation by Number of Records’ respond ‘Yes’ or ‘No’ as required. The calculation is performed for every record processed and the total added to that for the previous record. This option then divides this accumulated total by the number of records processed to achieve an average value for all records printed rather than a total.

Secondly, you are prompted ‘Recalculate at Totals Level’. As mentioned the calculated result is accumulated for each record and printed in the $SUBTOTAL or $TOTAL control block. However, if the calculation is being used to calculate (say) a profit margin percentage then adding this value up would be meaningless. Setting this option performs the calculation based on the accumulated totals of the component parts

Once all the print parameters have been set you are returned to the report mask.

To move a data item on the report select it with the mouse and then drag to where it is required (note you cannot move an item between control blocks).To update the properties of a data item right-click it with the mouse. To delete a data item from the report select it with the mouse and press the <DELETE> key.

Report Constants

The report constants are one of the categories of data items available when inserting a data item to report and include:

Date (8-DD/MM/YY, 10-DD/MM/YYYY) – prints the date the report was run. If printed ‘in full’ then the date is printed as per 25^th December 2005. Otherwise the date is printed as 25/12/05 or 25/12/2005 depending on whether the size to print is 8 or 10 characters.

Report Name – prints the name of the report as entered in the General Report Parameters.

Break Value – can be printed within the $SUBHEADER or $SUBTOTAL control blocks – this option prints the current content of the set control block ‘break’ item.

Report Comment – allows print (and prompt at run-time) of a user entered report comment.

Record Number – can be printed within the $RECORD or $DETAIL control blocks – this option prints the record number of the current record being printed.

Total Records – can be printed within the $TOTAL block this option prints the total number of (main file) records processed in this report.

Sub Total nRecords – prints the total number of records processed for the $SUBTOTAL control block.

Total Copies – if report configured for label print and multiple copies within the general parameters this prints the total number of copies of the label that are to be printed.

Company Code – prints the three-character code of the currently active Datafile company

Total Pages – only applicable if the report total item is defined within the report General Parameters this option prints the total number of pages printed on the report.

Total Value – again only applicable if the report total item is defined, this is the total sum item of this value for the report as a whole.

This Page Value – and this is for the current page (these items are usually printed in the $HEADER block).

Main Selection Criteria n – prints the selection criteria (entry 1-6) used when processing this report.

Sub Selection Criteria n – prints the selection criteria (entry 1-6) for any detail records processed in this report.

Multi-File Set – if the ProFiler application configured to use the Multi-File Set options then this prints the current file set id.

Line Continuation Marker – only applicable if configured for output to disk (i.e. create a CSV file) this allows you to define an output over more than one line on the UDR but to create a single line on the output file.

Set Default for Accumulators – allows for the initialisation of accumulators with default values. Whilst a ‘data item’ is displayed on the mask no value is printed.

Set-up Extra Lookup Links – where the main file (or detail file) allows lookups to another file then you can print data items from that lookup within the control blocks.Screen actions allow you to define multiple lookup items to the same file but the report can only report from the lookup file in relation to the lookup item defined within the Data File Profiles of the application. This option allows you to switch the lookup item to another main file (detail file) item and subsequent print items from the lookup file within the current control block are printed based on this lookup.

Accumulators

Accumulators proved a very powerful way to carry out arithmetic functions within reports.Once accumulators have been defined, the can be printed and manipulated anywhere within the report definition itself.

Up to 160 accumulators can be defined and used for any one report, and the technique is to treat them as thought they were a separate file definition (though in this case the ‘file’ has only one record). Accumulators are defined via the Parameters pull-down menu off the main toolbar within the report design.

You create the accumulator ‘database’ items as you would a standard database – press <Insert> or <Enter> against an item, enter the data item name label, select the item type and then define size / other attributes as you would a standard data item. Once complete press the <ESC> from the list to return to the report mask.

Any time a value is defined to print on the report, it can also be added into one of the accumulators.Accumulators can also be printed at any time, and their values added into other accumulators at the same time.The option also exists to zero an accumulator immediately it has been printed to allow for the manipulation of accumulators in sub-totals (i.e. print the total for a group of records, then zero it to start accumulating for the next group).

Using the technique of an extra database definition the accumulator ‘database’ also allows 100 constant values associated with the accumulators to hold specific values used in calculations.Accumulator Constants are also defined off the Parameters pull-down menu.

Depending on the report requirements you may only rarely need to use accumulators, since user-defined reports cater internally for the accumulation of printed values at sub-total and total levels.

Selection Criteria

This option, available off the Parameters pull-down menu on the main toolbar, offers the standard selection criteria options against the ‘main reporting file’ for this report.

Apply Selection Criteria To This Report – allows definition of up to six lines of criteria for printing/processing records from the main reporting file. Note that if allowing changes and you wish one or more lines to be ‘fixed’ then after setting whether a criteria line is mandatory or optional a pop-up parameter asks whether you wish to ‘Prevent User From Changing This At Run-time’ which you can set to Y or N as required.

Allow Changes to Selection When Run – set whether the user can amend the criteria when running the report or not. If set to allow changes then a pop-up asks whether these changes are to be made in ‘Easy Mode’ only. If set then only the matching values can be entered by the operator, the data item names and condition types can not be amended (the full criteria options can still be accessed though via the F7-Option key).

Save Any Changes Made To The Selection – set whether any changes made by the user at run-time are to be saved back to the report definition so they are offered as the default criteria next time the report is run.

Report Sequence

This option, again off the Parameters pull-down menu, allows you to define the sequence the records are printed / processed in. You may print reports in any sequence you want by defining the sorting sequence on the main reporting file by naming up to four items to use as sort keys.

If you are using sub-totals on your report then the report sequence is very important – you typically would define the sequence in the reverse order as your sub-total break items.

Which Sequence Type– Three options are available, setting to ‘1’ prints the records in record number order (the order they were entered), setting to ‘2’ prints the records in prime-index order, whereas setting to ‘3’ allows you to define your own sequence.

Item / Size / Asc or Desc – If using your own sequence they you can select up to four items from the main reporting file to sort the report upon, <F4-Select> is available to select the item. The size option defaults to the size of the data item but you can reduce this if you only wish to sort on the first X characters of the item.Finally you set whether the sort sequence for this item is processed in ascending or descending sequence.

Setting Default and Allowed Fonts

The ProFiler designer allows for the setting of a default font and a further 16 fonts that can be applied to the report design. This is achieved through the ‘Font Settings’ option available on the Control pull-down.

If no default is set then a font of Courier New 10 point is applied during print preview and at run-time.

To update the default font or create a new font application highlight the entry and double-click (or click the UPDATE button or press the <ENTER> key).

Enter the description of the font to be used. You might use this to describe the areas the font is to be used – ‘Report Title’, ‘Column Headings’ etc. For the default font leave this as set.

Set the alignment to be used for text and data items the font is applied to. Select from Left, Right and Centre. Each text and data item starts and finishes at a particular column, left alignment ensures starts print at the start column, right alignment ensures ends print at the end column and centre alignment centres the text / data value within the start and end column positions.

Even when left aligning text and data generally it would be usual to right align numeric values so that the decimal points on data aligns correctly for ease of reading.

When printing text, the system interprets a gap of two or more columns between text items as being separate text labels, this can cause larger gaps between words than you’d like when printing continuous text. Setting this option treats all the text within the defined area as one continuous block – you would normally set this for report title areas but usually would be left unset.

After confirming the parameter details the font dialog is displayed for you to select the font, font style, size and colour to be used.

Select the font and attributes required and then click the OK button and then on return to the font entry details press OK to save the entry and return to the font list.

From the font list you can select to define / update another font or use the OK button to return to the report design. The Enquire button displays where in the report the highlighted font is used, if the font is unused then the system will offer to delete the entry at this point.

Applying Fonts to Control Blocks / Selected Areas

The designer allows for the application of fonts to a control block as a whole or to defined areas within the block. To apply a font right-click on the control block and select ‘Print Properties’.

The Print Properties block only applies to the selected control block and you define up to ten separate font areas. Any area within the block left undefined applies the default font.

Select, via <F4-Select> if required, the font to be applied. You can, at this point, use the <F7-Option> key to define a new font entry.

Set as required whether the font is to be applied to text and/or data items within the set area.

Define in Column (left/right) and Row (top/bottom) positions where the font is to be applied. If leave values as zero then this font will apply to all items within the control block overriding the default, although this in turn can be overridden by a font entry that is applied to a particular region.Note that the Top/Bottom row entries relate to rows within the control block and not the row overall.When on the document mask the system will show the current control block row number in brackets within the status line.

Applying a Background Colour

To apply a background colour to a control block right-click against the control block and select ‘Background Colour’.

The current colour is displayed within the pop-up form and you can amend this by clicking the COLOUR button.

You can select one of the predefined basic colours or create up to sixteen custom colours that can be used as the background. Note that custom colours are defined for the report as a whole (i.e. only sixteen per report not per block). Once selected click OK to return to the Background Colour form (which will have the selected background colour displayed) and then click OK to apply to the control block.

Note that the background colour is applied to the control block as a whole (no defined regions) and that data items and text labels are applied as transparent items upon this.

Tools / DB Restructure

When defining a report you may find you need to restructure a database being reported on – to add or change a field you need in the report perhaps. Selecting this option asks for the database you wish to restructure.Enter the pathname for the required database and press <Enter> - the standard database restructure options are available. Once the restructured database is updated then you are returned to the report definition.

Tools / Refresh

This option suppresses the display of the data item blocks, this can be useful when designing the report and placing text / lines behind items.

Tools / Link to MS Office

This option allows you to set either the $RECORD block(s) or a $SUBTOTAL block to be output to Microsoft Excel.

Allow Output to Excel at R-Record or S-Sub-Total Level – set to ‘R’ or ‘S’ to output the data items within the selected control block to Microsoft Excel.

If Sub-Total Level, Then Specify Which Level – you may have more than one sub-total. Set the sub-total block, from 1-20, that is to be output.

When outputting a control block to Microsoft Excel only the data items are output (no text labels or lines etc). Each data item is updated into its own cell – the order of output is each line in turn from left-to-right. If outputting at the $RECORD level and you have more than one $RECORD block then each block is its own row in Excel.

Tools / Report W.P. Disk File

As an alternative to outputting to Excel this option allows you to output the report to a TXT or CSV file.Apart from the output file itself the primary difference is that the whole report is output using this option and the report is usually structured for the output – i.e. if creating a CSV you specify the data item, followed by a comma, then another item and so on.

Specify the Full Pathname of the Data File to be Created – enter the pathname of the file to be created. You can use a filename of CONSTnnn.CSV to pick up the filename from a field on the constant file; alternatively you can use UUUUUU to replace the characters with the user-id.

Allow Above Pathname to be Confirmed at Run-Time – set to allow confirm of the path by the user at run-time (note if using the special functions these will be in the path to be confirmed – the user-id/constant data item will not be replaced prior to the confirm).

Output A Control-Z at the end of this file – set to output an ‘End-of-File’ character at the end of the output file.

Omit Spaces Around ",” delimiters in this file – often a text data item will have space at the end of the field, or a numeric item will have space at the start as the content of the item doesn’t fill the available size.Setting this option removes these spaces from the output file.

Web Style – similar to the above but removes spaces within quote marks around data items.

Automatically Load Word Processor With This Report – not used to much nowadays but this option allows you to load the created report within the internal Datafile Word Processor for editing.

Margins for Word Processor – Left / Right – set the margins to be used if the report is loaded within the Datafile Word Processor.

Automatic Default to Printer Number – set to output the report to a specific printer within the Datafile printer list (use 99 for Disk Spooling). Leave at 0 to offer the current default / last used print device.

Output Numbers with Starting Negative – when creating CSV files it can be desirable to output any negative numbers with the ‘-‘ sign at the start of the value rather than the end. Set if required.

Tools / Hard Copy Definition

This option outputs a report containing the printed text and the control blocks. It is similar to the Print Preview and Print options available off the File pull-down menu.

Tools / Hide (Show) Navigator

This option disables / enables the Navigator pop-up which allows you to scroll around the report page using the mouse rather than the keyboard.

Save and Exit

Once the user-defined report design is complete you can use the Save and Exit options on the ‘File’ pull-down menu to exit from the report designer.

Article ID: 1223
Created On: Thu, Jun 28, 2012 at 4:52 PM
Last Updated On: Tue, Jun 20, 2023 at 5:27 PM