Saved Search to show Gross Profit by Customer

Create a saved search to show Gross Profit by Customer.

1. Navigate to Lists > Search > Saved Searches > New
2. Click Transaction
3. Search Title: Enter Title
4. Click Criteria tab > Standard subtab
5. Filter:

• Select Account Type
• Account Type: Select Other Income, Income, Cost of Goods Sold
• Click Set
• Select Posting
• Posting: Select Yes
• Click Set
• Select Date
• Date: Enter Date Range
• Click Set

1. Click Results tab
2. Show Totals: Enter Checkmark
3. Click Columns subtab
4. Field:

• Select Customer Fields…
• Customer Field: Select Name
• Summary Type: Select Group
• Select Type
• Select Document Number
• Select Formula (Numeric)
• Formula: Enter CASE WHEN {?accounttype} = ‘Cost of Goods Sold’ THEN {?amount} ELSE 0 END
• Summary Type: Select Sum
• Function: Select Round to Hundredths
• Summary Label: Enter Total Cost
• Select Formula (Numeric)
• Formula: Enter CASE WHEN {?accounttype} = ‘Income’ THEN {?amount} ELSE 0 END
• Summary Type: Select Sum
• Function: Select Round to Hundredths
• Summary Label: Enter Total Income
• Select Formula (Numeric)
• Formula: Enter CASE WHEN {?accounttype} = ‘Income’ THEN {?amount} ELSE 0 END – CASE WHEN {?accounttype} = ‘Cost of Goods Sold’ THEN {?amount} ELSE 0 END
• Summary Type: Select Sum
• Function: Select Round to Hundredths
• Summary Label: Enter Total Gross Profit
• Select Formula (Percent)
• Formula: Enter sum(case when {?accounttype} = ‘Income’ then {?amount} else 0 end – case when {?accounttype} = ‘Cost of Goods Sold’ then {?amount} else 0 end) / NULLIF(sum(case when {?accounttype} = ‘Income’ then {?amount} end),0)
• Summary Type: Select Sum
• Summary Label: Enter Gross Margin

1. Sort by: Select Customer Fields…

• Customer Field: Select Name

1. Click Save & Run