1. As an example, we will use a query that retrieves companies that have been issued the most invoices within a given period, along with the total number of those invoices and the number already paid:
select nazfirmy, count(*) as pocet, count(CASE WHEN datuhr is not null THEN 1 ELSE null END) as pocetUhrazenych from ddoklfak where modul = 'FAV' and datvyst >= '2016-01-01' and datvyst <= '2016-12-31' group by nazfirmy order by pocet desc limit 10;
2. Prepare a user query from the SQL.
select nazfirmy, count(*) as pocet, count(CASE WHEN datuhr is not null THEN 1 ELSE null END) as pocetUhrazenych from ddoklfak where modul = 'FAV' and datvyst >= '<>' and datvyst <= '<>' group by nazfirmy order by pocet desc limit <>
Make sure to create "Parameters" and "Properties" for the query. The max parameter can be optional, but in that case set a default value for it, e.g. "5". You will also need the ID of the newly created user query, e.g. "11".
3. The query should be callable via the API:
https://localhost:5434/c/firma/uzivatelsky-dotaz/11/call.json?fromDate=2016-01-01&toDate=2016-12-31"
4. On the dashboard, click the button for adding a component (the yellow circle with a white plus sign in the bottom-right corner), select "User Query Results" under the "Charts" column, and click "Add".
5. For the newly added chart (it should be the last one, at the very bottom), click the three dots in the top-right corner, select "Settings", and fill in the fields:
Name — Any name you like; can be left empty.
Chart type — Select Grouped Bar.
User query ID — 11
User query parameters — Each parameter should be entered on a separate line in the format "parameter=value". These parameters will be used when the query is called.
When the query is called, the parameters fromDate (start date) and toDate (end date) are automatically populated with the current "From" and "To" values of the dashboard. These parameters are also used in the example query here and are enclosed in single quotes (datvyst >= '<<fromDate>>').
Set the max parameter to 10, i.e. enter: max=10
Series names
Here you need to list the columns from the query result that should be displayed in the chart (not all columns are required) and assign the name under which each will appear in the chart. As with the parameters above, each column is described on a separate line in the format column=name.
We want to display the column pocet as "Total count" and pocetuhrazenych as "Count of paid", so enter the following:
pocet=Total count
pocetuhrazenych=Count of paid
Note that the column name is written in lowercase, matching the format returned in the API response. In the SQL, the column is named pocetUhrazenych (with a capital "U"), but do not use that name here.
X-axis value
Enter the name of the column you want to use for the values on the X axis — in our case, nazfirmy.
Data type in series
This setting lets you specify whether the chart displays whole numbers or decimal numbers. In our case, whole numbers.
6. Save the settings and the chart should appear.
== Pie chart ==
This chart type displays only one series — the one listed first in the "Series names" field. Any additional series, if present, are ignored.
For more information, see our tutorial article — Displaying custom outputs in a chart.