Wednesday, December 19, 2018

Saved Search to show Quantity Sold for an Item, whether it was sold individually or as a Member of an Assembly or Kit Item


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

2. Click Transaction.

3. Criteria Tab > Standard sub tab:


    Type = Cash Refund, Cash Sale, Credit Memo, Invoice

    Main Line = No

    Shipping Line = No

    Tax Line = No

    Account Type = Income


4. Result tab> Columns sub tab:

    Add Formula (Text):

     a. Set Summary type = Group

     b. Set Formula = DECODE({item.memberitem},NULL,{item},{item.memberitem})

     c. Custom Label = Item Name


    Add Formula (Numeric):

     a. Set Summary type = Sum

     b. Set Formula = DECODE({item.memberquantity},NULL,{quantity},{quantity}*{item.memberquantity})

     c. Custom Label = Total Item Quantity


   Add Formula (Numeric):

     a. set Summary type = Sum

     b. set Formula = CASE WHEN (TO_CHAR({trandate},'MON YYYY') = 'AUG 2012') THEN DECODE({item.memberquantity},NULL,{quantity},{quantity}*{item.memberquantity}) ELSE 0 END

     c. Custom Label = Total Item Quantity Per Month


Note: This formula is for August 2012. For September 2012, another Formula (Numeric) should be added with AUG 2012 to be replaced with SEP 2012.


5. Rename the Search Title.

6. Click Save & Run.


No comments:

Post a Comment