Custom Reports | Sales by State by Billing Address

Overview

This article provides the AQB (Advanced Query Builder) query that can be used to pull up a custom report showing Sales by State by Billing Address.

Information

The following query can be used to create a custom report in the Advanced Query Builder showing Sales by State by Billing Address:

SELECT 
  a.state,  c.cust_Code,  c.name,
  sum(i.INV_AMOUNT) AS sales_amount
FROM
  invoices i  WITH (NOLOCK) INNER JOIN address a WITH (NOLOCK)
 ON (i.bill_code = a.addr_code)
  INNER JOIN cust c WITH (NOLOCK)
 ON (c.CUST_CODE = i.cust_code)
WHERE
  i.status = 9 AND 
  i.order_date >= 01/01/2016 AND 
  i.order_date <= 12/31/2016
GROUP BY
  a.state,  c.cust_Code,  c.name

Follow these steps to create the custom report based on the above query:

1. Under Utility menu | Advanced Query Builder.
2. Click on SQL statement tab | paste the above query
3. Click on update SQL.
4. Select browser setting tab | under primary key | click on the drop-down and select c.cust_code
5. Under the filters tab - edit the start date and end date according to your requirement.
6. Clear the default values in the AQB's Parameter Information tab:
   
7. Uncheck 'Remember Parameter Values' so it would prompt for the given parameter:
   

Refer to Building a Custom Report using Advanced Query Builder SQL Statements for more information on how to use the AQB to create custom reports.

Back to top