Scenario
Create a report that will display the total number of items sold whether or not they were sold individually or part of a kit or assembly with additional columns that shows item sold per month.
Solution
1. Navigate to Reports > Saved Searches > All Saved Searches > New
2. Click Transaction
3. Search Title : Enter Title
4. Click Criteria tab
5. Click Standard subtab
6. Filter:
Select Type
1. Type : Select Cash Refund, Cash Sale, Credit Memo, Invoice
2. Click Set
Select Main Line
1. Main Line : Select False
2. Click Set
Select Shipping Line
1. Shipping Line : Select False
2. Click Set
Select Tax Line
1. Tax Line : Select False
2. Click Set
Select Account Type
1. Account Type : Select Income
2. Click Set
7. Click Results tab
8. Click Columns subtab
9. Fields:
Select Formula (Text)
1. Summary Type : Select Group
2. Formula : DECODE({item.memberitem},NULL,{item},{item.memberitem})
3. Custom Label : Item Name
Select Formula (Numeric)
1. Summary Type : Select Sum
2. Formula : DECODE({item.memberquantity},NULL,{quantity},{quantity}*{item.memberquantity})
3. Custom Label : Total Item Quantity
Select Formula (Numeric)
1. Summary Type : Select Sum
2. Formula : CASE WHEN (TO_CHAR({trandate},’MON YYYY’) = ‘AUG 2022’) THEN DECODE({item.memberquantity},NULL,{quantity},{quantity}*{item.memberquantity}) ELSE 0 END
3. Custom Label : Total Item Quantity Per Month
Note: This formula is for August 2022. For September 2022, another Formula (Numeric) should be added with AUG 2022 to be replaced with SEP 2022.
10. Click Save