How to generate a multi-year sales comparisons report for your customers?

Overview

This article describes how to generate a "BEST Customers" internal report for a number of years in a side by side comparison format.

Information

For the time being, this report is not readily available in Everest. However, it can be generated by the following steps:

1. Run the following query either by using the Advanced Query Builder or by using SQL Server Management Studio:

   SELECT 
     i.cust_code,  c.name,
     year(order_date) AS yr,
     sum(inv_amount) AS amt
   FROM
     invoices i WITH (nolock)
     INNER JOIN cust c WITH (NOLOCK) ON (i.cust_Code = c.cust_code)
   WHERE
     status = 9
     and year(order_Date) >= {start_year}
     and year(order_Date) <= {end_year}
   GROUP BY
     i.cust_code,  c.name,
     year(order_date)

   This query will allow you to retrieve the needed data. Replace the following tokens with the appropriate values for your use case:

   1. {start_year} - the start year for the report
   2. {end_year} - the end year for the report
2. Copy this data and paste it into an Excel Spreadsheet
3. Use Excel to create a PivotTable, to analyze the data.