To create a Sales report which displays the
total amount and columns to display due dates during alternate date ranges, a
user can create the following Saved search:
1. Navigate
to Reports > Saved Searches > All Saved
Searches > New
2. Click on Transaction link
3. Under Criteria tab
> Standard subtab
Type = Invoice
Main Line = True
Due Date = Not Before Today
4. Under the Results tab
> Columns subtab
Field
|
Formula
|
Custom Label
|
Number
|
||
Name
|
||
Amount (Transaction Total)
|
||
Formula (Numeric)
|
CASE WHEN {duedate}-{today} BETWEEN 0.01 AND
3 AND (to_char({duedate}, 'MM'))-(to_char({today}, 'MM')) = 0 then {duedate}
ELSE null END
|
This Month - Upto 3 Days from Today
|
Formula (Numeric)
|
CASE WHEN {duedate}-{today} BETWEEN 4 AND 7
AND (to_char({duedate}, 'MM'))-(to_char({today}, 'MM')) = 0 then {duedate}
ELSE null END
|
This Month - Days 4 -7 from Today
|
Formula (Numeric)
|
CASE WHEN {duedate}-{today} > 8 AND
(to_char({duedate}, 'MM'))-(to_char({today}, 'MM')) = 0 AND
(to_char({duedate}, 'YYYY'))-(to_char({today}, 'YYYY')) = 0 then {duedate}
ELSE null END
|
This Month - Over 8 Days from Today
|
Formula (Numeric)
|
CASE WHEN (to_char({duedate}, 'MM'))-(to_char({today},
'MM')) = 1 AND (to_char({duedate}, 'YYYY'))-(to_char({today}, 'YYYY')) = 0
then {duedate} else null END
|
Next Month
|
Formula (Numeric)
|
CASE WHEN (to_char({duedate},
'MM'))-(to_char({today}, 'MM')) <> 0 then {duedate} else null END
|
2 Months and Onwards
|
5. Under the Available Filters tab:
Filter = Status > Show in Filter
Region = Yes
6. Rename the Search Title
7. Hit Save & Run
