Data exports and also regular transfer of data to other systems or via e-mail can be realized via individual reports. SQL reports enable the evaluation of all available data in any conceivable form and are a powerful tool for analyzing and providing data for further analysis in business intelligence tools (BI systems).
Starting with version 20.1, many additional report scripts will be available and the module will include more features that will make creating and using report scripts a simple task.
Reports can be shared in a variety of ways:
-
Easy sharing with other users in the reports dashboard
-
Integration in the software as a table in an additional tab (register)
-
Integration in the action menus of the documents as a download
-
Regular sending by e-mail
-
Regular transfer via FTP
-
Provision via URL, even for a limited period of time
-
Release for access via API
Xentral provides a large number of reports as of version 20.1 and these will be further expanded. In the future, most modules will come with a number of reports that can easily be supplemented with your own reports. The exchange of report scripts is possible through report templates - files in which the definitions for reports can be quickly saved and loaded elsewhere.
The database tables are mainly in German language. The naming follows common standards and therefore offers a quick start.
The fastest way to find your way around the database is in the xentral Custom Suite in the register SQL Viewer. There you can have a quick look at all tables of the system. For performance reasons, some tables are limited in the view to columns or rows. All details can be found in the XCS (Xentral Customizing Suite) article.
The figure shows a screenshot from the xentral Customizing Suite with the table adresse_rolle.
Independent of the xentral installation, the own database can be downloaded at any time via the backup tool as a database snapshot and viewed on a local computer. In this way, SQL statements for reports scripts (reports) can also be created and tested. The following procedure is recommended for this purpose: The backup of the xentral database is to be downloaded to the local computer. To do this, use the "Create DB Snapshot" function and follow points 1. and 2. of the Create Database Backup page. The downloaded file can then be loaded e.g. in a XAMPP environment into the database server available there and SQL statements can be tried out. XAMPP is a completely free, easy-to-install Apache distribution that includes MariaDB, PHP and Perl. The XAMPP open source package has been set up for extremely easy installation and use. XAMPP is available for Windows, Linus and MacOS in 15 languages and offers a simple complete installation of a running web server with database server and the necessary tools (such as PHP and phpMyAdmin). In addition, many tutorials and community support are available online for XAMP.
-
auftrag
-
auftrag_position
-
rechnung
-
rechnung_position
-
lieferschein
-
lieferschein_position
-
gutschrift
-
gutschrift_position
The most important tables from the xentral database schema is located under DB-Diagram.io. This structure will be extended to all database tables in the future.
{LAND} = DE; {DATUMVON} = 2019-01-01; {DATUMBIS} = 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';
SELECT belegnr, datum, name, kundennummer, land, ustid, SUM(soll) as soll FROM rechnung WHERE land != '{LAND}' AND land IN ({EULAENDER}) AND status != 'angelegt' AND ustid != '' AND datum >= '{DATUMVON}' AND datum <= '{DATUMBIS}' GROUP BY ustid
Spaltennamen: Rechnung;Datum;Kdnr;Kunden;Land;USTID;Betrag Spaltenbreiten: 20;25;65;10;15;30;25 Column direction: L;L;L;L;L;L;R Note: If a comma should be used for the numbers instead of a point (e.g.: 13.10 instead of 13.10), then the structure would have to be changed accordingly and in line 1 of the SQL statement the word "shall" would have to be replaced e.g. by: FORMAT(should,2,'en_DE') AS amount
SELECT p.abkuerzung, ap.bezeichnung, COUNT(ap.auftrag), FORMAT(SUM(ap.umsatz_netto_gesamt),2,'de_DE'), FORMAT(SUM(ap.umsatz_brutto_gesamt),2,'de_DE') FROM auftrag_position ap LEFT JOIN projekt p ON ap.projekt = p.id LEFT JOIN auftrag au ON ap.auftrag = au.id WHERE au.datum >= '{DATUMVON}' AND au.datum <= '{DATUMBIS}' AND p.abkuerzung = '{PROJEKT}' GROUP BY ap.projekt, ap.artikel
To narrow down the origin of orders in SQL statements, the store field can be evaluated. It contains the id from the table shopexport.
-
shop = 0 means: the order was created manually or imported into the system via document importer, API or the transfer module.
-
shop > 0 specifies the store Id (channel) from the shopexport table All imported orders are logged in the shopimport_auftraege table.