Reports give you the ability to analyze your data in Xentral. Whether it's data-driven management of your items and sales channels, a quick overview of your customer revenues, or optimizing your inventory performance—reports are the perfect starting point for uncovering potentials and problem areas in your business.
For newcomers to data analysis, the intuitive no-code interface and selection of pre-defined report templates make getting started easy. These templates, developed from the practices of key business models and expert experience, offer insights that are ready to use immediately. Experienced users can take advantage of the advanced features of the SQL editor and benefit from the modern database technology AWS Redshift, which enables fast and timely data updates.
The reports on gross revenue (order value) allow you to analyze revenues generated before deductions and assess the performance of your sales channels and products. With inventory performance, you gain insights into stock values and turnover rates to optimize the efficiency of your inventory. The reports on net sales and gross profit help you determine profitability after deducting returns and discounts. Return, cancellation, and discount rates provide insights into potential weaknesses in your sales processes. Additionally, master data management ensures high data quality and supports key operational processes.
Our new reporting platform is built on the latest database technology, enabling faster and more stable reports. With a fully documented new data model, you gain access to detailed information on all tables and columns. The data is consistently translated into English and resembles the original data model to facilitate your orientation. We continuously expand and improve the model, for example, by integrating smart tables that consolidate invoices, credits, and their positions.
Note
We utilize AWS Redshift, which is optimized for high speed and efficiency with large datasets. Unlike MySQL, which operates on a row-based system, Redshift uses columnar storage. This enables faster queries and analyses, making it ideal for data-intensive tasks and quick reporting. Please note that while the Redshift dialect is very similar to that of MySQL, there are some differences in the syntax.
Note
Data is updated standardly once per night. This is cost-effective and aligns with our usage analysis, as over 90% of customers do not query their reports multiple times a day. If you require a higher update frequency (e.g., every 3 hours or even nearly in real-time), you can request this through an individual contract for your instance. Please contact our Customer Development Team at accountmanagement@xentral.com for this purpose.
Reports can contain sensitive information. For this reason, reports are by default only accessible to admins. Users without admin rights need the permission User → Analytics_report_builder to access reports. Currently, this permission grants access to all reports. We are working on implementing restrictions that will be included in upcoming updates.
For a smooth start without technical expertise, it is recommended to begin with predefined report templates. These templates already cover a wide range of general analyses, from management to master data maintenance.
For individual queries that go beyond the content of report templates, you can create your own reports. Depending on your technical knowledge and the complexity of the task, we offer a simple, click-based No-Code Editor as well as a much more flexible SQL Editor.
In the Basic version (enabled by default for all plans), you can view all report templates, review their content, and configure your own reports. The retrieval volume is unlimited but should not regularly exceed a typical usage level. The only limitation is in the view of the results—here, the toggle is set to "Preview" and cannot be switched to "Full View." As a result, only five lines of the result are displayed, and the export functions are disabled.
An exception to this are reports in the "Master Data Maintenance and Support for Operational Processes" collection. These reports are not intended for classic reporting and are therefore fully available to you, including CSV file downloads. If you believe any important reports are missing from this collection, you are welcome to submit a ticket. We will then review your request to determine if the report can be added later.
In the Premium version, you receive the full range of the add-on's features, including full view of the results and extended export functions. The preview mode remains available for you to freely develop and test reports.
Please note that in the Premium version, the toggle starts in preview mode and will remain in full view after the first switch, even when reopening reports, until you manually switch it back to preview mode.
Well-maintained master data is essential for meaningful data analysis and data-driven management. Since not all Xentral modules fully support optimal master data maintenance, we offer free tools to help identify and resolve issues within your master data. An example of this is locating and tracking items without EAN codes.
Additionally, our reports can support operational processes that are not yet fully covered by existing Xentral modules. We have reviewed the most common individual reports and made them available in a generic form. This collection serves as an extension of the current export center, for example, for exporting inventory data for your own pick & pack processes.
Xentral reports for master data maintenance and support of operational processes are free of charge with regular use (several times a day, but not on a minute-by-minute basis).
|
Gross Revenue |
Analyze your gross revenue based on orders (excluding created orders) before returns, cancellations, and discounts. These reports help you understand incoming orders and identify the performance of individual factors. Segment your gross revenue by dimensions such as time, product, customer, sales channel, and more. |
|
Net Revenue & Gross Profit |
Evaluate your net revenue based on invoices and credits (excluding created ones) after returns, cancellations, and discounts. These reports assist you in analyzing your revenue and gross profit, helping you to identify optimization opportunities. |
|
Return, Discount, and Cancellation Rates |
Analyze the rates for returns, discounts, and cancellations to identify potential weaknesses in your process. You have access to the same dimensions as in the analysis of gross and net revenue. |
|
Inventory and Performance |
Monitor your inventory and performance to identify potential shortages or overstock early on. In this collection, you will find reports on inventory levels and value, slow and fast movers analysis, inventory turnover, and sell-through rates. |
Sometimes predefined analyses are not sufficient, and a custom solution is needed. For this, you can create custom reports and save them in a collection.
Report collections work similarly to folders and help you stay organized by logically grouping your reports. This way, you can quickly find relevant reports on finances, sales, or inventory. Creating an archive collection for old or outdated reports can also be useful.
Editing and Deleting Collections: You can edit or delete your custom collections using the three-dot menu next to the collection title. Collections created by Xentral are write-protected and cannot be edited.
Creating a New Report: You can create a new report either by clicking the button in an empty collection or by using the "Create Report" button in the upper right corner. Existing reports can also be deleted through the three-dot menu next to their title.
Tip
Interactive Documentation: Next to the editor, you'll find our interactive documentation. It allows you to search for specific content in both German and English and provides enhanced insights into data points through new brief descriptions for tables and columns. To aid in navigation, the old labels of source data for each table and column are also displayed.
The No-Code Editor guides you through the process of creating your query step by step, without requiring in-depth programming knowledge. However, a thorough understanding of the English data model and the ability to join tables will quickly reflect in your editing efficiency and the results.
To begin, select the dataset that best matches your query. For instance, choose "Sales Orders" when analyzing order entries. If your focus is on net revenue or gross profit, start with "Invoices."
Using the drop-down icon next to your selected dataset, you can choose the columns to include in your result table.
You can add additional datasets to expand your query. You have two options:
Note
-
Left Join: Extends the existing dataset (often the best starting point).
-
Inner Join: Supplements and restricts the existing dataset.
Choose the table you want to add and specify the parameter to which it should be linked. Typically, these are ID fields with similar names to the initially selected table, also known as Primary and Foreign Keys.
If these terms still seem too cryptic, you'll find a helpful example below for better understanding.
Imagine you have two Excel spreadsheets: one with the names of all family members and another with birthday gifts.
Note
LEFT JOIN
A SQL LEFT JOIN combines both tables, showing which family members received a gift and who did not. All family members will be displayed, even if they did not receive a gift.
In Excel, you would use the VLOOKUP function to check the gift list to see which gift each family member received. The LEFT JOIN works similarly but with a key difference: it also displays those family members who did not receive a gift. The "ON" parameter in a LEFT JOIN is like the lookup criterion in VLOOKUP, connecting the lists based on a common attribute, such as the name.
Example:
-
Table 1 (Family Members): Names
-
Table 2 (Gifts): Names and Gift
The LEFT JOIN connects the tables "ON" (based on) the shared name, so you can see who received a gift and who did 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 functions similarly to a VLOOKUP in Excel but only shows the family members who actually received a gift.
Example:
-
Table 1 (Family Members): Names
-
Table 2 (Gifts): Names and Gift
In an INNER JOIN, only the names of family members who are also in the gift list are shown. This means you'll see only the family members who received a gift. The "ON" parameter in the INNER JOIN specifies that the lists should be joined based on a common attribute, such as the name.
Result of the INNER JOIN:
-
If Child A has a gift, it will be shown.
-
If Child B has a gift, it will be shown.
-
If Child C does not have a gift, it will not be shown.
Only those who received a gift are visible in the result.
Tip
These explanations will help you understand the differences between LEFT JOIN and INNER JOIN and how they are used in SQL queries.
Once you have created your dataset, you can filter it using rules:
Select a Column: Choose the column you want to filter:
-
Text Fields: Contains/does not contain a specific value.
-
Numeric Values: Greater than/less than a specific number.
-
Date Fields: Before/after/on a specific date.
-
Boolean Fields: True/False.
Combine multiple rules using AND/OR operators, and remove them if needed using the X button. Note that only fields selected in the first step can be filtered.
Aggregate specific columns using functions such as Sum, Average, Minimum, Maximum, or Count. The editor only allows valid operations based on the column type. For example, you cannot calculate a sum for a text column like “City.”
Sort your results by a specific column in either ascending or descending order. Use the limit parameter to restrict the number of displayed rows, for example, to view only the top 100 results.
Check the completeness of your selection in the editor. Once your selection is valid, click on the Preview button to test your query. The validity indicator next to the title will always show whether your report is ready to run. If everything is correct, save your report by giving it a meaningful name and storing it in the desired collection for future access.
Save the report by clicking the Save button in the upper right corner. Make sure to save your changes before leaving the page to avoid losing any data.
In addition to the No-Code Editor, Xentral offers an SQL Editor for advanced users.
This editor supports:
-
Auto-Complete: Suggestions while typing.
-
Syntax Highlighting: Color-coded highlighting of keywords.
-
Error Display: Shows error messages in the result field.
Expand the editor input field if needed using the six dots below the input area, and access the documentation via the question mark next to the editor. Collapse the editor using the arrows if necessary. Remember to save your changes to avoid data loss.
Edit the title of your report and the selected collection directly in the editor using the pencil icon in the upper right corner.
Insert Dynamic Parameters / Filters
Dynamic filters make your reports more flexible and allow users to modify results without needing programming skills. To create filters, you use parameters in your SQL statements. You can configure these parameters by clicking the {x} button next to the input field. This allows you to add and manage parameters directly within the SQL editor, enhancing the interactivity and adaptability of your report.
In the sidebar, you can now create a new parameter. When the query is executed, the parameter will automatically be replaced with the set filter value.
A parameter has the following properties:
-
Value: The value is what you use in your SQL statement enclosed in curly braces. For example, {name_filter}.
-
Label: The label describes the name under which your new filter will be displayed to users. For example, "Name Filter." In the current implementation, the label is automatically generated by converting hyphens into spaces.
-
Type: The data type of the parameter. You can choose between Text, Number, Date, and SQL Statement. Note that values for the "SQL Statement" type must be wrapped in single quotation marks (’yxz’). This allows for more complex filter expressions, such as WHERE product_name LIKE {{VAR}} with filter.
-
Default Value: To ensure your SQL statement functions even without setting filter values, you need to specify a default value. Your parameter will then automatically be replaced with the default value when executing the query if no other filter value is provided.
Data can be exported from the Reports module in various ways:
-
Permanent-Link (URL):
The URL is a permanent link that allows you to trigger the download of a .csv file with your report results by accessing it through a browser or third-party tool. You can create and manage your individual report URL in the "Export Settings" tab under "Permalink."
-
E-Mail (coming soon):
When setting up email delivery, you can specify the recipient address, the frequency of the email, and the file format in which the report will be saved. At the scheduled time, you will receive an email with a download link for the report.
-
FTP/SFTP/FTPS:
With the new reporting module, you also get a completely revamped FTP service. It is based on the new Connect technology and can be set up in two steps.
-
First, configure your server (FTP/SFTP/etc.) under Settings → Administration → Data Exchange → FTP.
-
In the second step, for each report, you can select an FTP configuration and define a time interval by clicking on the "Export Settings" tab within the report and expanding the FTP export options.
-
-
File download:
You can download your current, saved report as a .csv file via the Export button in the top right corner. The creation of your report happens asynchronously in the background and may take a few seconds. The download link to the file can be found under the "Export" tab in each report view.
-
Reporting API:
We are actively developing new API endpoints for programmatic retrieval and modification of reports and configurations. Documentation for these can be found in our OpenAPI specs. The following endpoints are currently available:
-
Query endpoint to create, execute and list the history of executed SQL queries
https://developer.xentral.com/v24.31/reference/analyticsquery
-
Report endpoint to create, alter, delete reports, retrieve / change export configurations
https://developer.xentral.com/v24.31/reference/analyticsreportlist
-
Report usage endpoint to show the history of chargeable requests made within the given timeframe
https://developer.xentral.com/v24.31/reference/analyticsreportusageget
Tip
To retrieve the results (and not just the configuration) of reports via the API, there are several methods. For example, you can query the SQL statement of an existing report using GET /report/{id} and then execute it in a second step using /query. Alternatively, you can generate a CSV file using POST /report/{id}/export and retrieve the result using GET /report/{id}/export. If the file is successfully created, GET will return both the status and the file.
You can find the report ID in the URL, for example, 1362 for ...xentral.biz/app/analytics-platform?activeReport=1362&editorModeActive=false&tab=report
-
GSheet Integration:
-
Step 1: Download the code file for upload into Google Scripts (Status: In Progress).
-
Step 2: Follow this guide to configure your connection to Xentral (Status: Preliminary).
-
-
Excel Integration:
-
Step 1: Download the code file for upload to Google Scripts (Status: In Progress).
-
Step 2: Follow this guide to configure your connection to Xentral (Status: In Progress).
-