Function SQLExp

SQLExp is a function that allows you to access data that is otherwise not available in the orignal dataset of a report.

This way you can include data from other tables into user-defined reports.

Syntax

SQLExp('<SQL statement>')

Example of the Use on the Report 103 Order report - detailed

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.

You are collecting sales order and once a week you deliver these orders to consignees. In sales orders report you can create a report of non-dispatched orders with entered Delivery field (delivery method) Delivery - with own truck.

In the RecordSet of sales order report are not provided the customer's address or their contact person. All these data are available in the Subjects register, as for the the Subjects ID, it is already present in the RecordSet of the 103 Order report - detailed report.

All criteria were met to use the SQLEXP sentence in the report. Let's look!

Put text object onto report. Click on the object to open text editor. Write expression in the editor.

Report of the customer's address from the sales order

[SQLExp('select acAddress from tHE_SetSubj where acSubject  = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '')]

Comment:

acConsignee

is a field from dataset qReportIzpis which is used to generate a report.

After replacing dlQuotedStr(<qReportIzpis."acConsignee">) , the SQL command is:

'select acAddress from tHE_SetSubj acName = 'Customer & Co.''

and thus the query that is executed is:

select acAddress from tHE_SetSubj acName = 'Customer & Co.'.

Report of the customer's place from the sales order

[SQLExp('select P.acName from tHE_SetSubj S left join tHE_SetPostCode P on S.acPost = P.acPost where acSubject  = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '')]

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 text object and write into it the SQL sentence:

[SQLExp('select S.acSerialNo + '' ('' + LTrim(Str(Sum(S.anQty), 10, 2)) + '')'' as SN from tHE_MoveItemSerialNo S, tHE_LinkMoveItemMoveItem V where V.acLnkKey = S.acKey and V.anLnkNo = S.anNo and V.acType = ''4'' and V.acKey = ''' + <tbIzpisPrometPoz."acKey"> + ''' and V.anNo = ' + VarToStr(<tbIzpisPrometPoz."anNo">) + ' group by S.acSerialNo')]

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 text object and write into it the SQL sentence:

[SQLExp('select S.acSerialNo + '' ('' + LTrim(Str(Sum(S.anQty), 10, 2)) + '')'' as SN from tHE_MoveItemSerialNo S, tHE_LinkMoveItemMoveItem V where V.acLnkKey = S.acKey and V.anLnkNo = S.anNo and V.acType = ''V'' and V.acKey = ''' + <tbIzpisPrometPoz."acKey"> + ''' and V.anNo = ' + VarToStr(<tbIzpisPrometPoz."anNo">) + ' group by S.acSerialNo')]

A difference from the previous example is only in Link type which is V instead of 4.