In xentral, custom reports can be created using SQL statements and exported as a PDF/CSV or sent via FTP and email.
Reports can be edited via App Center → Reports (Legacy).
Note
This is a legacy module, which is not supported or further developed by xentral anymore. In order to allow our customers to continue to work with the existing functionality, it is still available in the xentral App Store.
The "Settings" tab is where the required information for the report is entered.
In the fields, enter the following:
-
Name → The name of the report, can be freely chosen
-
Project → Project of the report can be specified optionally
-
Description → Optional description to display the report
-
Variables → Optional definition of variables, which are then replaced in the SQL statement are replaced accordingly
-
Structure → SQL statement, which defines the structure of the report
-
Column name → Name of the columns in which the data appears (e.g.. Date, customer, amount)
-
Column width → Optionally, the column width can be specified in millimeters, the total width is 190mm
-
Column alignment → The alignment of the text can be specified in all fields of the column, R (right-aligned), L (left-aligned), or C (centered), optionally
-
Internal remark → An internal remark can be optionally created, this is not visible in the report
It is possible to declare variables in reports and use them in the SQL - statement.
The corresponding syntax then looks like this, the semicolon must be inserted at the end: {DATUMVON} = 2018-10-01; In the report, the variables will be inserted.
Created reports can be transferred via FTP as well as sent at regular intervals and sent by e-mail at specified times.
The function must be activated and all fields must be filled:
-
Email recipient → Email address to which the report should be sent
-
Email Subject → Subject that the email should have
-
Time → Time at which the email should be sent
-
Filename → Name of the file that the report should output. This must contain the extension of the corresponding file type, e.g. .csv or .txt
For the sending of the e-mails to work, the process starter "Reports FTP transfers" must be active and configured correctly. The settings could look like the following:
-
Description → "Reports FTP transfer" must be entered as the name
-
Type → Select "periodic" as the type from the drop-down menu
-
weekday → Select "Every day" from the drop-down menu
-
Start time → The default start time must be left as it is
-
Last Run → The specified end time is to be left in place
-
Period → 1 in min. is to be specified as the period
-
Type → Select "Cronjob" from the drop-down menu
-
Parameter → "berichte_ftp_uebertragen" must be entered as parameter
-
Active → Ticking activates the process starter
SELECT document number, date, name, customer number, country, ustid, SUM(should) as shouldFROM invoiceWHERE country != '{LAND}' AND country IN ({EULAENDER}) AND status != 'created' AND ustid != <em> AND date >= '{DATUMVON}' AND date <= '{DATUMBIS}'GROUP BY ustid</em>
Variables: {country} = DE;{DATEFROM} = 2019-01-01;{DATEBIS} = 2019-12-31; {EULAENDER} = 'AT','BE','BG','CY','CZ','DE','DK','EE','ES','FI','FR','GB','GR','HR','HU','IE','IT','LT','LU','LV','MT','NL','PL','PT','RO','SE','SI','SK';
Column name: Invoice;Date;Customer;Kdnr;Country;USTID;Amount.
Spaltenbreite: 20;25;65;15;10;30;25
Column orientation: L;L;L;L;L
Note
Should there be a comma instead of a period for numbers. (bsp: 13,10 instead of 13.10), the structure must be changed accordingly: FORMAT(shall,2,'en_DE').
SELECT delivery_note_item.number,delivery_note_item.description,delivery_note.country,round(order_item.price,2),(order_item.price * delivery_note_item.quantity),delivery_note.order,DATE_FORMAT(delivery_note.date,'%d.%m.%Y'),item.device,delivery_note_item.quantityFROM delivery_note_itemLEFT JOIN delivery_note ON delivery_note. id=delivery_note_item.delivery_noteLEFT JOIN article ON article.id=delivery_note_item.articleLEFT JOIN order_item ON order_item.id=delivery_note_item.order_item_idWHERE article.device=1 AND delivery_note.country!='DE'AND delivery_note.date >='2016-01-01' AND delivery_note.date < '2016-07-31'
Column names: item number;item description;country;AB price;AB;date;device;quantity.
Spaltenbreite: 20;50;10;20;20;20;20;20
Column orientation: L;L;L;L;L;L
The SQL statement is used to pull the following information from the delivery note:
-
Delivery address of the customer
-
All customers who ordered a specific product (item ID)
-
Delivery bills over a specified date range
SELECT l.document_id,l.date,l.name,l.contact,l.department,l.subdepartment,l.address_address,l.country,l.street,l.zip,l.city, a.name_en,a.number FROM delivery_note l LEFT JOIN delivery_note_item lp ON lp. delivery_note=l.id LEFT JOIN article a ON a.id=lp.article WHERE lp.article=212 AND l.date>='2017-08-01' AND l.date <='2017-12-31' ANDl.status='shipped'
SELECT a.number,a.name_en,TRIM(IFNULL((SELECT SUM(lpi.quantity) FROM stock_item_content lpi WHERE lpi.item=a.id),0)-IFNULL((SELECT SUM(ap. quantity) FROM order_item ap LEFT JOIN order on ON on.id=ap.order WHERE ap.item=a.id AND (on.status='released' OR on.status='shipped')),0))+0 FROM item a WHERE a.deleted!=1
SELECT article.number, article.name_en, article_foreign_numbers.number as foreign_number, article_foreign_numbers.description FROM article_foreign_numbers LEFT JOIN article ON article.id=article_foreign_numbers.article
SELECT MAX(date), name, customer number FROM order WHERE status!='created' AND date >='2018-01-01' AND date<'2018-04-01' GROUP by address
IDSELECT MAX(a.date), a.name, a.customer number , p.abbreviation FROM order a LEFT JOIN project p ON p.id=a.project WHERE a.status!='created' AND a.date >='2018-01-01' AND a.project='1' AND a.date<'2018-04-01' GROUP by a.address