Overview

This article explains how to generate two types of Advanced Query Builder (AQB) reports: one that provides a detailed breakdown of sales invoices for customers in a specific customer class and another that summarizes total sales invoices for customers during the current quarter. Both reports are based on SQL queries and can be refined using date filters within the Everest browser window once the AQB filter is activated.

Solution

Government Sales Report

This report offers a detailed breakdown of sales invoices for a specific customer class over a custom time period.

• Use the following SQL query as a base:

SELECT
  i.doc_no AS DOCUMENT_NUMBER,
  i.order_date AS INV_ORDER_DATE,
  i.cust_code AS CUSTOMER_CODE,
  c.name AS CUSTOMER_NAME,
  i.taxable AS INV_TAXABLE,
  i.tax_amount AS INV_TAX_AMOUNT,
  i.exempt AS INV_TAX_EXEMPT,
  i.inv_amount AS INVOICE_AMOUNT,
  i.paid_amt AS PAID_AMOUNT,
  i.po AS INV_PO
FROM
  invoices i
INNER JOIN cust c WITH (NOLOCK) ON (c.cust_code = i.cust_code)
WHERE
  i.cust_code IN (SELECT cust.cust_code FROM cust WITH (NOLOCK) WHERE custclass = 'CUSTOMER_CLASS_PLACEHOLDER')
  AND i.status = 9
ORDER BY
  i.doc_no

Instructions:

1. Replace 'CUSTOMER_CLASS_PLACEHOLDER' with the customer class name used in your system.
2. After activating the AQB filter, the Everest browser window will open. You can apply custom date filters within this interface to narrow down the report to a specific time frame, if needed.
3. This report will generate a detailed breakdown of sales invoices, including document numbers, invoice dates, customer codes, and amounts for each invoice.

Quarterly Government Sales Report

This report aggregates the total invoice amounts for customers in a specified class for the current quarter. The report does not require additional date filtering, as it is already structured to cover the ongoing quarter.

• Use the following SQL query as a base:

SELECT
  i.cust_code AS CUSTOMER_CODE,
  c.name AS CUSTOMER_NAME,
  SUM(i.inv_amount) AS INVOICE_AMOUNT
FROM
  invoices i
INNER JOIN cust c WITH (NOLOCK) ON (c.cust_code = i.cust_code)
WHERE
  i.cust_code IN (SELECT cust.cust_code FROM cust WITH (NOLOCK) WHERE custclass = 'CUSTOMER_CLASS_PLACEHOLDER')
  AND i.order_date >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
  AND i.order_date < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0)
  AND i.status = 9
GROUP BY
  i.cust_code,
  c.name
ORDER BY
  c.name

Instructions:

1. Replace 'CUSTOMER_CLASS_PLACEHOLDER' with the appropriate customer class name in your system.
2. This query is designed to generate the total invoice amount for each customer in the specified customer class within the current quarter.
3. The results are grouped by customer name, providing an overview of total sales per customer.

Reference Documentation:

• For more details on using AQB with SQL statements, refer to the guide on Building a Custom Report using Advanced Query Builder.

<supportagent>

https://central-supportdesk.zendesk.com/agent/tickets/4457523

</supportagent>