Welcome to the guide on displaying custom query results in a chart.

License and Reports

First of all, you need to check whether your license includes the Reports module and custom queries. Reports (dashboards) are available in the basic plan, i.e. the Basic plan. Custom queries and their creation are available from the Premium plan onwards. If you have this plan, you will find custom queries in the Tools module.

Creating a Query

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 as 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 go into the details of writing the custom query code itself, as it requires advanced knowledge of SQL (PostgreSQL) and the Flexi database.

In the first step, open the Tools -> Custom Queries module. As always, 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 be helpful when creating 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 pasting the code into the blue field, first click the Create Properties button. This button will create the columns for the corresponding query (in accordance with the select values, of course).

We will not display the percentage profit (on the 3rd row) in the chart, although it may come in handy for the table output in our case.

The query can be run (using the Run Query button) without displaying it in a chart. The output will then be a table.

You can change the name, data type (Type), and other settings for the properties (columns). However, it can be said that the Create Properties button handles this step for you. Any additional changes can be made on the Properties tab.

Configuring Parameters

The next step in our case will be creating the input parameters for the custom query.

You can create a parameter at any position in the code (provided it is selected) using the Create Parameter button. You can also enter it manually following the pattern mentioned above, e.g. <<datOd>>, or first create it in the parameters section and then insert it at any position using the Insert Parameter button.

If you open the parameter editor (using the Edit button), you can specify its data type, similarly to properties. This can be plain text, a number, a date, the currently selected record, or an existing object from one of the tables, for example. You can also set its default value.

Dashboard and Chart

At this point, the custom query should be ready to have its output displayed as a chart in the Reports module.

First, create a new dashboard in the Reports -> Manage Reports module. After this step, the report will be completely empty — no chart will be present, and everything will need to be added manually.

Once created, navigate directly to it — if it was created successfully, you will find it in the Reports module menu. After opening it, click the + button in the bottom-right corner of the report.

From the available options, select Custom Query Results and confirm. In the top-right corner of the newly created chart panel, open its settings.

In the first three fields, select the name, chart type, and custom query from which the chart will draw data. You can choose from the following chart types.

The custom query parameters listed here must match the actual parameters of the custom query — their codes must be entered exactly as defined. In our case, these are datOd, datDo.

The values in curly braces refer to the variable code in the report, which we will show in the next step.

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 only.

The value on the X axis can always be only 1. In our case, this is the month — so for each month, there will be unique values for revenues, costs, and the difference.

After saving, the chart will not contain any results — because the input parameters have not yet been filled in.

Use the button in the bottom-right corner again and, under the Other section, select the Report Variables option.

Here, add both variables one by one, corresponding to the input parameters of the custom query. As mentioned above, the values in the Variable column must match the values in curly braces in the chart parameters.

After saving the variables, a new table will appear where you can enter the variable values.

After entering the values — in our case, a date range — the chart will display the custom query results for the given period. On the Y axis (vertical), you can see the amounts; on the X axis (horizontal), you can see the months.