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>')

Adding SQLExp onto report

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

Save changes.

Report of the customer's address from the sales order

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

Comment:

acConsignee

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

After replacing dlQuotedStr(<qReportIzpisD."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 and postal zone 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(<qReportIzpisD."acConsignee">) + '')]