You can display the result of a custom query in the web app on your dashboard as a chart or a table. This guide walks through the entire process using an example query that sums the revenues, costs, and gross margin of issued invoices by month.
First, check whether your license includes the Custom Queries module. Custom queries and their creation are available only in the Premium plan. If you have this plan, the fastest way to open the Custom queries module is to type its name into the search in the top bar.
You can order the creation of a custom query from us as a paid service. The output is always a table, which can also be displayed as a chart. In this case, the query can also be used in a lower plan.
Creating a custom query
We won't go into the details of writing the custom query code itself, as it requires advanced knowledge of the SQL language (PostgreSQL) and the ABRA Flexi database.
Step 1: In the Custom queries module, click New. The custom query form opens with the fields Name, Abbreviation, Private, and Query, and with the tabs Properties, Parameters, and Texts.
⚠️ Do not use a slash in the abbreviation of a query that you want to display on the dashboard. The dashboard widget calls the query by its abbreviation, and with an abbreviation such as OBRAT/MESIC the result will not load. The widget will stay empty and no error will be displayed. Use, for example, OBRAT-MESIC.
We will work with a query that shows costs, revenues, and gross margin for a period of your choice. The start and end dates will therefore be the input parameters.
For inspiration and testing, you can copy and use the code below. The PostgreSQL documentation and the Flexi database structure may help you when creating the 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 procento
from ddoklfak as f
left join aadresar a on a.idfirmy = f.idfirmy
where f.modul = 'FAV' and f.sumcelkem != 0 and f.datvyst between '<<datOd>>' and '<<datDo>>'
group by date_part('Month', f.datvyst)
order by date_part('Month', f.datvyst)
Step 2: After pasting the code into the Query field, create the properties (that is, the output columns) on the Properties tab. Add each column using the New button below the table. The Abbreviation must match the column name in the select part. You can also change the Name, the data type (Type), column visibility, and width.
In the desktop application, the Create properties button is available, which creates the columns for you based on selectu. In the web app, you need to create the properties manually.
Step 3: You can run the query using the Run query button even without displaying it in a chart. The application first asks for the parameter values and then displays the result as a table, which you can also export to Excel.
Step 4: On the Parameters tab, create the input parameters of the query.
You can use a parameter anywhere in the code; in our case, it is in the where condition. It must be written in the form <<datOd>>, and in the case of a date, it must be enclosed in apostrophes, that is, '<<datOd>>'.
You can change the abbreviation, type, default value, and whether the parameter is required directly in the table row. To display all the parameter fields, click the pencil icon at the end of the row. As with properties, you specify the parameter's data type: it can be plain text, a number, a date, the currently selected record, or, for example, an existing object in one of the tables. Last but not least, you can specify its default value.
After you save it using the Save and close button, the custom query is ready to have its output displayed as a chart on the dashboard.
Displaying the result on the dashboard
Step 1: On the dashboard, click the arrow next to the New button in the top right and select Dashboard widget.
Step 2: In the Dashboard widgets panel, click Custom query results. The widget is added straight to the end of the dashboard. Then close the panel using the Close button.
Step 3: In the new widget, click Settings. Later, you can access the settings through the three-dot icon in the top right of the widget.
Step 4: In the settings panel, select the custom query and specify how the result should be displayed. We describe the individual fields below.
Name and Custom query
The first two fields are used to name the chart and to select the custom query.
Custom query parameters
This field is used to enter the input parameters. Each parameter goes on a separate line in the form jméno=hodnota. The name before the equals sign must exactly match the abbreviation of the custom query parameter, which in our case is datOd and datDo.
In the chart, it is not yet possible to specify parameters using variables. Values must be entered directly, and to change them, you need to switch to the widget settings.
Series names
Each series name must be on a separate line in the form hodnota=jméno;typ. The value before the equals sign must match the abbreviation of the custom query property. All series should have the same data type so that the Y axis shows values in only one unit. To display help on the format, click the question mark icon in the field.
Display
This field determines whether the result is displayed as a chart or a table.
Chart type
If you choose the chart display, you select its type here: clustered column, stacked column, line, or pie.
X-axis value
There can be only one value on the X axis. In our case, it is the month (property abbreviation mesic), so each month will have separate values for revenues, costs, and gross margin.
Data type in series and Size
Next, specify the data type in the series (amounts, integers, or decimal numbers; in our case, decimal numbers) and the size of the widget: large widget (2 x 2) or extra-wide widget (4 x 2).
Step 5: Save the settings using the Save button. The desired output will appear on the dashboard.
As with other outputs, the chart responds to hovering the cursor over it, so you can view specific values.
The permissions a user needs in order to view the widget are described in the article on permissions for dashboard access. For the procedure for the desktop application, see the guide Displaying custom outputs in a chart in the desktop application.
💡 Need help? Email us at podporaflexi@abra.eu or use the chat in the bottom right corner.