In this article, we show you a quick and easy way to use data available in Xentral for separate data preparation tools, such as Power BI or Google Sheets. To present your data in these external applications in a structured and graphically processed way according to your needs, some preparatory settings are necessary, which are described below.
Important
The methods described below require advanced knowledge of data structures, preparation, and analysis. You should only use them if it is essential that external tools evaluate your data from Xentral. Furthermore, you should have the appropriate expertise on how to handle and store your sensitive business data securely. Be sure to check out the article Overview: Reporting - Controlling, statistics and reports first to see if your requirements can also be met with the Reports module directly in Xentral.Xentral offers you the following options to transfer your data to external tools:
-
Data request via Xentral API: You can retrieve the data via a dedicated API call.
https://{xentralId}.xentral.biz/api/v1/analytics/collection/{id}Tip
You can find more information about this API call in the Xentral API documentation.
-
Saving data on an FTP server: You can connect an FTP server to Xentral so that current data is stored on this server later at fixed intervals.
-
Providing data via permalink: You can create a so-called permalink in Xentral with consistently up-to-date data, which you store in other tools.
-
Transferring data to Google Sheets: Evaluate your report data in Google Sheets by executing a specific script.
Important
All three mentioned procedures consume queries from your monthly quota, which is limited depending on the tariff model (Basic or Premium). If you regularly need complete and extensive data exports, you must book the Premium version of the reporting module!
Specifically for the use of external BI tools, we have created a pre-made collection in Xentral with the most frequently required data. Before creating a data export, take a look at this collection to see which included data is relevant to you.
-
Log in to Xentral.
-
Open the menu Reporting > Reports.
The overview in the Your reports tab opens.
-
Click on the entry [8] FTP backup and BI reports.
The available reports are displayed.
Tip
Can’t find exactly the report you need in the list? Then you can easily configure your own reports using the No-code editor. For advanced users, there is also an SQL editor that you can use to create complex queries. You can find all the important information on creating individual reports in the chapter Creating Custom Reports.
Below is a list of all available report templates in the [8] FTP backup and BI reports collection, including a short description of the data contained.
Report name |
Data |
|---|---|
Addresses |
List of all customer and supplier addresses |
All purchase prices |
All purchase prices with product information |
All sales prices |
All sales prices with product information |
Quotation_Position + Quotations |
Product positions including associated quotations |
Products |
Master data of all products |
Order_Position + Orders |
Order positions and associated orders |
PurchaseOrder_Position + PurchaseOrders |
Purchase order positions and purchase orders with supplier information |
CreditNote_Position + CreditNotes |
Credit notes and their positions |
Categories |
Product categories with hierarchy |
Warehouse_Reservations |
Reservations in the warehouse |
Stock incl. Batch/Best before date |
Current stock including batch and best before date information |
Stock movement incl. Best before date/Batch |
All stock movements with best before date and batch tracking |
Stock values |
Value of goods in stock |
DeliveryNote_Position + DeliveryNote |
Delivery notes and their positions |
Production_Position + Production |
Production positions and associated production orders |
Projects incl. subprojects |
Project master data including subprojects |
Invoice_Position + Invoices |
Invoice positions and associated invoices |
Returns_Position + Returns |
Returns and their positions |
Liability_Positions + Liabilities |
Liabilities and their positions |
Time tracking with project assignment |
Recorded times with reference to projects |
After you have selected the appropriate report, you can proceed to configure the data export.
The easiest and fastest option is usually the creation of a permalink (short for permanent link). Permalinks ensure that the same data source is reliably referenced at all times. You can use this as follows:
-
Open the desired report in Xentral and generate a permalink to this report in the Export configurations tab.
-
You then store the permalink created in this way in external BI tools such as PowerBI.
-
Once a day (overnight), the report data to which the permalink refers is recalculated by Xentral.
-
You can then view the latest business data in your external BI tool.
-
To create a permalink for a report’s data, proceed as follows:
-
Open the menu Reporting > Reports.
-
Click on the entry [8] FTP backup and BI reports.
-
Open the desired report by clicking on the report name.
-
Open the Export configurations tab.
-
Click on Permalink (URL).
-
Carry out the desired settings for the permalink.
-
Click on Create permalink.
The permalink is displayed. You can now copy it and store it in an external BI tool of your choice.
If you want to store your report data on your own FTP server at regular intervals, the following conditions must be met:
-
You have all the access data for your FTP server and the necessary knowledge of how it works
-
You have created the connection to your FTP server in the Settings > Administration > Data exchange > FTP menu, i.e., entered your access data
Proceed as follows to store report data on your FTP server.
-
Open the menu Reporting > Reports.
-
Click on the entry [8] FTP backup and BI reports.
-
Open the desired report by clicking on the report name.
-
Open the Export configurations tab.
-
Click on FTP export.
-
Carry out the detailed settings for the FTP export.
Important
Fields marked with an asterisk (*) are mandatory fields!
-
Click on Create export.
If you do not use a separate BI tool, you can alternatively prepare your report data from Xentral in Google Sheets.
Click on the following link to view step-by-step instructions for creating the connection to Google Sheets: