You can generate a lot of useful reports with the query builder of the report manager. For more complex cases, you can also edit the SQL behind it and in doing so you can utilize MySQL or MariaDB functions, for example, to query time intervals.
The required knowledge around Reporting and the database structure of i-doit can be found in the respective articles. A good knowledge of SQL or SQL functions provided by MySQL/MariaDB is clearly advantageous.
Often a report is needed to provide information about a certain time interval. i-doit already supplies the required fields with date specifications, for example, when an object was changed the last time or when a maintenance contract expires. The query builder of the report manager can address these fields but only with fixed date specifications: Which objects were changed since 2016-01-01? However, often the date is required to be in relation to the current time: Which objects were changed during this month? You don't specify the month but it is assumed that the month during which the report is executed is the month in question.
In order to generate such a report we create a new report with the query builder first. For the query we add, for example, object title, object type, date of the last change and the name of the editing person.
We save this report and then we duplicate it and name the duplicate with a significant name, for example "Changed objects of this month".
We duplicate the report as it cannot be opened with the query builder anymore once the SQL has been edited.
Then we edit the duplicate with the SQL editor and extend the WHERE clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
In order to narrow the time interval to this month, we use the SQL functions
MONTH(). These are applied to the change date that is saved in the table column
bj_main.isys_obj__updated. Additionally, we exclude all relation objects with
j2.isys_obj_type__const != 'C__OBJTYPE__RELATION'.
You can find this and other examples regarding the query of time intervals on the Stackoverflow website.