 |
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 stavek>')
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!
1. Create a new record from the 103 Order report -
detailed report for an invoice. Add a new Field with
Statement by clicking the
icon:
2. In the Statement field, enter
the SQL sentence:
Report of the customer's address from the sales order
SQLEXP('select Naslov from Subjekt where Naziv = ''' + qReportIzpis.Narocnik + '''')
Comment:
qReportIzpis.Narocnik is a field from the recordset qReportIzpis, which is used to
generate a report.
After replacing qReportIzpis.Narocnik, the SQL command is:
'select Naslov from Subjekt where Naziv = ''Neki kupec'''
and thus the query that is executed is:
select Naslov from Subjekt where Naziv = 'Neki kupec'.
Report of the customer's place and postal zone from the sales order
SQLEXP('select K.Naziv from Subjekt S left join Posta K on S.Posta = K.Posta
where S.Naziv = ''' + qReportIzpis.Narocnik + '''')
Report of the customer's contact from the sales order
SQLEXP('select LTrim(RTrim(KontOseba)) + '' '' +
Cast(case when (Tel is null) then '' '' else '' Tel: '' +
Tel end as Char(30)) as Ko
from KontOs where Subjekt = ''' + qReportIzpis.Narocnik + '''
order by Poz')
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:
Primer izpisa obračuna naročil kupcev iz tako oblikovanega izpisa:
