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.
The following query can be used to create a custom report in the Advanced Query Builder showing Sales by State by Billing Address:
a.state, c.cust_Code, c.name,
sum(i.INV_AMOUNT) AS sales_amount
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)
i.status = 9 AND
i.order_date >= 01/01/2016 AND
i.order_date <= 12/31/2016
a.state, c.cust_Code, c.name
Follow these steps to create the custom report based on the above query:
- Under Utility menu | Advanced Query Builder.
- Click on SQL statement tab | paste the above query
- Click on update SQL.
- Select browser setting tab | under primary key | click on the drop-down and select
- Under the filters tab - edit the start date and end date according to your requirement.
- Clear the default values in the AQB's Parameter Information tab:
- 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.