Saturday, July 6, 2019

Create a Saved Search to Display the Total Sales of Items Grouped by First Level Parent Items

Steps to create a transaction Saved Search to display the total sales (amount) for items, grouped by the first level parent item.

For example:

- Sales:

Bedroom : Handcrafted Queen bed - Total Sales $ 2,000.00
Bedroom : Platform Bed : Yao Bed - Total Sales $ 1,000.00
Carpet : Berber Carpet - Total Sales: $ 500.00

- Search Results:

Item        | Total Sales
Bedroom  | 3.000,00
Carpet     | 500.00

1. Navigate to Lists > Search > Saved Searches > New.
2. Select Transaction.
3. Under the Criteria tab > Standard subtab add two filters:

  • Main Line is False.
  • Type is any of Invoice, Cash Sale.

4. Under the Results tab > Columns subtab add two fields:

  • Field = Formula (Text).
    Formula = CASE WHEN INSTR({item}, ':') > 0 THEN SUBSTR( {item},0, INSTR({item}, ':')-1 ) ELSE {item} END        
    Summary Type = Group.
  • Field = Amount.
    Summary Type = Sum.

5. Click Save.

No comments:

Post a Comment