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.
An Example of a Collective Invoice Created from the Collective Invoice of a Consignment Report
In the chapter on SQLEXP - Reporting Serial Numbers in a Collective Invoice, we saw an example of reporting linked serial numbers when a collective invoice is created directly from issue documents. We stressed that the report of the collective invoice or the overview of serial numbers does not give information about the received or issued serial numbers of items from the collective invoices because these serial numbers were already received or issued with an issue document.
But you can use the SQLEXP function to report a list of received or issued serial numbers (see Using SQLEXP Stamements in Reports).
It's a little different when you report werial numbers in a consignment supplier's collective invoice which you've created using a different collective invoice (see Consignment Reports) and not directly from the documents where the serial numbers were recorded.
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('exec dl_HE_PrtSerialNoKomOdjava ''' + tbIzpisPrometPoz.Kljuc + ''' , ' + tbIzpisPrometPoz.Poz)
This statement refers to the stored procedure dl_HE_PrtSerialNoKomOdjava, which is why it is somewhat shorter than the other examples for reporting serial numbers. In this example, a predefined stored procedure is useful. A different, user stored procedure could also be used.
In the chapter on SQLEXP - Reporting Serial Numbers in a Collective Invoice, we saw an example of reporting linked serial numbers when a collective invoice is created directly from issue documents. We stressed that the report of the collective invoice or the overview of serial numbers does not give information about the received or issued serial numbers of items from the collective invoices because these serial numbers were already received or issued with an issue document.
But you can use the SQLEXP function to report a list of received or issued serial numbers (see Using SQLEXP Stamements in Reports).
It's a little different when you report werial numbers in a consignment supplier's collective invoice which you've created using a different collective invoice (see Consignment Reports) and not directly from the documents where the serial numbers were recorded.
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('exec dl_HE_PrtSerialNoKomOdjava ''' + tbIzpisPrometPoz.Kljuc + ''' , ' + tbIzpisPrometPoz.Poz)
This statement refers to the stored procedure dl_HE_PrtSerialNoKomOdjava, which is why it is somewhat shorter than the other examples for reporting serial numbers. In this example, a predefined stored procedure is useful. A different, user stored procedure could also be used.