Create Income and COGS by Sales Rep Saved Search

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

2.Click Transaction.

3.On the Criteria tab > Standard subtab > Filter column. Add the following fields:

a. Account Type = is any of Cost of Goods Sold, Income

b. Posting = is true

c. Customer/Project Fields…> Entity Type = is Sales Rep

4.On the Results tab > Sort by field > Select from the dropdown list the Name field

5.On the Results tab > Columns subtab > Field column. Add the following fields:

a. Date
b. Type
c. Account
d. Name | Summary Type: Group
e. Amount (Gross)
f. Formula (Currency) | Summary Type: Sum | Formula: case when {accounttype} = 'Income' then {grossamount} else 0 end | Custom Label: Revenue | Summary Label: Revenue
g. Formula (Currency) | Summary Type: Sum | Formula: case when {accounttype} = 'Cost of Goods Sold' then {grossamount} else 0 end | Custom Label: Cost of Sales | Summary Label: Cost of Sales

h. Formula (Currency) | Summary Type: Sum| Formula(case when{accounttype} = 'Income' then {grossamount} else 0 end) - (case when{accounttype} = 'Cost of Goods Sold' then {grossamount} else 0 end)

 

i. Formula (Percent) | Summary Type: Average | Formula: round(sum((case when{accounttype} = 'Income' then {grossamount} else 0 end) - (case when{accounttype} = 'Cost of Goods Sold' then {grossamount} else 0end))/nullif(sum(case when {accounttype} = 'Income' then nvl({grossamount},0)else 0 end),0),4)

-Navigate to Available Filters tab > Filter column> Select the Date field from the drop down list and mark the box under the Show in Footer column.

6.Click Save & Run button

Note:

a. Remove the criteria filter set for: Customer/Project Fields…>Entity Type = is Customer should you need to display the total balance in the report equal to that of your income statement balance for sales. The discrepancy signifies that there are transactions recorded that impact sales accounts but are either tied up to a different entity record other than the customer (Example: Employees, Vendors, Partner records etc.) or no entity type was associated at all. Transactions that are not associated to a certain entity type will be grouped under: -None-

b. If the Saved Search takes too long to pull up the results; you can set a filter under the Criteria tab to specify a shorter date range