Data and Internationalization
An SQL Query may extract almost anything from the database.
This process ignores the security restrictions in the system, so you will have to implement them yourself.
Structure
The structure is the same for all graphs.
The first row of data will be used for headers.
The column named "Title" will be used for titles. On circular diagrams that is the labels, next to the chart, and on xy-graphs that is the labels on the x axis.
The rest of the headers will be used when hovering over the graph, to give information about the data. On xy-graphs it will also be displayed as labels.
The rest of the lines in the data will be added as datapoints in the graph.
If the datapoint is a number, the graph will auto-scale to match the data.
Strings are also supported, but the outcome is unknown.
If a datapoint is null, the graph will bridge the gap, if it is an xy-line-graph, see sample here.
Sample pie/doughnut-graph
| Title | TitleEnglish | This moth | Last month |
|---|---|---|---|
| GOOG | Alphabet | 100.23 | 25.23 |
| TSLA | Tesla | 10.23 | 20.23 |
| AAPL | Apple | 50.23 | 50.23 |
Sample line-graph
| Title | TitleEnglish | Alphabet | Tesla | Apple |
|---|---|---|---|---|
| THIS | This Month | 100.23 | 10.23 | 50.23 |
| LAST | Last month | 25.23 | 20.23 | 50.23 |
Multiply y-axis
Multiple y-axis are supported as of version 9390.
To use this feature, prepend the name of the new axis to the data that should be on that axis, eg y1|Apple.
The default y-axis is named y. If no axis is specified, this will be used.
You can specify as many axis as ChartJS supports.
Post-Parsing data
There is two possible post-processing methods available. They can't be combined.
Transpose
Start the query with a line containing: -- @TRANSPOSE
This transposes (inverts columns and rows) the data fetched by the query, before handing it of to the chart-engine.
Parse
Start the query with a line containing: -- @PARSEDATASET
This expects the query to generate data in the following format (headers are optional), and removes the support for links.
| X-axis | Group by | Y-axis |
|---|---|---|
| 2024 | Sales | 2000 |
| 2024 | Budget | 2000 |
| 2025 | Sales | 2500 |
| 2025 | Budget | 4000 |
| 2026 | Budget | 5000 |
Internationalization
Add extra columns named "Title", followed by the name of the language, eg TitleEnglish.
If a column with this name is found, it will be used, otherwise the grath will fallback to the "Title" column.
If no title-column is found, the first column will be used, for compatibility reasons.