Configuration - User Defined Report Set-up


Datafile Software

Configuration - User Defined Report Set-up

If a UDR is to update a ledger it must be configured in a particular way. The document needs to be defined so that the ‘Invoice’ template falls within a $SUBHEADER and $SUBTOTAL control blocks. Both control blocks should be set to break on the same item, typically an account code or order reference. A typical structure would be:

$HEADER

$SUBHEADER, STARTINV, BREAK (ACCOUNT)

Here you would have general account information – name, address, etc.

$RECORD

Here you would have individual invoice lines / details – qty, price, nominal, etc.

$SUBTOTAL, ENDINV, BREAK (ACCOUNT), PAGEAFTER

Here you would have final totals plus more account information

$END

To update the ledger you copy details from within the report structure into accumulator items, these are then cross-referenced for update to the ledger. Whenever a data item is printed on the report you have the option to save it to an accumulator.

Accumulators – creating accumulators is similar to creating extra database data items.Within the UDR set-up, select ‘Parameters’ and then ‘Accumulators’, you will then be presented with a blank database structure for you to add your own items.

Within the accumulators you need to configure selected items for update to the transaction file. For each invoice you need a minimum of

DATE

ACCOUNT

REF

DESCRIPTION

GOODS AMOUNT

TAX CODE

TAX AMOUNT

In addition you may want to take over Nominal, Analysis or Stock Codes or unit costs and quantities. You need to create a data item for each of these values within the accumulators.

If creating multiple analysis lines, i.e. each invoice will have more than one database record processed for it, then special consideration is required for the Goods Amount and Tax Amount accumulators. For multiple analysis lines you will a need an accumulator for the $RECORD and an accumulator for printing the document totals within the $SUBTOTAL.

A typical structure would be:

Name Type Description

LINE-GOODS K, L Holds the goods amount for current $RECORD

TAX-RATE K, L Holds the tax rate for the tax code on this $RECORD

LINE-VAT * Calculated item giving the VAT amount using a calculation of goods amount and tax-rate. Calculation would be ‘Line Goods x Tax Rate / 100’. To use a value of ‘100’ in an accumulator calculation you would reference a constant item, i.e. C1, and you would set this accumulator constant as 100

LINE-TOTAL * calculate item adding Line Goods and Line VAT

TOTAL-GOODS K, L Holds the total goods amount of the invoice

TOTAL-VAT K, L Holds the total VAT amount of the invoice

TOTAL K, L Holds the total value of the invoice

INV-DATE D Holds the date of the invoice

INV-REF X (6) Holds the reference number

INV-DESC X (30) Holds the document description

ACCOUNT X (10) Holds the customer/supplier account code

NOMINAL X (16) Holds the nominal analysis code

ANALYSIS N Holds the customer/supplier analysis code

STOCK X (16) Holds the stock code (if any)

UNIT-COST K, L Holds the unit cost (if any)

QUANTITY K, L, M Holds the issue quantity (if any)

Several of these items would be set to copy to the accumulators from the $SUBHEADER – these would include INV-DATE, INV-REF, INV-DESC and ACCOUNT. All others (except document totals) would come from accumulators copied from the $RECORD.To update the ‘Total’ items you need to print the accumulator line values and set to copy to the total accumulator items.

Tips on Copying to Accumulators

If you need to copy an item to the accumulators, but don’t want to print on the document, set to print after column 80/132 (whatever the maximum width of the document).

As each $RECORD is processed then items are copied to the accumulator and an analysis line is generated. When the next $RECORD is processed text items will be overwritten with the new copy but numeric items will be added (hence accumulators). To avoid this create a control block $RECORD, NOPRINT before the existing $RECORD and print the line value accumulated and in the print parameters set them to be zeroed.

If Premier, you can create a calculation on the input database to derive the tax rate from the tax code using special calculation item ‘!C11’. On other product levels you would need to create a tax lookup database containing the tax code.

We would recommend getting the document print configured (including copy to the accumulators) before proceeding to define the link to sales/purchase ledgers.

When saving document only (and not generating transactions) you still need accumulators for the account, date, reference, and description. If the document has a value then you need to update into Amount and Tax Amount.


Custom Fields

Article ID: 1256
Created On: Fri, Jun 29, 2012 at 12:06 PM
Last Updated On: Tue, Jun 20, 2023 at 5:15 PM

Online URL: https://kb.datafile.co.uk/article/configuration-user-defined-report-set-up-1256.html