Data sources management

The Pivot control needs data to work.
In the current version of the Pivot Reports module, data can only come from SQL queries targeting the current Dolibarr database.
You can add as many SQL queries as you want in the module's config page.
Each SQL query is treated as an independent data source and its name is displayed in the main module interface as the left menu.
Each SQL query already created can be disabled or deleted, it will no longer appear in the left menu of the module.

Data sources list

The list of Data sources is available in the module's config page, "Data Sources" tab.
The Pivot Reports module comes with 17 predefined data sources covering the majority of Dolibarr elements (Quote, Orders, ...).

Add a Data source

Above the list of data sources, the button "New Data source" opens the dialog for creating a new SQL query.

The name of the data source is mandatory as well as the SQL query (Data source) itself.
We recommend using aliases for the fields names in the SELECT query because these are the aliases that will be displayed by the control Pivot, and it is best to choose them carefully to reflect the data they represent.
It is also preferable that the alias contain only one word, but if it contains more than one word, surround them with apostrophes.
The Data sources provided as standard by the Pivot Reports module cover the majority of Dolibarr business entities, so you can use them to create your own query.
The Pivot Reports module checks the syntax of the SQL query, if it is not valid a message is displayed with the error found.
The word SELECT is not mandatory, if it is not present, it will be added by the module before submitting the request to the database server.
When the Data source is successfully created, a default report is created as well.
This report is called "Default":
You can then set it in the main interface of the module.

Example of a default report created by a new Data source:

Edit / Delete a Data source

In the list of Data sources, a click on the name of the data source brings up the data source card in consultation mode.
You can then edit it by clicking on the "MODIFY" button or delete it by clicking on the "DELETE" button.

Time fields naming conventions

The module has specific processing functionality for time field values ​​representing days, months, quarters, or semesters.
This treatment consists of:

• order them in ascending order (example for the months: Jan, Feb, ...)
• add the missing values ​​(for example: if you did not make sales during the month of August, this month will be missing from the data source, and it will also be missing in the X axis labels of the charts. To avoid this behavior, the module adds the missing months to have a more consistent charts)
• the months can begin with the first month of the fiscal year or the civil year (according to the setting of the module) All of these features are only possible if you follow the following naming conventions for time fields:
• Days: The field should be called "Day" and possible values ​​must be called: 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'
• Months: The field should be called "Month" and the possible values ​​must be called: 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
• Quarters: The field should be called "Quart" (don't use "Quarter") and the possible values ​​must be called: '1st Quart', '2nd Quart', '3rd Quart', '4th Quart'
• Semesters: The field should be called "Semester" and the possible values ​​must be called: '1st Sem', '2nd Sem'

Example: concerning the "Month" field:
SELECT
t.date_command AS date_command,
(CASE MONTH (t.date_command)
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
ELSE '' END) AS Month,
...

Reordering Data sources in left menu

Data sources are displayed as left menu in the main module screen.
To reorder these menu entries, you will need to use the Dolibarr menu manager.
For that:

• go to Home > Setup > Menus
• choose the "Menu editor" tab
• select "All" in the combo "Menu handler" and click on Refresh The entries in the left menu of the Pivot Reports module are then visible.
  You can reorder them using the small arrows at the end of each entry.

NB: Do not use this screen to edit / delete an entry from the left menu.
For modification / deletion, use the module Data sources manager instead in the admin part of the Pivot Reports module.