Custom Report: Sales by Ship-To-State

Overview

By default, there is no report to obtain the sales data by Ship-to-state.

However, the AQB (Advanced Query Builder) can be used to create a custom report showing Sales by Ship-To State for the specified time period.

Solution

First, create a custom filter to retrieve the Sales Invoices list with the shipping state. Follow steps 1 to 6 of the Building a Custom Report using Advanced Query Builder SQL Statements article, using the following SQL query:

SELECT 
  i.cust_code,  c.name,  i.order_no,  i.INV_AMOUNT,  i.TAX_AMOUNT,
  a.state AS ship_state,  a.COUNTRY,
  a2.state AS bill_state,  a2.COUNTRY AS bill_country
FROM
  invoices i WITH (nolock)
  INNER JOIN cust c WITH (NOLOCK) ON (i.cust_Code = c.CUST_CODE)
  LEFT OUTER JOIN address a WITH (NOLOCK) ON (i.ship_code = a.addr_code)
  LEFT OUTER JOIN address a2 WITH (NOLOCK) ON (i.bill_code = a2.addr_code)
WHERE
  i.status = 9 AND 
  i.ORDER_DATE >= :startDate AND 
  i.order_date <= :endDate

After the filter has been created you can directly execute the report by following these steps:

1. In the main Everest window, navigate to the Advanced Filters option under the "Filters" bar.
   
2. Select the Filter that you created in the previous steps.
3. Enter the desired date ranges.
4. Click OK.

Back to top