I have been challenged to create a report that will run (with a run-time option to select # of days prior) that will display:
For each Contact 'Assigned to' name, the number of contacts added during the report period and the total number of contacts they have (including the newly-added),
Graphical and / or row & column will suffice.
Any ideas?
As John and Mauricio mentioned, this is not possible using regular reports, but this is a good candidate by Advanced reports. The only downside of using Advanced reports is that there won't be any "Runtime" option, but this can be overcome by adding the most useful information.
For example, here is a quick report that I created in the last couple of mins:
This is the query that I used:
select concat(u.first_name, ' ', u.last_name) User,
sum(case when c.date_entered > now() - interval 30 day then 1 else 0 end) 'Created in Last 30 days',
sum(case when c.date_entered > now() - interval 90 day then 1 else 0 end) 'Created in Last 90 days',
sum(case when c.date_entered > now() - interval 1 year then 1 else 0 end) 'Created in Last 1 year',
count(*) 'Created All Time'
from contacts c,
users u
where c.created_by = u.id
and c.deleted = 0
and u.deleted = 0
group by c.created_by
order by 2 desc, 3 desc, 4 desc, 5 desc, 1;
Follow this documentation to know more on how to create advanced reports with a query: support.sugarcrm.com/.../
But - this might cause performance issues since this scans through the whole contacts table, so use it judiciously.
As John and Mauricio mentioned, this is not possible using regular reports, but this is a good candidate by Advanced reports. The only downside of using Advanced reports is that there won't be any "Runtime" option, but this can be overcome by adding the most useful information.
For example, here is a quick report that I created in the last couple of mins:
This is the query that I used:
select concat(u.first_name, ' ', u.last_name) User,
sum(case when c.date_entered > now() - interval 30 day then 1 else 0 end) 'Created in Last 30 days',
sum(case when c.date_entered > now() - interval 90 day then 1 else 0 end) 'Created in Last 90 days',
sum(case when c.date_entered > now() - interval 1 year then 1 else 0 end) 'Created in Last 1 year',
count(*) 'Created All Time'
from contacts c,
users u
where c.created_by = u.id
and c.deleted = 0
and u.deleted = 0
group by c.created_by
order by 2 desc, 3 desc, 4 desc, 5 desc, 1;
Follow this documentation to know more on how to create advanced reports with a query: support.sugarcrm.com/.../
But - this might cause performance issues since this scans through the whole contacts table, so use it judiciously.
