Function SQLExp

SQLExp is the 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;

Simple Examples

Let us first look at two simple examples which can be used on every report. First expression returns address of subject named 'Factory'

[SQLexp('select acAddress from tHE_SetSubj where acSubject = ''Factory''')]

Second  example returns name of our company. It uses join of two tables

[SQLexp('select S.acSubject from tHE_SetSubj S, tPA_SysParam P where S.acSubject = P.acOurCompany')]

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

You are collecting sales orders 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 the 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. because it is written as expression in text object.

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

Explanation

acConsignee is the field in thedataset qReportIzpis which is used to generate a report.

Function dlQuotedStr (registered for report by Datalab) converts string to quoted version.

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

'select acAddress from tHE_SetSubj where acSubject  = '''Customer&Co.''' '

and thus the query that is executed is:

select acAddress from tHE_SetSubj where acSubject  = 'Customer&Co.''

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.

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 from the report of an already existing report for an invoice. Add a new text object and write the following 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 the following 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 is in this case V instead of 4.

An Example of Calling the Stored Procedure

Function parameter can also be the stored procedure

Following example returns place of our company using stored procedure  fHE_GetOurCompanyCity

[SQLExp('select dbo.fHE_GetOurCompanyCity()]