Datalab functions examples

An example of using a function dlGetSQLString

 
The most common example of using a function is to display the entire SELECT statement that is specified for the selected dataset when the report is opened. Most often, a SELECT statement appears, which belongs to a band called Detail:

begin
   ShowMessage(dlGetSQLString(Detail.Dataset));
end.

 An example of using a function dlStringReplace

On the 10A report (Goods | Invoices Issued Reports menu) add a band with the name Detail, 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', '')]
 

An example of using a function GetOurCompany

Name of our company: [GetOurCompany]
 

An example of using a function GetSubject

[GetSubject(<qSubjIzpis."acSubject">, <qSubjIzpis."acName">)]
[GetSubject(<qSubjIzpis."acSubject">, '']

 

An example of using a function

This chapter presents examples of using aggregate functions, an example of using GetForValue, SQLEXP, and IIF. Examples of using functions specifically written for PANTHEON and not included in the standard FastReport are explained separately in the Datalab Functions category.

An example of using the IIF function

The next example of using the IIF function is on the 22G report, which can be found in the program in the Goods | VAT menu only for Croatian legislation. The term first displays the name of the subject. If a VAT number is also entered, it will also print it in the new line. For the transition to the new line, the character sequence LF (No. 13) and CR (No. 10) was used.

[IIF(<qReportIzpis."acCode"> = '', <qReportIzpis."acSubject">,
<qReportIzpis."acSubject"> + Chr(13) + Chr(10)+ <qReportIzpis."acCode">)]

If nesting the IIF function, it quickly becomes opaque (non-transparent), so it's better to replace it with the if statement in the code. A code equivalent to the above expression is

procedure Memo19OnBeforePrint(Sender: TfrxComponent);
begin
if (<qReportIzpis."acCode"> = '' then
  Memo19.Text := <qReportIzpis."acSubject">
else
  Memo4.Text := <qReportIzpis."acSubject"> + Chr(13) + Chr(10)+ <qReportIzpis."acCode">;
end;
 

An example of using the GetFormValue function

In case of using the GetFormValue function, write down the term that returns the warehouse on the invoices issued thet is located in Goods | Invoices issued report | Invoices issued/Profit margin report. The name of the window is PrometObracunIzdRac, the name of the input field in which we select the warehouse is fSkladisce.

[GetFormValue('PrometObracunIzdRac.fSkladisce')]

The names of some document windows are composed of a base and a suffix representing the type of document. If we want to print the referent on the issue, we write down the term

[GetFormValue('PrometIzdDob3000.fReferentZap')]

It will only be printed for the document type 3000, but not for all others. In order to print for all document types, we have to write it down like this

[GetFormValue('PrometIzdDob'+<tbLookPoslDog."acDocType">+'.fReferentZap')]

Kjer dataseta tbLookPoslDog ni na voljo, lahko dobimo naziv okna iz številke dokumenta. Na naročilih kupcev zapišemo takole

Where tbLookPoslDog datase is not available, we can get the name of the window from the document number. On sale orders we write down:

[IIF(Copy(<qReportIzpisD."acKeyView">,Pos('-',
<qReportIzpisD."acKeyView">)+4,1) ='-',
GetFormValue('Naroc'+Copy(<qReportIzpisD."acKeyView">,Pos('-',
<qReportIzpisD."acKeyView">)+1,3)+'0.fReferentZap'),
GetFormValue('Naroc'+Copy(<qReportIzpisD."acKeyView">,Pos('-',
<qReportIzpisD."acKeyView">)+1,4)+'.fReferentZap'))]

An example of using the GetFormValue with Date function

The GetFormValue function returns the date in the yyyy/mm/dd format, but usually we want it in the dd.mm.yyyy or d.m.yyyy format.

The best example is when there are no data that you need in the dataset, there are notifications of outstanding commitments that are in the program in the Financials | Reports | Outstanding items.

The following expression returns the date of the state in the yyyy/mm/dd format:

[GetFormValue('GKOdprtePostavke.fDatumStanja')]

The term also has a disadvantage that if the date is not entered, it will print 1900/01/01.
We must check whether the date is entered before printing with the IIF function:

[IIF(GetFormValue('GKOdprtePostavke.fDatumStanja')= '1900/01/01','',
GetFormValue('GKOdprtePostavke.fDatumStanja'))]

If we want to print the date in the format dd.mm.yyyy, we need to change the order of the numbers in the report:

[IIF(GetFormValue('GKOdprtePostavke.fDatumStanja') ='1900/01/01','',
Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),9,2)+'.'+
Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),6,2)+'.'+
Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),1,4))]

The date of the situation in the d.m.yyyy format is obtained by turning the day of the month and month into a number and then back to the text:

[IIF(GetFormValue('GKOdprtePostavke.fDatumStanja') ='1900/01/01','',
IntToStr(StrToInt(Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),9,2)))
+'.'+IntToStr(StrToInt(Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),6,2)))
+'.'+Copy(GetFormValue('GKOdprtePostavke.fDatumStanja'),1,4))] 

An example of using GetFormValue function - example of non-text object

The GetFormValue function can also be used for objects that do not represent a text. In this case, the most important feature of this facility is returned. Returned value is usually not suitable for direct printing, but we can use it at the report itself. For example, Text is displayed in the window at this value.

As an example, we take a group of radio buttons called grpGroupBy on a new account card. The name of the window is GLAccountCard. For this object type, the function returns the ItemIndex property that determines which button is selected. In our example, this feature determines how data is to be aggregated and the bands on the report are hidden. The value returned by the function is written to the nGroupBy variable and then, depending on its value, we hide the bands on the report, In the SubjektFooterOnBeforePrint procedure.

  var nGroupBy: Integer;
  ...
  procedure SubjektFooterOnBeforePrint(Sender: TfrxComponent);
  begin
    if nGroupBy <= 1 then
      SubjektFooter.Visible := False;
  end;
  ...
  nGroupBy := GetFormValue('GLAccountCard.grpGroupBy');



A few more complexed examples are on the 04C report, which is in the program in the menu Goods | SAD/Intrastat/Excise | Create SAD - Import and on the 3AR report of the Employee Files. In the program, we get to it by navigating to the Employee Files in the Personnel menu, click on the Prints button.

An example of using SQLExp function - date fields

On the copy of the 241 that is in the program in the Goods | Issue | Document (eg. 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">) + '''')]

An example of using SQLExp function - numbers

In this case, we want to use report 156 that is in the program menu Goods | Material Movements that shows the quantity in the alternative measurement unit (EM2).

Since we do not have a factor for the conversion of the quantity in the primary measurement unit (EM) into EM2, it must be obtained 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 = '''+ <qReportIzpis."acIdent">+'''')]

The above expression returns the number as text. Now we want to print the quantity printed in EM2, 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 = '''+ <qReportIzpis."acIdent"> +''''), 1))]
 

An example of using SQLExp function - reading data from a window in the program

The following example can be done on a copy of the 16B report, which is in the program menu Goods | Invoices Issued Reports | Invoices Issued/Profit Margin Report. If a 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 fSkladisce. The term that returns the text in this object is already known:

[GetFormValue('PrometObracunIzdRac.fSkladisce')]

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.fSkladisce') = '',
SQLExp('select SUM (anStock) from tHE_Stock where acIdent = ''' +
<qReportIzpis."acIdent"> + ''''),
SQLExp('select anStock from tHE_Stock where acIdent = ''' +
<qReportIzpis."acIdent"> + ''' and acWarehouse = ''' +
GetFormValue('PrometObracunIzdRac.fSkladisce') + ''''))]

An example of using SQLExp function with complex select statement

A special treatment deserves the expression on the 3AR report that displays whether the worker is disabled or not.

This information is not directly written in the database, but it contains the disability category in the 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 recorded in the adCreationDate field 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 ''DA'' from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''' and adCreatonDate = (select max(adCreatonDate) from tHR_PrsnHealth where acWorker = ''' + <qryDelavec."acWorker"> + ''' and acDisabilityCode > ''0'')') = 'DA','DA','NE')]

Example of using SQLExp function - grouping by field that is not in the dataset

It is very unusual and useful to use the SQLExp function for sorting and grouping by field that is not in the dataset.

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

The property of the GroupHeader condition is set to the value

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

We replace the inscription which describes the information that we group, and for the data itself, we enter the same as above, but let's put it all together in square brackets (as shown in the figure). To sort in the Reports register, write:

(select G.acIssuer from tHE_Move G where G.acKey = P.acKey), G.acKey