Grouping and Getting graphs.
All reports that have a grouping will automatically get a bar and pie chart at the end of the page.
To add a grouping we must select the summary type on the third step of the report creation
and a field to group by in the fifth step
If we select a field of type date we will get an additional choice to group by the different parts of the date value. But this isn't a grouping option for aggregations it simply sorts the records together by that part of the date.
Let me repeat that, all grouping options do not group the report records themselves, they will only be used in the charts and to order/sort the values together. In the case of date values the sorting will be done starting by the year and UP TO the date part chosen. So if we select a date field and then group by day it will effectively order by the complete date
order by DATE_FORMAT(Potentials_Expected_Close_Date, '%Y' ) ASC, CEIL(DATE_FORMAT(Potentials_Expected_Close_Date,'%m')%13) ASC, DATE_FORMAT(Potentials_Expected_Close_Date,'%d')
if we select month the rows will be ordered by year and month
order by DATE_FORMAT(Potentials_Expected_Close_Date, '%Y' ) ASC, CEIL(DATE_FORMAT(Potentials_Expected_Close_Date,'%m')%13)
and if we order by quarter, they will be sorted by year and quarter
order by DATE_FORMAT(Potentials_Expected_Close_Date, '%Y' ) ASC, CEIL(DATE_FORMAT(Potentials_Expected_Close_Date,'%m')/3)
Direct SQL reports
In order to enhance the possibilities of the reporting system, we added the option to introduce a direct SQL statement.
For the reporting extension to work correctly with the SQL command there are some rules that must be respected.
- each column must have an alias that is the module name followed by the field label with all spaces substituted by underscores.
- The last column must be the crmid of the record of the row with an alias of ACTION
- you must select the main module of the SQL command when creating the report in order to support ad-hoc conditions
- you cannot use conditions on groups (having)
The most demanded feature that is resolved with the direct SQL option is to calculate some aggregation on records grouped by some relation. For example. the total invoiced per client.
We can create a normal report where we order the invoices by the account and filter those that start with a letter "A":
Now if we want to sum the total of each invoice per account we would create a report with direct SQL like this:
SELECT SQL_CALC_FOUND_ROWS vtiger_accountInvoice.accountname AS Invoice_Account_Name, concat(vtiger_invoice.currency_id,'::',sum(vtiger_invoice.subtotal)) as 'Invoice_Sub_Total', concat(vtiger_invoice.currency_id,'::',sum(vtiger_invoice.total)) as 'Invoice_Total', 0 AS "LBL_ACTION" from vtiger_invoice inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid left join vtiger_account as vtiger_accountInvoice on vtiger_accountInvoice.accountid = vtiger_invoice.accountid group by vtiger_accountInvoice.accountname,vtiger_invoice.currency_id
The resulting report filtered by those organizations whose name start with "A" looks like this:
Photo by rawpixel on Unsplash rawpixel