Function SQL Exp

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">) + '')]