Reporting rules

Reports are easily created using the Firebird SQL language and some EMDI commands embedded in comments.

Variables you can include in your SQL code:

The following parameters are used within the WHERE clause, which is responsible for limiting the records returned:

:apo	Date from
:ews	Date until
Reference description	1st parameter from running accounts
Reference code	2nd parameter from running accounts
:param1	3rd parameter from running accounts. Between quotes if it is string
:param2	4th parameter from running accounts. Between quotes if it is string
:search	Search for forms that have search checked. Between quotes if it is string
:orderby	Is added instead of the order by command for sorting
:loydoc	Points document number
:loypro	Points item code
:loypay	Redemption item code
:orderdoc	First abbreviation from the check_copy_balance field (Sales → Settings → «Related copy»)
:orderdid	Comma list of document type ids (eidhpar.Aa) from the check_copy_balance field — for IN (:orderdid)
:relatedabbreviation	CASE with %ΔΠΑΡ%, %ΔΑΠΟ%, etc. per type from the check_copy_balance field; substituted in Sxetika LIKE (pending-quantity reports)
:dm	Decimal digits for item’s price
:ds	Decimal digits for summaries
:df	Decimal digits for percentage
:dp	Decimal digits for quantity
–nodecimals–	Unlimited decimals
–combo–	Drop down list

Related copy

In Settings, Purchases/Sales, field «Related copy», enter the abbreviations of the document types you track, separated by commas (e.g. ΔΠΑΡ,ΔΑΠΟ). When the report SQL contains one of :orderdoc, :orderdid or :relatedabbreviation, EMDI replaces them automatically based on:

:orderdoc	the first abbreviation in the list. Older reports (e.g. «02.Orders») use it in the Sxetika LIKE as '%:orderdoc%'
:orderdid	the Aa ids (eidhpar) of all types in the list; use IN (:orderdid) in the WHERE clause
:relatedabbreviation	a CASE expression per type (e.g. %ΔΠΑΡ% or %ΔΑΠΟ%); required when you have more than one type, so each document searches only its own abbreviation in Sxetika

Example for the Sxetika LIKE (multiple types):

For pending-quantity reports (e.g. «02.Pending items for issue») use all three placeholders. Only lines with balance > 0 from orders or delivery notes without prices are shown; delivery notes with prices displayed are excluded.

For example with :search you can add a WHERE condition for multi-field product search:

 

Fields in SQL code

The following aliases are used within the SELECT statement, which is used to return data to the caller:

doc	The sales line number “Aa” (create a button that opens the corresponding document in sales)
cus	The customer line number “Aa” (create a button that opens the corresponding customer in customer/supplier list)
pro	The product line number “Aa” (create a button that opens the corresponding item in customers/suppliers)
prd	The product line number “Aa” (create a button that opens the documents of the corresponding item in products/services)
ksq	Its result is used in the update script (creating a button that opens an editor for data processing)
eml	Ε-mail to send|Name|Value|e-invoice link|Voucher number (create button that sends e-mail)
sms	Mobile to send|Name|Value|E-invoice link|Voucher number (create button that sends sms)
img	Show photo
NEXT VALUE FOR “gen_counter”	The line number

 

Update script

:ksq	Value executed in the code with quotes
:ksp	Value executed in the code without quotes

 

It is possible to run up to 2 scripts separated by a semicolon “;”.

In example 1, we can edit and save the fields of the selected customer. Importantly, a key field named “Aa” must always be included.

Example 1:

Example 2 consists of 2 scripts. First a new line is created, if it doesn’t already exist, in the table “kinhseis” and then we choose to show from the same table 2 fields, “Order stage” and “Notes”. We also use the key field as in this case “Aa” (“kinhseis”.”Grammh”).

Example 2:

In example 3, we edit and save the “custom1” field of the selected document. Here the key field is “pvlhseis”.”Aa” and “Status” is the title we set for the field in the edit window.

Example 3:

If an update query is set first, it runs before the user selection. If it is set second, it runs after the user selection.

Open external application

We use the “editing field” to define a list of options in a field we want.

In “field options” we write, separated by commas, the options we want the list to have. It is not mandatory.

We use the “read field” to get the contents of a field of our choice. It is not mandatory.

Using the “link” field we can call an external application.

When the link calls a web address we use the variables:

#VL1	processing field
#VL2	reading field

For example:

In case we call a local program we don’t need to use variables because they are defined as 1st and 2nd parameters respectively.

For example:

 

EMDI commands

The first line can include some EMDI commands as a SQL comment and is automatically generated every time you make a change.

It contains all the settings we make for a report.

For example, if you want to create a graph, then you fill in where xx the field for the x-axis, yy the field for the y-axis and xxlabel the field for the title of the x-axis.

For line numbering we use the following variable after select:

 

Colors

To give color we can define a variable that takes some values (up to 9) separated by a comma, e.g.:

1,22,2,45,3,90,3,111,9

The color that the line gets respectively is:

yellow,red,green,yellow,red,green,yellow,red,green

 

Expanding list

It is possible to activate a list of options. Each option inserts a code into the –combo– variable, which reads it from a corresponding file with a psql extension.

In the following example we create 2 options, DELIVERY and RESERVED

When the user selects DELIVERY then the content of the sql1.psql file is inserted in place of the –combo– variable.

Example psql file

or any sql code we want to combine with our main code.

We save the psql files in the same folder as the reports.

 

Rights

We can define which users, based on permissions, can run a report. In the “Rights” field we write the title of rights belonging to the users who will have access to the report.
For many titles we separate with a comma.
Blank field for no restriction.

 

Additional functions in SQL (functions)

Get field from delimited text:

Result: test2

Convert text to number with decimals

Result: 152,33

Result: 152,33

Convert text to number without decimals

Result: <null>

Result: 152

 

* Note: To run multiple queries, put this at the beginning of the code: