Caution
This Report Module is replaced trough Reports (new). Status: 07/2024
In NextGen: Reporting > Reports
In Classic Design: Controlling > Reports
Xentral offers a range of default reports you can use to analyze all kind of data within your company, e.g. sales orders created per employee, shipping labels created within a certain period, sales per product, active customer subscriptions, and many more.
Apart from using the existing reports, you can also create your own reports based on your business needs.
Important
Creating your own reports requires some knowledge in SQL. If you do not have any knowledge in SQL, we advise you to contact one of our partners to create the required reports for you.
Reports can be displayed in various places in the software or downloaded. For download the following formats are available to you:
-
CSV
-
PDF
You can then keep working with your downloaded data in third-party systems such as Excel, Power BI or Minubo.
Reports are shown in the form of tiles in the report overview:
The report tile consists of the following elements:
|
Element |
Description |
|
|
|
Click on the star symbol to mark this report as favorite. The star symbol turns yellow. Marking a report as favorite has the following advantages:
|
|
|
|
You can assign your report to a category, e.g. finance & accounting, warehouse & fulfillment, etc. to mark it for a certain function. You can select a category when you create a report or when you edit an existing report. |
|
|
|
Clicking this edit icon, you can edit your report. More info on the fields in edit mode can be found in the section Create and maintain your report |
|
|
|
Clicking this delete icon, you can delete your report. |
|
|
|
Clicking this copy icon, you can create a copy of your report. This is especially useful, if you have different reports with many similar settings. |
|
|
|
When hovering over the information icon, a description of the report is displayed, if one was added. You can add a description when you create a new report or when you edit an existing report. |
|
|
|
Title of the report. |
|
|
|
Clicking the table icon, the report data is displayed in table form in the View tab of the report. |
|
|
|
Clicking the CSV icon you can create your report in CSV format for download. |
|
|
|
Clicking the PDF icon you can create your report in CSV format for download. |
Xentral offers several options to search or filter for a specific report. You can find these above the report tiles on top of the report overview. The following options are available to you:
-
Use the Only own filter if you want to display only those reports you have created. The Xentral default reports will be hidden. The filter is applied automatically, you do not need to click Apply.
-
Use the Favorites only filter to only display reports you have marked as favorite. The filter is applied automatically, you do not need to click Apply.
-
If you only want to display the reports of a certain category, e.g. warehouse & fulfillment, select the relevant category in the Category field. Click Apply to search for the relevant reports.
-
If you want to search for specific terms in the reports title, e.g. accounts, enter the relevant term in the Search field. Click Apply to search for the relevant reports.
You can combine the available filters and search options.
Important
Creating your own reports requires some knowledge in SQL. If you do not have any knowledge in SQL, we advise you to contact one of our partners to create the required reports for you.
If you want to create a new report, there are two options to do so - you can either create a report from scratch or create a copy of a similar report and adjust the relevant settings according to your needs. The latter option is generally the more common option as it does save time and effort.
|
To create a new report from scratch |
To create a report based on an existing report |
|---|---|
The report does not contain any settings at this point. Please follow the instructions below to add the required information. Use the search function to search for your report. |
|
Your report is created and listed in the report overview.
You can now edit your new or existing report. This process contains two steps:
To edit your new or an existing report:
-
Click on
for the report you want to edit. The Details tab of the report is displayed.
-
In the Setting area you can add, edit, or select the following:
-
Name: A name was assigned when the report was created but can be edited it at any time, if required.
-
Category: If required, you can select the category for the report, that is the department or area in which the report is to be created, e.g. accounting, sales, etc.
-
Description: Enter a description for your report. This description is displayed when you hover over the information icon
in the report tile in the report overview.
-
Project: If this report relates to a certain project, you can select the relevant project in this field.
-
Load from file: By clicking Datei auswählen, you can load information from an existing JSON file. Generally you would upload files here you have created from other reports within Xentral using the Definition file button at the bottom of the Details tab.
-
Variables: More information on variables and how to create them can be found here.
-
-
In the Structure area you can add the relevant SQL statement to create your report.
Note
The required database names and tables for your SQL statement are listed in the Database view, which you can find using the Supersearch/ Smartsearch. Furthermore, you can use variables. More on the use of variables in SQL statements can be found here.
You can test your SQL statement by clicking the Play button next to the SQL statement field. The result is displayed in the Result field.
Please note:
-
You cannot use the SQL commands UPDATE and SET as these could potentially lead to conflicts.
-
The user table or other tables starting with the word User must be set in back ticks as not to be mistaken for a command, e.g.
`User rights`.
You can find a SQL query example for reference at the end of this document.
-
-
In the CSV properties section select a CSV separator and, if required, a Field separator from the selection list.
-
In the Note section you can leave an internal comment, if required.
-
Click Save.
Your report with its settings has been saved.
Note
Clicking on Definition file, you can download all your report settings as JSON file if you want to reuse it.
The table columns are created and maintained in the Actions section of your report.
After you have defined your report settings, entered a valid SQL statement, and saved your report, you have to create the table columns. These table columns are then displayed in the View tab of your report and they are used in the report exports.
Depending on if you created a new report from scratch or edited an existing report and SQL statement, you have to carry out the following:
-
If you have created a new report from scratch and entered a new SQL statement, you now have to generate the table columns based on the SQL statement. To create the table columns, click Generate columns in the Actions section.
-
If you have edited the SQL statement of an existing report, we advise to delete the existing columns by clicking Delete all columns and then generating them anew by clicking Generate columns in the Actions section. This is the easiest and time-effective way to adjust the columns. You can, however, also add new columns by clicking +New column and enter possible additions in the SQL statement this way.
After you have generated the required columns, you can adjust the column settings, if required, by clicking for the relevant column.
Note
These settings only apply to the report display in the View tab. They do not apply when the report is exported.
You can adjust the following column settings:
-
Column name SQL: The name of the column as written in the SQL statement. Only adjust this name if you have changed the name in the SQL statement.
-
Description: This is the column header in your report. If you want to display a different column header than created by default, you can change it here.
-
Column width: The column width is defined by default by the system. Here you can adjust the column width, if required.
-
Alignment: Select here, if you want the displayed values to be left-aligned, center-aligned, or right-aligned in the column.
-
Sorting: You can select the sorting order of your orders and either sort them by number or alphabetically.
-
Formatting: Here you can adjust different format settings for your values. If your column value for example is a money amount, you can select to display the sum in German formatting, that is with a comma (45,00) or in English formatting with a dot (45.00). Furthermore you can choose between different date and time formats or custom create your own formatting.
-
Sum column: By selecting this check box you can add a line at the bottom of your report where the the money amounts of the column are summed up.
-
Sequence: You can enter a different sequence number to rearrange the display in the report, e.g. by changing 1 to three, the column will not be the first column from the left, but the third.
After editing the required columns, click Save.
If required, you can delete single columns by clicking for the relevant column.
You can use variables as placeholder in SQL statements. Variables have the following format:
{PROJEKTNAME}
The variable name must in capital letters. Currently only German variable names are allowed.
Furthermore the variables you create will be available to you as parameter when creating a report in the View tab. You can use these variables/ parameters to include or exclude certain data in your report or to filter it.
To add a new variable:
-
Open the report you want to add a variable to by clicking
-
In the Setting area click +New variable. The Edit parameters form is displayed.
-
Enter a Variable name.
Note
Characters allowed in a variable name: capital letters (A, B...), numbers (1, 2...), and underscore (_). The variable name must not consist of several words with blanks in between.
-
Enter a default value for your variable in the Default value field.
-
Additionally, you can define a value range that you can select from when creating a CSV or PDF report, e.g. certain months, weeks, or years. For that purpose select the Edit manually check box and enter the following:
-
Dialogue text: Name of the selection field displaying the values in the Value selection field.
-
Description: Internal description.
-
Input: Select the type of input required, e.g. date selection, drop-down, text field, etc.
-
Value selection: Enter the values for selection, separated by comma. In the final variable an alias is automatically added with a colon, e.g. project identifiers like LP, ST can become Logistics:LP,Standard:ST. This is for processing in the SQL statement.
Note
This option can only be used within Xentral. It is not available via API or other data query tools.
-
-
Click Save.
The variable is saved and displayed in the Details tab of your report under Variables with its default value like this:
To edit or delete a specific variable, click on the variable and the Edit parameters form for the variable is displayed. You can now edit or delete the variable.
Furthermore to creating your own variables, you can use the following variables in your report SQL statement:
-
{USER_ID}. This is the ID of the current user.
-
{USER_PROJECTS}. List of all projects the user has access to. This variable is useful when you want to limit the output.
-
{USER_ADMIN → 1}. Enter 1 to only allow an output if the current user has admin rights or 0 to allow an output for all users.
-
{REPORT_PROJECT}. ID of the project the current report is assigned to.
-
{DOCTYPE}. Use this variable only when the report is opened via the document menu. This variable will show the document type.
-
{DOCID}. Value will only be displayed when the report is opened via the document menu. This variable will show the database ID for the document.
Apart from the normal report data display in the View tab of a report, Xentral offers the possibility to authorize specific display methods or integrations for each individual report for specific employees. There are several ways in which the report can be integrated in the employees workflow:
|
Display method/integration |
Description |
|---|---|
|
Graph |
The report data is displayed as graph in the report tile in the report overview (works only for single tables, not available for joint tables). |
|
Action menu |
The report is added to the action menu of a document type that you specify in the settings. |
|
Download |
The report can be downloaded from the report overview. |
|
Tab |
The report is added as a separate tab to a document type that you specify. This means that when you open the a document of the specified document type, an additional tab with the report data is displayed. |
After assigning authorizations you can define settings for the different display methods/ integrations.
You can define authorizations for your employees on the following path:
Reporting > Reports > Open report > Open Sharing tab
In the Sharing tab you see all existing authorizations for the selected report and you can add new authorizations and define the settings for the authorizations.
To authorize a user to display/ integrate the selected report data in a specific format:
-
Under Actions click +New sharing. The Integration in Xentral form is displayed.
-
Select the employee you want to authorize in the Employee search field.
-
Select the authorizations you want to assign.
-
Click Save.
The employee is displayed in the table with the authorizations assigned to him or her.
Approval tab > 1. Graph in the dashboard section
You can select to make the report data available as basic graphic representation. Xentral offers different visualization options.
To define the graph settings:
-
If you want to make the graph for your report visible to all users and not just those with relevant authorizations, select the Public sharing check box.
-
Select the graph you want to display (e.g. pie chart) in the Type select field.
-
Define the descriptions for the fields Y-axis labeling, Axis column, Data column(s), and Grouping column.
-
In the Date format fields select the required date format from the drop-down menu and enter the parameters to be displayed in the graph, that is how many days, weeks, or months should be displayed.
-
Scroll down to the bottom of the form and click Save.
Your graph settings are saved. All employees with the required authorization will now see the following element in the relevant report tile in the report overview:
By clicking on it, the employee can open the report data in graph form.
Approval tab > 2. File download in dashboard section
By default PDF and CSV reports are available for download from the report dashboard. If you want to make them unavailable to a user:
-
Select the Public sharing check box.
-
Select the format(s) in which the report should no longer be available for download.
-
Click Save.
Approval tab > 3. Action menu in receipts section
You can select to add the option to download your report via the action menu of a document type. This is useful if you need the report data available to you when you work with a specific document type, e.g. delivery notes.
To define the action menu settings:
-
If you want to make the download of your report available to all users and not just those with relevant authorizations, select the Public sharing check box.
-
Select the required document type in the Receipt select field.
You can usually find the document type within the URL when you open the required module.
-
If you want a specific wording to be displayed in the action menu, enter it in the Menu lettering field. If you do not enter anything into this field, the report name is displayed.
-
Define the Format in which you want the report to be downloaded.
-
Click. Save.
When you work with a document of the specified document type and open the Action drop-down menu options, you can find your report download option there. Clicking that option will start the download of your report.
Approval tab > 4. Tab in software
If you need the reports to be displayed directly in the documents, you can authorize the display in the document as separate tab.
To define the tab settings:
-
If you want to make the report tab available to all users and not just those with relevant authorizations, select the Public sharing check box.
-
Select the document type you want to add the report tab to in the Module field.
-
Enter the action where you want to apply the tab in the Action field.
You can find the action in the URL when you open a document.
-
Enter the required tab name in the Tab labeling field.
-
Select the positioning of your report tab in the Position field. You can select to add it before or after the Free field tab.
-
Click Save.
The report tab with your defined tab name entered is displayed in the relevant documents. Clicking on the tab name, the report is opened in a pop-up window.
Reporting > Reports > Open report > Transfer tab
You can select to automatically send or share the report via different channels. The following options are available to you:
To transfer the report using FTP:
-
In the Transfer via FTP section select the Activate check box to active the transfer via FTP.
-
Select the FTP type you want to use from the Type drop-down. You can select among the following options: FTP, FTP with SSL, and SFTP.
-
You can select the Passive mode check box if the server cannot establish a connection to the client, e.g. when a firewall is used. In this case the firewall will allow the connection as its source lies within the protected zone. This option is only viable when you use FTP or SFTP. However, we do advise to use SFTP.
-
Enter your relevant FTP details in the the following fields: FTP host, Port, FTP user, and FTP password. If you leave the port empty, the default port is used. The default port for FTP and FTPS is 21, the default port for SFTP is 22.
Note
Reports can only be transferred to the root directory of an FTP server!
-
Enter the transfer interval in the Interval fields. From the drop-down menu select the interval mode: Every X days, Weekly on, or Monthly on. In the field right of the drop-down menu enter the relevant numeric value. Example: If you select the interval mode Weekly on and enter the numeric value 3, the transfer is carried out on the third day of the week.
-
Clicking on the Time field the Select time window is opened. Use the Hour and Minute slider to set the required time or click Current time to define the current time. Click OK to confirm your selection.
-
Select the format of the file to transfer.
-
If you want to use a different file name for the file than the default report name, enter it in the File name field. You can use the variables mentioned in the form.
-
Scroll down to the bottom of the tab and click Save to save your FTP transfer settings.
The FTP transfer is now automatically carried out in the defined interval.
You can select to send the report via email, f.x. to an employee who might need to regularly evaluate the report data.
To send the report via email:
-
In the Email shipping section select the Activate check box to active the transfer via email.
-
Enter the email recipient and the subject of the email (email title) in the relevant fields.
-
Enter the transfer interval in the Interval fields. From the drop-down menu select the interval mode: Every X days, Weekly on, or Monthly on. In the field right of the drop-down menu enter the relevant numeric value.
-
Clicking on the Time field the Select time window is opened. Use the Hour and Minute slider to set the required time or click Current time to define the current time. Click OK to confirm your selection.
-
Select the format of the file to transfer.
-
If you want to use a different file name for the file than the default report name, enter it in the File name field. You can use the variables mentioned in the form.
Note
Some email providers block attachments with other file name extensions than .csv or .pdf. If you experience problems, please rename the files as proposed.
-
Scroll down to the bottom of the tab and click Save to save your email transfer settings.
The email is now automatically sent in the defined interval.
You can select to share the report via a specific URL.
To share the report via an URL:
-
In the Share URL section select the format in which you want to share the report from the Format drop-down menu.
-
Clicking the Availability fields, a calender is opened. Select the time frame in which the report should be available on the generated URL. If you do not enter a time frame here, no URL can be generated.
-
Click Generate a new URL to generate an URL to share the report on. The window is automatically reloaded and the newly generated URL is displayed in the Access via URL field.
-
To share the URL, click the copy icon
.
You can now paste the generated URL into any email or chat to share the report.
You can select to share the report via an API account.
To share the report via API:
-
In the Share API access section select the Activate check box to active the transfer via API.
-
Select the API account to share the report with in the API account field. You define API accounts under Administration > System > API account.
-
Select the format in which you want to share the report from the Format drop-down menu.
-
Click Save.
Reporting > Reports > Open report > View tab
The report is automatically generated when you open the View tab.
You can specify or limit the report data using the parameters in the Parameters area. The parameters displayed here and the available options for each parameter are equal to the variables defined in the Details tab. To apply parameters select the relevant values for each required parameter and click Set parameters. The report data is updated based on the selected parameters.
Furthermore you can use the column filters to filter for specific data or download the report data as CSV file or PDF file by clicking Export CSV or Export PDF in the Download area respectively.
select count(*) as Anzahl_Bestellungen ,ad.kundennummer as Kundennummer, au.name as Kunde from auftrag au inner join adresse ad on ad.id=au.adresse where au.datum between '{VON}' and '{BIS}' and au.status <> 'angelegt' group by au.adresse",
"columns
Note
'{VON}' and '{BIS}' are variables