 |
SQLEXP is a function
used by the program to generate reports
which makes it possible to report information not available in the original
RecordSet of a report. This way, you can include information from other
tables and registers in reports for individual user reports. |
Syntax:
SqlExp('<SQL stavek>')
|
Because the names of RecordSets and fields in the RecordSets
could differ in each report, you must check the name of the RecordSets
and fields for each concrete report. |
An Example of a Collective Document Created with Internal Documents:
Let's look at an example where the collective invoice (receipt or issue)
hass been created from internal issue documents in which you've issued consignment
goods tracked by serial numbers.
The report of a collective invoice or an overview of serial numbers will
not produce information about the issued serial numbers of items from the collective
invoice, because these serial numbers were issued through an issue document
for the customer.
But you can report a list of issued serial numbers! To do this, use the SQLEXP
function for reports (see Using SQLEXP Statements
in Reports). Serial numbers will be directly reported using the link table,
where the link between the internal document and the collective invoice is entered
with type V (see Linking Documents).
Reports of this kind are used to report serial numbers in a
consignment report.
1. Create a new record from the report of an already existing report for
an invoice. Add a new Field with Statement by clicking the
icon:
2. In the Statement field, enter the SQL sentence:
SQLExp('select S.SerialNo + '' ('' + LTrim(Str(Sum(S.Kolicina), 10, 2)) + '')'' as SN
from SerialNoP S, Veza V
where V.VKljuc = S.Kljuc and V.VPoz = S.Poz and V.Tip = ''V'' and V.Kljuc = ''' +
tbIzpisPrometPoz.Kljuc + ''' and V.Poz = ' + tbIzpisPrometPoz.Poz + '
group by S.SerialNo')
Report:
1. Form
2. Example of a Report
From this example, you can see that serial numbers are reported one on top
of another. The issued quantity for an individual serial number appears in parentheses.
