Reports give you the opportunity to evaluate your data in Xentral. Whether it is data-driven management of your products and sales channels, a quick overview of your customer turnovers, or the optimization of your warehouse performance - reports are the perfect starting point to uncover potential and problem areas in your business.
For beginners in data analysis, the intuitive no-code interface and the selection of predefined report templates make it easy to get started. These templates, developed from the practice of the most important business models and the experience of experts, provide ready-to-use insights. Experienced users can use the advanced functions of the SQL editor and benefit from the modern database technology AWS Redshift, which enables fast and timely data updates.
Tip
Video tutorial: Your quick start into reporting
Do you want to get an overview of reporting in Xentral within a few minutes? Then our video tutorial is just right for you. Watch it now!
Xentral provides you with a variety of reports to evaluate the performance of your sales channels and products. With the warehouse performance, you get insights into inventory values and inventory turnovers to optimize the efficiency of your warehouse. Special reports on net sales and gross profit help you determine the profitability after deducting returns and discounts. Return, cancellation, and discount rates give you information about potential weak points in your sales processes. In addition, master data maintenance ensures high data quality and supports important operational processes.
The reports platform is based on state-of-the-art database technology and thus enables fast, stable, and reliable evaluations. The underlying data model is fully documented and provides detailed information on all tables and columns. All identifiers and content are consistently translated into English, which ensures uniform access and clear orientation. The model is continuously expanded and optimized, for example through intelligent tables that summarize invoices, credit notes, and their positions in a structured manner.
Note
We use AWS Redshift, which is optimized for high speed and efficiency with large amounts of data. In contrast to MySQL, which works row-based, Redshift uses column-based storage. This enables faster queries and analyses, ideal for data-intensive tasks and fast reporting. Please note that while the Redshift dialect is very similar to that of MySQL, there are differences in the syntax here and there.
Note
By default, the data is updated once over night. This makes sense both for cost reasons and based on our usage analysis, as over 90% of customers do not query their reports several times a day. Should you require a higher update frequency (e.g., every 3 hours or even in near real-time), you can request this via an individual contract for your instance. Contact us via email at accountmanagement@xentral.com.
Reports can contain sensitive information. For this reason, reports are only enabled for admins by default. If a user does not have admin rights, special permissions are required. You can find detailed instructions in the chapter Assigning user rights for reporting.
For an easy start without technical knowledge, it is recommended to start with predefined report templates. These templates already cover many general analyses, from Management to Master data maintenance.
For individual questions that go beyond the contents of the report templates, you can create your own reports instead. Depending on technical knowledge and complexity, we offer a simple, click-based No-code editor as well as a significantly more flexible SQL editor.
Before you work with the prefabricated report templates in the collections provided by Xentral or create your own report templates and use reporting productively, you should consider which of your employees are allowed to access which report templates and report exports. For example, it may make sense to grant warehouse staff targeted access to the reports in the [04] Warehouse performance collection or to ensure that your accounting department has access to the [02] Net sales & gross profit collection in order to gain insight into the latest business data and thus be able to make strategic decisions.
Proceed as follows to first create an access profile and then release either entire collections or individual reports for specific users.
-
Open the menu Reporting > Reports.
-
Open the Access rights tab.
-
Click on Access profiles.
-
Click on + Create access profile.
-
Assign a Name and a Description for the access profile. In our example, we create an access profile for the warehouse staff.
-
Under Enable read access, select the report collections that should be visible within the profile. Click on the desired report collection to exclude individual reports of the collection from the release. In our example, we exclude the report Inventory difference by warehouse. Thus, this report will not be displayed to the users of the access profile later.
-
Click on Create access profile.
In the second step, you assign exactly those users of your Xentral instance to your access profile who should receive read access to the previously selected collections and reports. Proceed as follows to do so.
-
Open the menu Reporting > Reports.
-
Open the Access rights tab.
-
Click on User access rights.
-
Click on Add user access.
-
In the User field, select the Xentral user who should receive access to an access profile.
-
In the Access profile field, select the access profile to which the user should receive access.
-
In our example, we grant the employee Klaus access to the previously created access profile called Warehouse insights.
-
Click on Create user access.
As a Xentral admin, you can grant all other Xentral users without administrator rights full write and read access to reporting in the Reporting > Reports menu. Proceed as follow to do so.
-
Use the Smart Search to open the User module.
-
Open the user to whom you want to grant rights for reporting by clicking on the pencil icon on the right side.
-
Open the Rights tab.
-
Navigate to the entry Analytics_platform.
-
Activate the all right to grant the user unrestricted write and read access to reporting.
-
Activate the restricted right to grant the user only read access to reporting.
-
Click on Save at the top right.
With the automatically provided basic version, you can generally view all report templates, check content, and configure your own reports in your instance. The retrieval volume is unlimited, but should not regularly exceed a normal scope. However, the view of the results is limited to a preview and cannot be switched to the full view. Therefore, only 5 rows of the result are displayed and the export functions are not available.
Exceptions to this are reports in the Master data maintenance and support of operational processes collection. These are not used for classic reporting and are therefore fully available to you, including the download of CSV files. Should you believe that important reports are missing here, you can report this to us via ticket. We will then check whether your report request can be added subsequently.
In the premium version, however, you receive the full scope of reporting and can make use of a full view of the results as well as advanced export functions. The preview mode is still available to you for developing and testing reports free of charge.
Note that the report view in the premium version always starts in preview mode and remains in full view (even when repeatedly opening reports in full view) until you click Preview again
Well-maintained master data is the prerequisite for meaningful data analyses and data-driven management. Since not all Xentral modules yet support ideal master data maintenance, we offer free ways to identify and fix problems in the master data. An example of this is finding and subsequent maintenance of products without EAN codes.
Furthermore, our reports can support operational processes that are not yet optimally covered by existing Xentral modules. For this purpose, we have scanned the most common individual reports and provided them in generic form. This collection serves as an extension of the current Export Center, e.g. for the export of inventory levels for your own logistics processes.
Xentral reports for the maintenance of master data and for the support of operational processes are free of charge with normal use (several times a day, but not every minute).
In the Reporting > Reports menu, predefined report templates are available, which are divided into different categories. These templates allow you to quickly and easily start evaluating the most important business data. The following table contains an overview of the available collections and a brief explanation of their content and special areas of application.
Collection |
Description |
|---|---|
|
[01] Gross sales (order value) |
Analyze your gross sales based on sales orders (excluding created sales orders) before returns, cancellations, and discounts. These reports help you to understand the order intake and identify the performance of individual levers. Slice your gross sales by dimensions such as time, product, customer, sales channel, and more. |
|
[02] Net sales & gross profit |
Evaluate your net sales based on invoices and credit notes (excl. created) after returns, cancellations, and discounts. These reports support you in the analysis of your turnover and gross profit and help you to identify optimization potentials. |
|
[03] Return/cancellation/discount rates |
Analyze the rates for returns, discounts, and cancellations to identify possible weak points in your process. The same dimensions are available to you as for the analysis of gross and net sales. |
|
[04] Warehouse performance |
Monitor your stock and performance to identify potential bottlenecks or overstocks at an early stage. In this collection, you will find, for example, reports on stocks and value, slow-mover/fast-mover analysis, inventory turnover rates, and sell-through rates. |
|
[05] B2B sales |
Get insight into strategically important B2B turnover metrics. In this collection, you will find reports such as the completed net sales for B2B orders or the sales forecast. |
|
[06] Master data maintenance and support of operational processes |
Access the most important metrics of your operational processes and master data. Here you will find a variety of reports on products, stocks, offers, liabilities, shipped packages, and much more. |
|
[07] Legacy templates |
Use a variety of report templates from the discontinued Xentral module Reports (Legacy). |
|
[08] FTP backup and BI reports |
Here you will find templates for creating an FTP backup as well as for providing report data in external tools. Detailed information is available the article Exporting data to business intelligence tools. NoteThis collection is only visible in your instance if you have booked the Basic or Premium package for reporting. |
|
[09] Legacy statistics |
Here you will find report templates for data from the legacy modules Management Board, Revenue Statistics and Sales Cockpit in Xentral. NoteThis collection is only visible in your instance if you have booked the Basic or Premium package for reporting. |
|
[10] Reorder proposal (experimental) |
Get more precise insights into product requirements and recommendations for the exact point in time when you should reorder goods. NoteThis collection is only visible in your instance if you have booked the Basic or Premium package for reporting. |
|
[11] Real-time reports |
In this collection, you will currently find the report template Batch tracking with best-before date. It provides information on which best-before dates and batches are contained in which delivery note, including information on the customer and the order date. NoteThis collection is only visible in your instance if you have booked the Basic or Premium package for reporting. |
Sometimes predefined analyses are not enough and an individual solution is required. This is exactly why you can create individual reports and save them in a collection. In the following step-by-step instructions, we roughly describe how you create a new, individual report. Further information on the creation of collections as well as the use of the SQL editor and the no-code editor can be found in the following chapters.
-
Open the menu Reporting > Reports.
-
Click on Create report at the top right.
-
Select whether you want to create the report using the No-code editor or the SQL editor.
-
In the Title field, enter a name for your report.
-
In the Description field, enter a description for your report. This description helps you to easily recognize the content and purpose of the report later.
-
In the Collection field, select which collection your report should be added to.
-
Click on Create report.
Report collections work similarly to folders and help you to keep an overview by grouping reports logically. This way you can quickly find relevant reports on finance, sales, or warehouse. Creating an archive collection for old, outdated reports can also be useful.
-
Editing and deleting collection: You can edit or delete your own created collections via the 3-dot menu next to the collection title.
-
The collections created by Xentral are write-protected and cannot be edited.
-
Creating a new report: You can create a new report either by clicking on Create report in an empty collection or via the Create report button at the top right in the Reporting > Reports menu.
-
Existing reports can be deleted within a collection via the 3-dot menu next to their title.
Tip
Right next to the editor, you will find our interactive documentation. It enables you to search specifically for content in German and English and offers you more precise insights into the data points through new short descriptions for tables / columns. For better orientation, the old designation of the source data is also displayed for each table and column.
The no-code editor guides you step-by-step through the creation of your query without the need for deep programming knowledge. An intensive examination of the English data model as well as merging tables (joins) helps you, however, to increase the processing efficiency and to achieve a better quality of the result.
To start, you select the data set that best fits your question. For example, you select sales orders when it comes to order intake. For questions about net sales or gross profit, you start with invoices.
Using the dropdown menu next to your selected data set, you can select the columns that your result table should contain.
You can add additional data sets to expand your query. You can choose between two options:
Note
-
Left join: Extension of the existing data set (often the best starting point).
-
Inner join: Supplement and restriction of the existing data set.
Select the table you want to add and determine the parameter where it should be attached. Usually, these are ID fields with a similar name as the first selected table, also known as primary and foreign keys.
If these terms still seem too cryptic to you, you will find a clear example as an aid below.
Imagine you have two Excel tables: one with the names of all family members and one with the birthday gifts.
Note
LEFT JOIN
A SQL LEFT JOIN combines both tables so that you can see which family member has received a gift and who has not. All family members are displayed, even if they have not received a gift.
In Excel, you would use the (VLOOKUP) function to look up in the gift list which gift each family member received. The LEFT JOIN works similarly, but with a crucial difference: It also shows those family members who did not get a gift. The "ON" parameter in the LEFT JOIN corresponds to the search criterion in (VLOOKUP) and connects the lists based on a common feature, such as the name.
Example:
-
Table 1 (Family members): Names
-
Table 2 (Gifts): Name and gift
The LEFT JOIN connects the tables "ON" (based on) the common name, so that you see who has a gift and who does not.
Result of the LEFT JOIN:
-
Child A has a car
-
Child B has a doll
-
Child B has a ball
-
Child C has no gift
Note
INNER JOIN
An INNER JOIN also works like a VLOOKUP in Excel, but only shows the family members who actually received a gift.
Example:
-
Table 1 (Family members): Names
-
Table 2 (Gifts): Name and gift
With an INNER JOIN, only the names of the family members who are also in the gift list are displayed. This means you only see the family members who have received a gift. The "ON" parameter in the INNER JOIN states that the lists should be connected based on a common feature, such as the name.
Result of the INNER JOIN:
-
If child A has a gift, it is displayed.
-
If child B has a gift, it is displayed.
-
If child C has no gift, it is not displayed.
In the result, only those who received a gift are visible.
Tip
These explanations help you to understand the differences between LEFT JOIN and INNER JOIN and how they are used in SQL queries.
After you have created your data set, you can filter it using rules:
Select a column and set comparison parameters, e.g.:
-
Text fields: contains/does not contain specific value.
-
Numerical values: greater/less than a specific number.
-
Date fields: before/after/at a specific date.
-
Boolean fields: true/false.
Combine any number of rules with AND/OR, and delete them if necessary with the X. Note that only fields can be filtered that you selected in the first step.
Summarize specific columns by using functions such as sum, average, minimum, maximum, or count. The editor only allows valid operations based on the column type. You cannot, for example, form a sum over a text column such as "City".
Sort your results by a specific column, ascending or descending. Limit the number of displayed rows via the limit parameter, e.g. to see only the first 100 results.
Check the completeness of your selection in the editor. If the selection is valid, click on Preview to test your query. The validity indicator next to the title shows you at any time whether your report can already be executed.
Save the report by clicking on Save at the top right. Make sure to save changes before leaving the page to avoid data loss.
In addition to the no-code editor, Xentral offers a SQL editor for advanced users.
This editor supports:
-
Auto-complete: Suggestions while typing.
-
Syntax-highlighting: Colored highlighting of keywords.
-
Error display: Shows error messages in the result field.
Expand the editor input field if necessary via the six dots below the input field and call up the documentation via the question mark next to the editor. Collapse the editor if necessary via the two arrows pointing towards each other. Save your changes to avoid data loss.
Edit the title of your report as well as the selected collection directly in the editor via the pencil icon in the upper right corner.
Inserting dynamic parameters / filters
Filters make your report dynamic and give your users the opportunity to change results without programming knowledge. You create filters very easily via parameters in your SQL statement. To do this, click on {x} to the right of the input field to configure parameters.
In the sidebar, you can now create a new parameter.
The parameter is automatically replaced by the set filter value when a query is executed. A parameter has the following properties:
-
Value: The value is what you insert in your SQL statement in curly brackets. For example {name_filter}
-
Label: The label describes the name under which your new filter is displayed for users. For example Name Filter. In the current implementation, the label is automatically generated by converting the hyphen into a space.
-
Type: The data type of the parameter. You can choose between Text, Number, Date, and SQL statement. Note that you must wrap values in single quotation marks ('yxz') for the type "SQL statement". This allows you more complex filter expressions, such as e.g. WHERE product_name LIKE {{VAR}} with filter
-
Default value: To ensure that your SQL statement also works without setting filter values, you must specify a default value. Your parameter is then automatically replaced by the default value when the query is executed, should no filter value be specified.
Depending on the requirement case and technical expertise, you can export data from the reports module in various ways. You can choose between different export formats as well as different endpoints in the Xentral API to export your report data from Xentral.
Click on the links below to learn more about the various export options and to receive further information.
Depending on how you want to further process the data contained in the report export outside of Xentral, you can make some settings for the number format of the exported data in advance. These settings then apply every time you export data via download.
Proceed as follows to carry out format settings for the data export.
-
Open the menu Reporting > Reports.
-
Open the Settings tab.
-
In the Number formatting drop-down menu, select whether the export should contain unformatted raw data without thousands separators (Option Unformatted) or with thousands separators and automatic date formatting (Option App language formatting).
-
In the Export format drop-down menu, select the desired export format. The options CSV, JSON, Excel, Text, Parquet, and PDF are available.
-
Click on Save.
An export function is available to you at the top right for every report. This allows you to manually download the most current report in various file formats. Note that an export is only possible once the report has already been saved. The creation of your report happens asynchronously in the background and can take a few seconds. Subsequently, you will find the download link to the file in the Exports tab of the respective report view.
A permalink is a permanent link through which you trigger the download of a CSV file with your report results by calling it up in the browser or a third-party tool. You create and manage your individual report URL in the Export configurations > Permalink (URL) tab of a report.
Proceed as follows to create a permalink that you can then store in an external tool of your choice.
-
Open the menu Reporting > Reports.
-
Click on the entry [8] FTP backup and BI reports.
-
Open the desired report with a click on the report name.
-
Open the Export configurations tab.
-
Click on Permalink (URL).
-
Make the desired settings for the permalink.
-
Click on Create permalink.
The permalink is displayed. You can now copy this and store it in an external BI tool of your choice.
Important
If you want to store your report data on your own FTP server at regular time intervals, the following requirements must be met:
-
You have all credentials for your FTP server as well as the necessary knowledge of its functioning.
-
You have created the connection to your FTP server in the Settings > Administration > Data exchange > FTP menu, i.e. entered your credentials.
Proceed as follows to store report data on your FTP server.
-
Open the menu Reporting > Reports.
-
Open the report whose data you want to store on an FTP server.
-
Open the Export configurations tab.
-
Click on FTP export.
-
Carry out the detailed settings for the FTP export.
Important
The fields marked with asterisks (*) are mandatory fields!
-
Click on Create export.
When setting up the email dispatch, you can specify both the time interval in which the email is sent and the file format in which the report is saved. You will then receive an email with a download link to the data of the selected report at the corresponding time.
Proceed as follows to set up the email export for a report.
-
Open the menu Reporting > Reports.
-
Open the report whose data you want to export via email.
-
Open the Export configurations tab.
-
Click on Email export.
-
Make the settings. The following table contains more detailed information on the available settings.
-
Click on Create schedule.
Setting |
Explanation |
|---|---|
|
Sender |
Select the sender address for sending the email. The System default option ensures that the email address stored in the Settings > Basic settings > Email > Email accounts menu is used as the sender address. |
|
Email address |
Enter the email address here to which the email with the download link should be sent. If you do not enter an email address, the email will be sent to the address stored in the Settings > General settings > Email > Email accounts menu. |
|
File format |
Select the file format in which the report results should be provided for download. You can choose between the options CSV, *JSON, Excel, Text, Parquet, and PDF. |
|
Export frequency |
Determine how often a current report file should be generated and the email with the corresponding download link should be sent. First select whether the email export should be carried out Monthly, Weekly, or Daily. In the second step, determine on which date, on which day, or at what time the export should be automatically initiated. |
If you have the necessary technical know-how, you can retrieve and create a variety of report data directly via the Xentral API.
Tip
Video tutorial: Your quick start into the Xentral API
Do you want to get an overview of the possibilities of the Xentral API within a few minutes? Then our video tutorial is just right for you. Watch it now!
Xentral provides, among others, the following API endpoints for the export of report data:
-
Query endpoint, to create and execute a list of performed SQL queries: Click here to go to the developer documentation
-
Query endpoint, to create and execute the history of performed SQL queries: Click here to go to the developer documentation
-
Report endpoint, to create, change, and delete reports as well as retrieve and adjust export configurations: Click here to go to the developer documentation
-
Usage endpoint, to display the history of paid report queries within a specific period: Click here to go to the developer documentation
A complete list of all endpoints for reporting can be found here in the developer documentation and on all other subpages of the Analytics topic section.
Tip
To retrieve the results (and not just the configuration) of reports via the API, there are several ways. You can, for example, query the SQL statement of a created report via GET /report/{id} and then execute it in a second step via /query. Alternatively, you generate a CSV file via POST /report/{id}/export and query the result via GET /report/{id}/export. If the file was successfully created, GET returns the file in addition to the status.
The report ID can be found in the URL, e.g. 1362 for …xentral.biz/app/analytics-platform?activeReport=1362&editorModeActive=false&tab=report
Alternatively, you can sync and process your report data from Xentral in Google Sheets (also Google Tables).
Click on the following link to be guided step by step through the connection to Google Sheets: