SQLEXP - Customer Address in the Sales Order Report

Example of the Use on the Report.

WARNING SQLEXP  is a function used by the program to generate reports which makes it possible to report information not available in the original RecordSet of a report. This way, you can include information from other tables and registers in reports for individual user reports.

Syntax:

SqlExp('<SQL sentence>')

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

WARNING 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!

1. Create a clone of report 103 Order report - detailed

2.  Add a text object.

3. Open the text editor and enter write the next SQL sentence:

Report of the customer's address from the sales order:

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

Comment:

qReportIzpis.acConsignee is a field from the recordset qReportIzpis, which is used to generate a report.

After replacing qReportIzpis.acConsignee, the SQL command is:

'select acAddress from tHE-Subj where acSubject = ''Some Customer'''

and thus the query that is executed is:

select acAddress from tHE_Subj where acName = 'Some Customer'.

Report of the customer's postal code and place from the subject register

SQLEXP('select P.acPost + '' '' + P.acName from tHE_SetSubj S left join tHE_SetPostCode P on S.acPost = P.acPost 

where S.acSubject = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '')

Report of the customer's contact  from the sales order

SQLExp('select (LTrim(RTrim(C.acName)) + '' '' + LTrim(RTrim(C.acSurname)) + '' '' + Cast(case when (A.acPhone = '''') then null else +  '' Phone: '' + A.acPhone end as Char(30))) as Ko +
from tHE_SetSubjContactAddress A  join tHE_SetSubjContact C on  A.acSubject = C.acSubject and A.anNo = C.anNo where A.acSubject = ' + dlQuotedStr(<qReportIzpis."acConsignee">) + '

 Reports:

Because you would like to sort non-dispatched orders by customers and than by order numbers, you have to select the appropriate report criteria in the Report settings:

Example of printout from of so designed report: