CRM Software - Custom Report - Quotes not Invoiced |
|
|
|
|||
Home Page Site map CRM Software FAQ Import Customers from Spreadsheet Contact Database Share Customer Data between PCs Download FREE CRM Software 30 Day Trial
In Amphis Customer there are many Built-in Reports and you can also create Custom Reports to suit your business requirements. This is an example Custom Report you can use to show customers who have more quotes than invoices in their customer history. This would be a good indication that there are quotes for this customer that have not been invoiced.
You can create a custom report using the Custom Report Builder (from the Reports->Custom Report Builder menu)
For more information on how to create a custom report, see Create Custom Reports
Here is the SQL command you need to enter when creating this custom report. (Don't worry if you have no idea what this means - you just need to copy and paste this into the Report Builder).
SELECT displayid as CustomerId, CompanyName, LastName, FirstName, (select count(*) from history where history.customerid=customer.customerid and history.what='quote') as quotes, (select count(*) from history where history.customerid=customer.customerid and history.what='invoice') as invoices FROM customer where (select count(*) from history where history.customerid=customer.customerid and history.what='quote') > (select count(*) from history where history.customerid=customer.customerid and history.what='invoice')
And here is an example output from the report showing customers where there are more Quotes than Invoices, and also the number of Quotes and Invoices.
You can click View Customer to display a customer record, or export the results to a spreadsheet, or add all the customers to a group so that you can Mail Merge to send them all a letter. Or you can just print the results.
Also, you might want to try this one... which shows customers where the total price of the quotes for the customer is greater than the total price of the invoices for the customer. Note that the SQL contain the number 90 (4 times) which restricts the report to the last 3 months (roughly) of quotes and invoices.
SELECT displayid as CustomerId, CompanyName, LastName, FirstName, (select sum(total) from history where history.customerid=customer.customerid and history.what='quote') as quotes, (select sum(total) from history where history.customerid=customer.customerid and history.what='invoice') as invoices FROM customer where (select sum(total) from history where history.customerid=customer.customerid and history.what='quote' AND history.displaydate > DATE()-90 ) > (select sum(total) from history where history.customerid=customer.customerid and history.what='invoice' AND history.displaydate > DATE()-90 ) UNION SELECT displayid as CustomerId, CompanyName, LastName, FirstName, (select sum(total) from history where history.customerid=customer.customerid and history.what='quote') as quotes, 0 FROM customer where not exists (select * from history where history.customerid=customer.customerid and history.what='invoice' AND history.displaydate > DATE()-90 ) and exists (select * from history where history.customerid=customer.customerid and history.what='quote' AND history.displaydate > DATE()-90 )
(You can also get some free help to create a CRM Custom Report in Amphis Customer, by clicking here)
Disclaimer: we develop custom reports at no charge, often at the request of clients. We cannot guarantee that these custom reports will produce exactly the results you want to achieve, but we try to develop custom reports that could be useful for the majority of our clients.
Home Page Site map CRM Software FAQ Import Customers from Spreadsheet Contact Database Share Customer Data between PCs Download FREE CRM Software 30 Day Trial
