Author: Nikolay Bulava, CEO
We keep sharing the secrets on how SugarCRM can make your work faster and easier. In our previous materials, we explained how to quickly figure out what important events have happened. Today we are going to deal with another interesting issue “what important thing has NOT happened?”.
Chief Sales Officers want to know which deals are not pushed forward and which leads are untended. Chief Support Officers want to know which cases are pending. Account managers want to know which customer they have not called for a long time.
A simple list like this allows you to both save time and keep track of what is important.
With TimeLine Viewer add-on you will have no trouble dealing with report generation in Sugar Enterprise or Sugar Ultimate.
Below I will show you how to generate a report step by step using the following task as an example: “As the Head of Sales department I want to see all Leads which didn’t see any activities over the last week”. You can use it to create your own options adjusting data selection criteria to specific needs.
Step 1. Creation of a Custom Query
Note: You must be an administrator in order to create, edit and delete custom queries.
Let’s go to Advanced Reports and select Create Custom Query. Specify a clear Query Name – for example, Active Leads without events for the last 7 days. In Custom Query indicate the query which selects the required records, i.e. Leads in the Status neither Dead nor Converted, which don’t have any linked events in Timeline over the last 7 days. Let’s output in the report the name of Lead, its Status and its Assigned Manager, as well as the last activity/event on this Lead. Such a Query will look like this:
tlv2.parent_id = leads.id
AND tlv2.parent_type = 'Leads'
tlv3.parent_id = leads.id
AND tlv3.parent_type = 'Leads'
(leads.assigned_user_id = u.id)
leads.id NOT IN (
tlv.parent_id IS NOT NULL
tlv.action_date >= DATE_SUB(CURRENT_DATE (), INTERVAL 7 DAY)
tlv.parent_type = 'Leads'
leads.status not in ('Dead', 'Converted', 'Recycled')
In Sugar interface your record will look like this:
Let’s save the Custom Query record.
Step 2. Create Data Format
The next step is formatting the report so that its output is clear and user-friendly – change the names of columns (for example user.user_name is unlikely to be clear for a user, however Assigned Manager will be clear for everyone), font, background etc. To read more about data formatting you can go to Vendor’s website.
Let’s enter Data Format and select Query Name created in the previous step. Also tick Exportable and set Font Size to Default.
Let’s save the record. Once the record is saved, we see how the data is displayed in the report. You can leave it as is, but it would be better to give clear names and highlight the main data.
Here you can see how to format data displayed in the report.
Now our report is much clearer for an end-user.
Step 3: Create a report
The last step is to create a report. Enter the name of the report and, if necessary, indicate the description and Title. Let’s save the record.
Next open the report and Data Format and click Select to choose Data Format created in the previous stage:
That’s it. The report is ready.
Now the Chief Sales Officer just in 2 clicks gets the list of all Leads which didn’t see activities/events for the last 7 days – the task is done.
You can extend the current report, for example by adding one more Custom Query with data on Accounts which didn’t see activities/events for the last N days.
Want to try TimeLine Viewer? Leave us a request and we’ll be happy to give you access to the demo-environment.
WEBINAR: Productivity Enhancement for Every CRM User with Minimum Effort and Maximum Results
Find out how to be productive wherever you are with regular add-ons such as Customer Journey, Hint, iReports and TimeLine Viewer.
WEBINAR:A Smart Way to Collect Key Business Events in SugarCRM for BI and AI
Find out how you can customize TimeLine Viewer add-on for your specific needs and be able to collect data for BI and AI easily.
Other articles from “SugarCRM tips” series:
- SugarCRM Tips. How to Save 50% of Your Time When Preparing for Communication with the Customer
- SugarCRM Tips. How to Boost the Efficiency of Portfolio Management
- SugarCRM Tips. How to Enhance Efficiency of Manager’s Work
- SugarCRM Tips. How to Improve Team Selling
- SugarCRM Tips. Creating the Reports Which Help to Quickly Figure Out What Happened