Overview
The Advanced Query Builder (AQB) is a built-in utility within Everest used for building custom reports that can be assigned to a user.
This article explains how to use the AQB utility to generate a Customer List report with customizable columns.
Solution
AQB allows direct interaction with the database through SQL commands, thus providing the option of creating custom reports.
Queries can be saved in the Everest database and then used when needed without having to rewrite the query.
Follow these steps to build a Customer List report with the required columns using the Advanced Query Builder:
- Log in to the Everest platform and select the Advanced Query Builder option from the Utility menu.
- This will open up the Advanced Query Builder window:
- Click the SQL Statement tab.
- Paste the SQL query into the text field.
SELECT A.ACTIVE, C.CURRCODE AS CURR_CODE, A.CUST_CODE, C.SALESPERSO, LEFT(CAST(C.MDESC AS varchar(8000)), 8000) AS Notes,
C.CONTCODE, A.ADDR_CODE, A.ACCT_NAME AS ACCOUNT_NAME, C.DEP_CODE, A.FIRST_NAME, A.LAST_NAME, ISNULL(A.TEL1, '') + ISNULL(A.EXTN_TEL1, '')
AS ETEL1, ISNULL(A.TEL2, '') + ISNULL(A.EXTN_TEL2, '') AS ETEL2, ISNULL(A.FAX, '') + ISNULL(A.EXTN_FAX, '') AS EFAX, A.EMAIL,
A.STREET_ADDRESS, A.CITY,
A.STATE, A.COUNTRY, CONVERT(DATETIME, CONVERT(CHAR(10), C.CREAT_DATE, 101)) AS CREAT_DATE, A.ZIP, A.TYPE, C.SHIPCODE, A.ADDR_CODE AS Expr1,
C.HAND_OFF, C.CURRCODE, (C.CUR_DR - C.CUR_CR) + (C.UNPOST_DR - C.UNPOST_CR) AS BASENETBALANCE, (C.CUR_DR_FEX - C.CUR_CR_FEX)
+ (C.UNPOSTDRFEX - C.UNPOSTCRFEX) AS FRXNETBALANCE, C.REQUIREPO, A.ADDRESS_LINE1, A.ADDRESS_LINE2, A.FULL_ADDRESS, C.BILLCODE,
ADR2.FULL_ADDRESS AS SHIPPING_ADDRESS, A.JOB_TITLE AS TITLE, C.CATEGORY_CODE, A.MOBILE_PHONE, A.PAGER_NO, A.URL, ISNULL(A.TEL1, '') AS TEL1,
ISNULL(A.EXTN_FAX, '') AS EXTN_FAX, A.NAME AS COMPANY_NAME, C.CR_LIMIT_FEX
FROM
ADDRESS AS A WITH (NOLOCK) INNER JOIN
CUST AS C WITH (NOLOCK) ON A.ADDR_CODE = C.BILLCODE LEFT OUTER JOIN
ADDRESS AS ADR2 WITH (NOLOCK) ON C.SHIPCODE = ADR2.ADDR_CODE
WHERE (A.ACTIVE = 'T') and c.ACTIVE = 'T'
ORDER BY C.CUST_CODENote: This sample query can be customized as required to add or remove columns from theADDRESS
table. - Verify the query by pressing the Update SQL button.
- Select the Primary Key by doing the following:
- Open the Browser Settings tab
- Select the
CUST_CODE
as the primary key from the Primary Key drop-down menu. This Primary Key will be used to sort the filter results. - Enter the report name into the Filter Name field.
- Click on the Save button.
- Press the Find button to run the report you have created. Enter the values when prompted and press OK. The report will then be generated.
Testing
The Customer List report should be visible and showing all the selected columns. The report can be exported or printed as desired.
Comments
0 comments
Please sign in to leave a comment.