Funkcije

Example with Function dlStringReplace

On the report '10A - Material Movements Report - Detailed' that is in the program menu 'Goods | Material Movement ' add a "Text" object on the band named 'Detail' and write expression which returns serial numbers display that belong to the selected position and the selected invoice. The SQLExp function returns all records, each in its own row, but we want to display serial numbers in one row, separated by commas.
 
[dlStringReplace(SQLExp('SELECT acSerialNo, FROM tHE_MoveItemSerialNo WHERE acKey = ''' + <qReportIzpis."acKey"> + ''' AND acIdent = ''' + <qReportIzpis."acIdent"> + '''  GROUP BY acSerialNo UNION ALL SELECT acSerialNo FROM tHE_OrderItemSerialNo WHERE acKey = ''' + <qReportIzpis."acKey"> + ''' AND acIdent = ''' + <qReportIzpis."acIdent"> + ''' GROUP BY acSerialNo ORDER BY acSerialNo'), #13#10, ', ', 'rfReplaceAll', '')]
 

Example with Date Fields

On the clone of the '241 - Invoice' report that is in the program menu 'Goods | Issue | Document (e.g. 3000 - Sale)' add the balance of the issue invoice recipient to the 2200 account on the day of the invoice. It is important that the date is formatted in the form yyyymmdd.

[SQLExp('SELECT SUM(anValDebit-anValCredit) from tHE_AcctTransItem where acAcct = 2200 and acSubject=''' + <qReportIzpisD."acReceiver"> + ''' and adDateDoc <= ''' + FormatDateTime('yyyymmdd',<qReportIzpisD."adDateInv">) + '''')]

Example with Number

In this case we use the report '156 -Material Movements Report - Summary by Items' that is in the program menu 'Goods | Material Movements' that shows the quantity in the alternative measurement unit.

Since we do not have factor for the conversion of the quantity in the primary measurement unit (U/M) into U/M 2, we must obtained them from the Units of Measurement register using the SQLExp function.

We do this with the following expression:

[SQLExp('SELECT anUMToUM2 FROM tHE_SetItem WHERE acIdent = ' + dlQuotedStr(<qReportIzpis."acIdent">)+'')]

The above expression returns number as text. Now we want to print the quantity printed in U/M2, so we have to convert the value obtained with the dlStrToFloatDef function into a number. Then we divide the amount into the EM with the factor obtained, and using the dlDiv function instead of dividing it from the zero, rather than the ordinary division. Finally, we get:

[dlDiv(<qReportIzpis."anBegState">, dlStrToFloatDef(SQLExp('SELECT anUMToUM2 FROM tHE_SetItem WHERE acIdent =  ' + dlQuotedStr(<qReportIzpis."acIdent">) +''), 1))]

Example with Reading Data from PANTHEON Window

We will do the following example can be done on a clone of the report '16B - Invoices Issued Report - by Suppliers and Items', that is in the program menu 'Goods | Invoices Issued Reports | Invoices Issued/Profit Margin Report'. If warehouse is selected in the window, the expression displays the entire stock of the item, otherwise it only prints the stock in the selected warehouse.

The window in the program from which the report starts is called 'PrometObracunIzdRac', the input field in which we select the warehouse and has the name 'fWarehouse'. The term that returns the text in this object is already known:

[GetFormValue('PrometObracunIzdRac.fWarehouse')]

First we check if warehouse is selected. If not, the first SQLExp expression that returns the entire stock of the selected item in all the warehouses, otherwise the second one returns the stock of the item only in the selected warehouse.

[IIF (GetFormValue('PrometObracunIzdRac.fWarehouse') = '',
SQLExp('select SUM (anStock) from tHE_Stock where acIdent = ' +
dlQuotedStr(<qReportIzpis."acIdent">) + ''),
SQLExp('select anStock from tHE_Stock where acIdent = ' +
dlQuotedStr(<qReportIzpis."acIdent">) + ' and acWarehouse = ''' +
GetFormValue('PrometObracunIzdRac.fSkladisce') + ''''))]

Example with Complex Select Statement

A special treatment deserves the expression on the report '3AR - Employee Record'  that displays whether the worker is disabled or not. It is in the program menu 'Personnel | Employee Files'. Then click on the 'Print' icon.

This information is not directly written in the dataset, but it contains the disability category in the field "acDisabilityCode" field in the table "tHR_PrsnHealth". Since the disability category of an individual worker can change, we must get the one that was last entered. The date of the change of disability is stored in the field "adCreationDate" in the same table. The following term gives you the last date when the invalidity category for the selected worker has changed.

[SQLExp('select max(adCreatonDate) from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + '''')]

Then, with the term below, we get the category of disabillity of the worker

[SQLExp('select acDisabilityCode from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''' and adCreatonDate = (select max(adCreatonDate) from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''')')]
 

If in the above term we replace the "acDisabilityCode" field with 'YES', the expression returns 'YES' if the worker is disabled, otherwise it returns an empty string. Since, in the case when a worker is not disabled, the expression returns 'NO', we give the term to conditional sentence IIF and finally get

[IIF(SQLExp('select ''YES'' from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''' and adCreatonDate = (select max(adCreatonDate) from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''' and acDisabilityCode > ''0'')') = 'YES','YES','NO')]

Sorting and Grouping by the Field that is not in the Dataset

Very unusual and useful usage of function SQLExp is  for sorting and grouping by field that is not in the dataset.

As a basis, we will take a report '164 -  Invoices Issued Report - Detailed'  that is in the program menu 'Goods | Invoices Issued Reports | Invoices Issued/Profit Margin Report'. First we create a clone on which we will operate. The data will be grouped by the invoice issuer and sorted by the issuer and the document number.

We set property "Condition" of "GroupHeader band" to;

SQLExp('select G.acIssuer from tHE_Move G where G.acKey = ''' + <qReportIzpis."acKey"> + '''')

Then replace the description which describes the information that we group, and for the data itself. We enter the same as above, but let's put it in square brackets (as shown in the picture). To change sorting change the 'Sort By' field in the reports register:
(select G.acIssuer from tHE_Move G where G.acKey = P.acKey), G.acKey