Reporting rules

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

Variables you can include in your SQL code:

(used in the where command)

: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
:param2	4th parameter from running accounts
:search	search for forms that have search checked
:orderby	is added instead of the order by command for sorting
:loydoc	points document number
:loypro	points item code
:loypay	redemption item code
:orderdoc	abbreviation of the document that appears in related
:orderdid	document line id based on abbreviation of the document
–nodecimals–	unlimited decimals
–combo–	drop down list

Fields in SQL code

(used in the select command)

doc	the sales line number “Aa” (create a button that opens sales on the corresponding document)
cus	the customer line number “Aa” (create a button that opens the corresponding customer)
pro	the product line number “Aa” (create a button that opens the corresponding item)
prd	the product line number “Aa” (create a button that opens the documents of the corresponding item)
ksq	its result is used in the update script (creating a button that opens an editor for data processing)
eml	e-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	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:

If there is an update query as the first, then it is executed before the user’s selection. If it is set second, then it is executed 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