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.

Example of a collective issue:

Let's look at an example where the collective invoice (receipt or issue) ahs been created from internal documents (receipts or issues) in which you've received or issued goods tracked by serial numbers.

The report of a collective invoice or an overview of serial numbers will not produce information about the received or issued serial numbers of items from the collective invoice, because these serial numbers were received or issued through internal documents.

But you can report a list of received or 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 4 (see Linking Documents).

Reports of this kind are used to report serial numbers in a collective invoice that has been created directly from internal documents (collective receiving, collective issue).

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 = ''4'' and V.Kljuc = ''' + 

tbIzpisPrometPoz.Kljuc + ''' and V.Poz = ' + tbIzpisPrometPoz.Poz + ' 

group by S.SerialNo')

The same sentence can be used to report serial numbers in a collective receiving document, because it is linked with the same type of link (type 4).

Using a similar procedure, but a different sentence, you can report serial numbers in collective invoices when creating consignment reports (for an example see SQLEXP - Reporting Serial Numbers in a Collective Invoice for a Consignment Report) and from a collective consignment invoice (for an example, see SQLEXP - Reporting Serial Numbers in a Consignment Partner's Collective Invoice).