Note
This app has the Beta label. This means that the app is not fully developed and you may expect performance issues when using this app in the production environment.
The "Reports (Reports)" module allows company data from the xentral database, with the help of SQL statements, according to company-specific needs in table form.
In the overview, all already created reports can be viewed and new reports can be added via the "+ New entry" button. In addition, reports can be searched using the categories and report names stored in the report. In a tile, the report can be edited with the icons in the upper right corner (pencil icon), the report can be deleted (X), the report can be copied (paper icon) and with the info icon, the description of the report can be displayed.
In addition, black icons are available for selection at the bottom left:
-
TABLE: A click leads directly to the live view of the report
-
CSV: The report will be downloaded as a CSV file. If in the variables of the report, the "Edit manually" checkbox is activated and values have been entered, a dialog box will appear. In it, the variable can be selected for filling the SQL statement. Otherwise, the stored default value is used for filling
-
PDF: Download the report as a PDF file. For the variables of the report, the hook "Edit manually" activated and also values in the value selection have been entered, a dialog box will appear. In it, you can enter the values for the variable that can be selected for filling the SQL statement. Otherwise, the stored default value is used for filling
-
Chart: In order for the chart to be displayed in the overview next to Table, CSV and PDF, you must specify "Yes" in the respective report in the settings for graphs in the dashboard. When you click on it, a graphic appears
In order to access further information about a report, the following reference is made to a concrete report. In order to access this information must be clicked on that specific report.
The "View" tab displays the result of the SQL statement. All Column definitions that were previously created are displayed. This table can be sorted and searched in xentral, just like the other tables, provided that no ORDER BY was used in the SQL statement.
In this view, the sorting statements are not considered, since the sorting can be selected manually here. An export of the report is nevertheless sorted accordingly, provided that an ORDER BY in SQL statement is present.
If parameters have been defined for the report with the "Edit manually" checkbox set have been selected, an input field will be displayed next to the table for each parameter. If the parameters have been selected, click once on "Parameter Set" one time to apply the parameters to the table view. The parameters must not be empty, i.e. a value must always be entered. The parameters are fix and currently cannot be changed within the SQL statement.
In the "Details" tab, there are several areas to define the properties of the report.
Various settings can be made for the report.
The following fields are available:
-
Name → A descriptive name for the report must be specified in order to quickly find it in the search of the report overview
-
Category → A freely selectable category for the report is to be specified. This allows the reports to be searched by category in the report overview and be displayed
-
Description → A description must be specified, such as the purpose, the Information of the report or the use of the variables created
-
Project → Restricts the visibility in the report overview, i.e. only users with the appropriate project rights can see this report in the See report overview
-
Load from file → If a file with the necessary settings is available, it can be loaded by clicking on the "Select file" button
By clicking on the "+ New Variable" button, a new variable, i.e. a placeholder for the SQL statement, can be created. Variables are capitalized in the SQL statement and enclosed in curly brackets, e.g. {PROJECTNAME}.
The following fields are available:
-
Variable name → A variable name from the SQL statement must be specified
-
Allowed characters → uppercase letters, digits, underscore ("_")
-
Illegal characters → lowercase letters, umlauts, "ß" and other special characters
-
-
Default value → If no other values have been stored in the "Value selection" field were, a default value for this variable is to be set
-
Edit manually → If this check mark is activated, a dialog box is displayed for CSV and PDF output, a dialog box is displayed beforehand, where the following fields are relevant:
-
Dialog text → Label of the selection field for the values in the field. "Value selection"
-
Description → Serves only internal purposes and has no further use
-
Input → Select the desired input from the drop-down menu
-
Value selection → Here can be comma-separated the values that are to be selected in the dialog box for the download desired and subsequently in the Report are specified. The value selection is automatically supplemented by a colon, e.g. yes,no a Yes:yes, No:no
-
The "Details" tab can be used to customize the structures. The input of the SQL statement takes place here to get a direct result of the query. The designations of the individual database tables and -columns can be easily defined with the module Database View.
The following fields are available:
-
SQL Statement: the SQL statement created is to be entered here. With the "Play"-button on the right side of the tex, the SQL statement can be executed directly. In the field "Result" a feedback appears, whether the SQL statement could be executed successfully. In case of failure, an error message appears.
Note
If something is changed to an already saved SQL statement, a warning appears to indicate that the changes have been saved.
Use of variables
Variables that have been set as parameters can be used in the query by entering the variable names in curly brackets. Note that SQL commands like UPDATE or SET are not allowed as name or value, because they can cause a conflict. In addition to the specified parameters, the following variables are available:
-
USER_ID → ID of the current user
-
USER_PROJECTS → list of all projects that the current user can access. Can be used e.g. to restrict the output: "[1] WHERE. order.project in {USER_PROJECTS}"
-
USER_ADMIN → 1 = current user is an administrator, 0 = current User is not an administrator
-
REPORT_PROJECT → ID of the project to which the current report is assigned
-
DOCTYPE → To be used only when the report is called from a document menu. Contains one of these document types: "quote", "order", "credit note", "invoice", "delivery bill", "order", "production"
-
DOCID → Appears only when the report is called from a document menu. Contains the database ID of the document
Note
The table User and other tables (e.g. Userrights, Userconfigurations) can only be addressed if the word is set in quotation marks ``. This is necessary so that the SQL report does not misunderstand the word as a command.
The table shows the created columns, which can be seen in the "View" tab and in exports. The columns can be edited and deleted at any time.
Button "+ New Columns"
With the button "+ New Columns" a new column for the table can be created.
The following fields are available:
-
Column name SQL → The column from the SQL statement must be specified. The column name must not contain a table alias, e.g. but rather a name
-
Label → A column name must be specified to define the column heading for later view or export
-
Column width → This optional value defines the column width for the later view or export
-
Alignment → Specifies the alignment (right, center, left) of the content of a Column for "view" or export. The desired alignment is to be selected from the drop-down menu
-
Sort → Specifies whether a column should be sorted alphabetically or numerically. Numeric sorting only works if the database column has a numeric data type
-
Formatting → The desired formatting can be selected from the drop-down menu, for example, the amount of money in EUR
-
Sum column → This option is optional. If this is activated, at the bottom of the table, the value of this column is summed up and displayed in the view, and displayed in the export
-
Order → When generating the report, the columns will be ordered according to this number arranged in ascending order
Create columns button
With the button "Create columns" the columns can be created automatically by the system and edited subsequently if necessary. To use this function, the SQL statement must have been successfully executed once by the "Play" button (see field "SQL Statement").
Delete all columns button
With the button "Delete all columns" all columns can be deleted from the column overview. This is recommended, for example, if a large output of the SQL statement is changed and not every column should be deleted from the column overview.
The CSV delimiter and field separator set here will be used when generating a CSV file for this report. These settings should be adjusted if the report is to be processed by a target system that requires specific separator characters.
If different areas are to be displayed for different users, this can be controlled via "Shares". In the overview, all previously created Releases for users and their shared areas can be viewed.
Click on "+ New share" to create a new share.
The following fields are available when creating/editing in the share dialog:
-
Employee: The employee must be selected for whom the release should apply
-
Release: It is to select which areas for the employee to be released. The choices are 1. graph, 2. download, 3. action menu, and 4. tab (tab)
1. Graph in dashboard
The graph in the dashboard has rather rudimentary functions and only works for a table, not with a join.
-
Share publicly → Checking this overrides personal shares
-
Type → From the drop-down menu, select how the graph will be displayed in the dashboard
-
x-axis label → The label is taken from the table
-
y-axis label → The label is to be entered
-
Axis column → The desired axis column is to be entered
-
Data column(s) → The desired data column is to be entered
-
Grouping column → The desired grouping column is to be entered
-
Date format → A date format is to be selected from the drop-down menu
-
Date format → The date format is to be selected from the drop-down menu, possible are days, weeks, and months
Note
As a column label should be well understandable aliases should be used. In addition, aliases must not be used twice.
2. File download in dashboard
-
Share publicly → If this is checked, any user can download the Download report in the overview. Additionally, there must be hooks at the formats set
-
PDF → If this check mark is set, the report can be downloaded as a PDF file
-
CSV → If this check mark is set, the report can be downloaded as a CSV file
3. Action menu in documents
If a report is to be called up quickly for a specific document type, the report can be integrated into the action menu of a document.
The following fields are available:
-
Share publicly → If this is checked, the report will be shared with all users who are employees of the project stored in the report, in the action menu. If this check mark is not set, the report is only displayed for users with previously created, suitable release in the action menu of the document
-
Document → It is necessary to select for which document type the report should be inserted. Possible document types are offer, order, invoice, credit note, delivery bill, purchase order and production
-
Menu label → Specify the label with which the report should be displayed in the pull-down menu. If the field remains empty, the name of the report is used
-
Format → Determines in which format the report will be downloaded in the action menu. Available formats are CSV and PDF
4. Tab (tab) in software
If a report for a specific document type is to be displayed quickly, the report can be integrated as a tab in the document interface.
The following fields are available:
-
Share publicly → If this is checked, the report will be displayed as a tab in the detailed view of the document. If this check mark is not set, the report will be visible as a tab only for the users with previously created matching share
-
Module → Select here for which module the report should be inserted as a tab. Possible module types are here offer, order, invoice, credit note, delivery bill, order and production
-
Action → Here it is specified on which page of the document the tab should be inserted. If the field remains empty, the overview page of the document type will be is selected
Note
To determine the correct action, it is necessary to go to the View in the document, in which the tab should be visible and from the Address line of the browser the value behind action= to copy. Example: yourxentraldomain/index.php?module=order&action=edit&id=1
-
Tab label: specify the visible name that the tab should carry
-
Position → from the drop-down menu select, whether the tab should be created before or after the free field
In the "Transfer" tab, there are several ways in which reports can be automatically be transferred.
Reports can be transferred automatically via FTP, among other options.
The following fields are available for this purpose:
-
Enable → Only if this checkmark is set, the FTP transfer will take place
-
Type → The selection of the type (FTP, FTP with SSL, or SFTP) must be made
-
Passive Mode → Check the box to enable the passive FTP mode. You can use this mode when the server cannot connect to the client. This is the case, for example, if the client is located behind a router that rewrites the client's address using NAT, or if a firewall shields the client's network from outside access. In this case, the firewall allows the data connection because it has its origin within the protected zone
-
FTP host → The host name of the FTP server must be specified
-
Port → The port of the FTP server is to be specified. If this field is empty, the default port 22 is used for FTP
-
FTP user → The user name of the FTP server is to be specified
-
FTP Password → The password of the FTP server is to be specified
-
Transfer interval:
-
Every X days → Enter a number, e.g. 5 → every 5 days the report will be transferred via FTP
-
Weekly on → Enter a number, e.g. 2 → always on the 2nd day of the Week, the report is transferred via FTP, so once a week
-
Monthly on →Enter a number, e.g. 3 → always on the 3rd day of a month, the report is transferred via FTP, so once a month
-
-
Time → Here you specify the time at which the report should be transferred via FTP. This usually only works if the transfer is set up before this time on the same day. Example: If the transfer should take place daily at 9 o'clock, then the transfer must be set up and saved before 9 o'clock
-
Format → Specifies in which format the report will be transferred as a file to the FTP server. The CSV and PDF formats are available
-
File name → The file name of the report must be entered. If this field remains empty, the name of the report will be used. In addition, the variables {TIMESTAMP}, {DATE} and {REPORT NAME} are available, which are replaced in the file name accordingly. The file name should ideally only consist of letters, numbers and variables. With special characters and also e.g. "-" it can be, that the report will not be sent
If you want to send reports via email, please make sure that the process starter Berichte FTP Übertragung is active. More information on process starters can be found here.
The following fields are available:
-
Enable → Only if this checkmark is set, the sending by e-mail will take place
-
E-mail recipient → The e-mail address of the recipient of the report is to be specified
-
Subject → The subject of the email is to be specified
-
Transmission interval:
-
Every X days → Enter a number e.g. 5 → every 5 days the report will be sent by e-mail
-
Weekly on → Enter a number, e.g. 2 → always on the 2nd day of the Week, the report is sent by e-mail, so once a week
-
Monthly on → Enter a number, e.g. 3 → always on the 3rd day of a month, the report is sent by e-mail, so once a month
-
-
Time → It is necessary to specify the time at which the report will be sent by e-mail
-
Format → Specifies in which format the report is attached to the e-mail as a file. The CSV and PDF formats are available
-
File name → The file name of the report must be entered. If this field remains empty, the name of the report will be used. In addition, the variables {TIMESTAMP}, {DATE} and {REPORTNAME} are available, which are replaced in the file name accordingly
If the report is required as a download via a URL, the following fields can be used:
-
Format → Determines in which format the report will be downloaded when calling the URL. The choices are PDF and CSV
-
Availability → A "from" and "to" date must be specified to define, in which period the URL is valid. If these fields remain empty, the URL always
-
Retrieval via URL → To get the URL for the report, click on the button. "Create new URL" to be clicked. The created URL will be displayed. With the "Copy" icon to the right of the text field, the URL can be copied to the clipboard. Each time a new URL is created, all previous URLs lose their validity
If other applications and external tools should be able to access the report automatically, the report can be shared for access via REST API. The prerequisite is that a REST API account has already been created.
The following fields are to be used:
-
Enable → When checked, the report can be accessed via the REST API
-
API account → Select the API account for which the report should be shared. Applications must use this API account to access the report
-
Format → Determines in which format the report is downloaded when the REST API is called. The choices are PDF and CSV
How to call the report via REST API can be found in the REST API documentation. Endpoint: /v1/reports