Saturday, June 1, 2019

Create a Sales Order Search that shows each date when a order is created and will display "0"

To create a Sales Order search that will display number of sales order created each day and will display "0" if a particular date has none. Please follow the steps below:

1. Navigate to Lists > Search > Saved Searches > New
2. Select Transaction
3. On the Criteria tab and standard sub-tab add a filter called Type and select Sales Order on the pop up window
4. Add another filter called Date and in the pop up select Within on the Date field and select Previous one month on the dropdown
5. Add another filter called Main Line and in the pop select Yes
6. Click on the Results tab and on the Column subtab add the following:

 
Field= Formula (Text)

Summary Type= Count

Formula= Decode(to_char({trandate}, 'DD Mon YYYY'),  ('01 ' || {postingperiod}),{number}) Day 1

Custom Label= Day 1

Note: You can add the same formula below:

Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('02 ' || {postingperiod}),{number}) Day 2 Day 2
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('03 ' || {postingperiod}),{number}) Day 3 Day 3
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('04 ' || {postingperiod}),{number}) Day 4 Day 4
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('05 ' || {postingperiod}),{number}) Day 5 Day 5
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('06 ' || {postingperiod}),{number}) Day 6 Day 6
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('07 ' || {postingperiod}),{number}) Day 7 Day 7
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('08 ' || {postingperiod}),{number}) Day 8 Day 8
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('09 ' || {postingperiod}),{number}) Day 9 Day 9
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('10 ' || {postingperiod}),{number}) Day 10 Day 10
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('11 ' || {postingperiod}),{number}) Day 11 Day 11
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('12 ' || {postingperiod}),{number}) Day 12 Day 12
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('13 ' || {postingperiod}),{number}) Day 13 Day 13
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('14 ' || {postingperiod}),{number}) Day 14 Day 14
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('15 ' || {postingperiod}),{number}) Day 15 Day 15
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('16 ' || {postingperiod}),{number}) Day 16 Day 16
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('17 ' || {postingperiod}),{number}) Day 17 Day 17
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('18 ' || {postingperiod}),{number}) Day 18 Day 18
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('19 ' || {postingperiod}),{number}) Day 19 Day 19
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('20 ' || {postingperiod}),{number}) Day 20 Day 20
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('21 ' || {postingperiod}),{number}) Day 21 Day 21
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('22 ' || {postingperiod}),{number}) Day 22 Day 22
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('23 ' || {postingperiod}),{number}) Day 23 Day 23
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('24 ' || {postingperiod}),{number}) Day 24 Day 24
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('25 ' || {postingperiod}),{number}) Day 25 Day 25
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('26 ' || {postingperiod}),{number}) Day 26 Day 26
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('27 ' || {postingperiod}),{number}) Day 27 Day 27
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('28 ' || {postingperiod}),{number}) Day 28 Day 28
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('29 ' || {postingperiod}),{number}) Day 29 Day 29
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('30 ' || {postingperiod}),{number}) Day 30 Day 30
Formula (Text) Count Decode(to_char({trandate}, 'DD Mon YYYY'),  ('31 ' || {postingperiod}),{number}) Day 31 Day 31
Number Count   Total Total
        

Please set the summary type to Group

7. Set Date and Period under Available Filters tab:

* Date || Show in Footer = Yes
* Period || Show in Footer = Yes

8. Enter name in the Search Title field
9. Click Save & Run

No comments:

Post a Comment