Friday, April 5, 2019

Creating a Saved Search to Display Purchase Price and Landed Cost per Category in Columns

If a user wants to create a report to show Transaction#, Vendor, Item, Amount (Purchase Price) and Amount columns for each Cost Category, then you can start with this Saved Search:

Assume that you have Cost Categories: Shipping, Insurance and Distribution

1.    Navigate to Transactions>Management>Saved Searches>New

2.    Select Transaction

3.    On the Criteria tab add Account Type = Other Current Asset, Type = Item Receipt and Memo does not contain Cost of Sales Adjustments

4.    On the Results tab add the following fields:

5.    Document Number (Summary Type = Group)

6.    Main Line Name (Summary Type = Group, Custom Label = Vendor)

7.    Item (Summary Type = Group)

8.    Formula(Currency) = DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo}, ':')-1), 'Cost'),'Cost',{amount}) (Summary Type = Sum, Custom Label = Purchase Price, Summary Label = Purchase Price)

9.    Formula(Currency) = DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo}, ':')-1), 'Cost'),'Shipping',{amount}) (Summary Type = Sum, Custom Label = Shipping, Summary Label = Shipping)

10.  Formula(Currency) = DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo}, ':')-1), 'Cost'),'Insurance',{amount}) (Summary Type = Sum, Custom Label = Insurance, Summary Label = Insurance)

11.  Formula(Currency) = DECODE(NVL(SUBSTR({memo}, 0, INSTR({memo}, ':')-1), 'Cost'),'Distribution',{amount}) (Summary Type = Sum, Custom Label = Distribution, Summary Label = Distribution)

12.  Hit Preview or Save and Run

NOTE: You need to replace 'Shipping',  'Insurance' and 'Distribution' with your own Cost Category (e.g. 'Freight', etc.)

No comments:

Post a Comment