Overview

As mentioned in Advanced Query Builder guide, a customer's demand for custom reporting can be fulfilled using Advanced Query Builder with SQL statements. The steps below represent a use case you will find useful while working on a custom report with an SQL statement.

Requirements

An inventory report based on the part number value. The client is mainly using the vendor part number in their daily processes. Include the following fields: item code, part number, item description, bin location, bin description, bin stock, total available stock.

Step-By-Step Guide

1. Login to the Everest platform.
2. Open Utility menu.
   
3. Click Advanced Query Builder to open Builder application.
   

   

4. Open SQL Statement tab.
   

   

5. Paste your SQL query into the text field. In our example, it will be the following query:
   
   SELECT 
  i.itemno AS Item_Code,
  irv.VENDOR_PART_NO AS Part_Number,
  i.descript AS Description,
  x.area_code AS Bin,
  s.descript AS Bin_descript,
  x.q_stk AS Bin_stock,
  i.qty_stk - i.q_on_reser AS Available_stock
FROM
  items i
  INNER JOIN x_stk_area x WITH (NOLOCK) ON (i.ITEMNO = x.ITEM_NO)
  INNER JOIN ITEM_REPLENISH_VENDOR irv WITH (NOLOCK) ON (i.ITEMNO = irv.ITEM_CODE)
  INNER JOIN stk_area s WITH (NOLOCK) ON (x.area_code = s.area_code)
WHERE
  x.area_code = :areacode
GROUP BY
  i.itemno,
  x.AREA_CODE,
  s.descript,
  irv.VENDOR_PART_NO,
  i.descript,
  i.qty_stk,
  i.q_on_reser,
  x.q_stk,
  x.Q_ON_RESERVE_COMM

In order to accept inputs from the users, we use ':', followed by the field name e.g. ":areacode".
   
   
6. Press Update SQL button to verify the query (Note that it will further apply all modifications). 
   

   

7. Open Browser Settings tab.
8. Select available primary key from the Primary Key drop-down menu. It which will be used to sort out the filter results.
9. Input report name into the Filter Name field.
10. Press Save button.

Confirmation

1. Press Find button to run the report you have created. It will should prompt to enter input values.
   
2. Input value.
3. Press OK. The report should be generated.