Tuesday, May 21, 2019

Saved Search to Combine Two "Case when Statements" in Criteria


1. Navigate to Reports>Saved Searches>All Saved Searches>New

2. Click Transaction.

3. Modify Search Title.

4. On the Criteria tab >Standard subtab> Set the following Filters:

- Type = Sales Order

- Main Line = True

5. Still under Criteria tab  >Standard subtab> insert Formula (Numeric) field.
- For the pop-up window:
a) Copy paste the formula below in the Formula field.

CASE WHEN {shipdate} BETWEEN to_date ('7/01/2013', 'mm/dd/yyyy') AND to_date ('7/31/2013', 'mm/dd/yyyy') THEN (case when {status} = 'Pending Fulfillment' then ({amount}) else 0 end) ELSE 0 END

b) Click on the Set button.

6. On the Results tab >Columns subtab> there are fields defaulted. Add or remove fields as needed.

7. Click Save & Run.

Note: For this example, dates cited are July 1 and July 31.

No comments:

Post a Comment