Custom Data Source

You can easily see any table in the system. You can link tables together, append columns from linked table etc. However, if you need to group the data, easily see aggregations and load data from nested links, you would probably use some kind of query. And that's exactly what custom data source extension enables. Here, you can create a query to your data and use it as data source for reports or widgets.

Let's say you have a table with thousands of orders

You do not want to load all of them into a report. Or you just want to create a simple widget with country and total amount. You need something like this:

As every extension, first you need to install that into the system. (see edit application)

In the table "Data Source" you add a new row. That will display a form with a list of all tables. In every table, you can select, which field (column) you are about to load. In case you aggregate the table, you can select the type of aggregation. The stored "query" can be used later in reports and widgets or used in API request.

Input screen for the custom data source definition

 User rights

Be careful what data you select to show in the custom data source and who you permit to create/edit custom data sources.

The custom data source does not check user permissions as in the case when loading data from a table (grid, widget, report) or API requests.
This means that with a custom data source, the user can also view data that they cannot access by default (denied tables/fields) - e.g. summarized in widgets or reports.

However, you can apply a condition to read data from the user role of the user under which the data is loaded, meaning that you can filter which records the user sees.
Applying user role conditions is the default behavior for all new custom data sources. 
These conditions can be disabled for a specific custom data source - for example, data that would otherwise be filtered out by conditions can be used for summary calculations.
Only a user who has permission to change user rights can disable these conditions.

For example - if you create a custom data source that retrieves invoice amounts (either individual invoices or the sum for months/years) and you use this custom data source for a widget or report that can be viewed by a user who is not allowed to view the invoices (or at least the amount field), he will still see the amounts in the widget or report.
If you load such a widget from the data source "table", then the user will not see the invoices/items because he is not allowed to access it.
This means that the use of a custom data source can be useful where the user does not have access to certain data, but you want to show him some data in the widget or in the print report.
However, if the user can only see e.g. invoices for a certain recipient using a filter in user roles, this filter can be enabled/disabled in the custom datasource and this can control what data the user can see. 

If the user has read-only permission for the custom data source, the definition of the custom data source cannot be changed and the query version stored in the DB is always used to load the data into the results preview.