Function SQLExp

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

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

Function header

function SQLExp(sql: String): String;

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

Because the names of datasets and fields could differ in each report, you must check the name of the dataset and fields for each concrete report.

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

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

All criteria were met to use the SQLExp on the report. Let's look!

Put text object on report. Click on the object to open the text editor. Write expression in the editor. You must enclose expression between square brackets.

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

Comment:

acConsignee is the field in 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

SQL sentence is in this case join of two tables

[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 invoice

Let's look at an example where the collective invoice (receipt or issue) has 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.  Serial numbers will be directly reported using the link table, where type of link between the internal document and the collective invoice is 4.

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

Create a new copy 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) has 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. 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.

1. Create a new copy 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 that link type which is in this case V instead of 4.

An Example of Calling the Stored Procedure from SQLExp Function

A argument of function SQLExp you can also use stored procedure

Next example returns Place of our company using stored procedure  .fHE_GetOurCompanyCity

SQLexp('select dbo.fHE_GetOurCompanyCity()')