Welcome to the guide on displaying custom query results in a chart.
First of all, you need to verify whether your license includes the option to use the custom queries module. Custom queries and their creation are available from the Premium plan onwards. If you have this plan, you will find custom queries in the overview of all modules.
You can order the creation of a custom query as a paid service directly from us. The output is always a table, which can be displayed in a chart. In this case, the query can also be used in a lower-tier plan.
Let's move on to the first step — creating a custom query. We will not cover the actual writing of the custom query code in detail — it requires advanced knowledge of SQL (PostgreSQL) and the ABRA Flexi database.
In the first step, we open the Custom Queries module. You can see where to find it in the screenshot above. As always, we click the New button. The custom query editing window will appear.
We will work with a custom query that displays costs, revenues, and gross margin for a period you define. The from and to dates will therefore be input parameters.
For inspiration and testing purposes, you can copy and use the code from the query shown above. The PostgreSQL documentation and the Flexi database structure may also be helpful when building your query.
select date_part('Month', f.datvyst) as mesic, sum(f.sumcelkem) as vynosy, sum(f.sumnaklady) * (-1) as naklady, (sum(f.sumcelkem) - sum(f.sumnaklady)) as rozdil,ROUND((sum(f.sumcelkem) - sum(f.sumnaklady)) / sum(f.sumcelkem),2) * 100 as procentofrom ddoklfak as fleft join aadresar a on a.idfirmy = f.idfirmywhere f.modul = 'FAV' and f.sumcelkem != 0 and f.datvyst between '<>' and '<>'group by date_part('Month', f.datvyst)order by date_part('Month', f.datvyst)
After inserting the code into the Query field, we can create properties (output columns). In the web interface, this currently has to be done manually.
In the desktop application, the Create Properties button is available. This button automatically creates columns for the respective query based on the select values. In the web application, properties currently need to be created manually.
You can change the name, data type (Type), and other settings for each property (column). However, it is worth noting that the Create Properties button handles this step for you.
The query can also be executed (using the Run Query button) without displaying it in a chart. The output will then be a table.
The next step in our case is to create the input parameters for the custom query. This can be done on the Parameters tab.
A parameter can be placed at any position within the code. In our case, it will appear in the where clause. The notation must follow the format <<datOd>>, and in the case of a date value, it must be wrapped in single quotes: '<<datOd>>'.
If we open the parameter editor (the Edit button), we can specify its data type, similar to properties. This can be plain text, a number, a date, the currently selected record, or for example an existing object from one of the tables. Finally, we can also set its default value.
After saving the parameter, the custom query should be ready to have its output displayed in a chart on the web interface dashboard.
First, we will create a new dashboard widget. We do this using the dropdown list next to the New button from the dashboard view.
Select the Custom Query Results option. Clicking the "+" button will immediately add the widget to the dashboard and you can return to it.
Once created, open its settings directly from the dashboard and select the relevant custom query. In our case, this is the OBRAT/MESIC query.
The first two fields are used to name the chart and to select the custom query.
The third field is used to specify the input parameters. The custom query parameters entered here must match the actual parameters of the custom query — the codes must be entered exactly as defined. In our case, these are datOd, datDo.
At this time, it is not possible to use variables for custom query parameters in the chart. Parameters must be entered directly, as shown in the screenshot. To change the entered parameters, you need to switch to the chart settings.
The values before the equals sign refer to the parameter codes of the custom query.
The series names — or rather their codes — must match the property codes of the custom query. They should also all have the same data type so that the Y axis displays values in a single unit.
Series names must follow the format — value=name;type
The next field determines whether the output should be displayed as a chart or a table.
The Chart Type field is used to select the type of chart, when the Chart display option is chosen.
The X axis value can only ever be 1. In our case, this is the month — meaning that for each month, there will be unique values for revenues, costs, and differences.
You can also specify the data type for the series (in our case, decimal numbers) and choose the chart size. For our purposes, we can select a large chart.
After saving, the desired output should be displayed.
The chart, like other outputs, responds to cursor interaction. You can therefore display specific values.
