Customer Profitability Report to include Cost of Sales Component in Vendor Bills where Billable to Customers = T

-The Customer Profitability report cannot display the related cost of sales component for vendor purchases that are billable to the customer.

-As an alternate solution, we may create a custom saved search that will pull up all related Income and Cost of Goods Sold balances that grouped based on the customer entity associated in the recorded transactions within the system. 

Here are the steps to create the Saved Search:
1. Navigate to Reports tab > Saved Searches > All Saved Searches > New.
2. Select Search Type = Transaction.
3. Navigate to 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 Customer 

4. Navigate to Results tab: Sort by field > Select from the dropdown list the Name field.
5. Navigate to 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) | Custom Label: Gross Profit | Summary Label: Gross Profit

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

6. 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.

7. Hit Save & Run button

Note:

a. Remove the criteria filter set for: Customer/Project Fields…>Entity Type = is Customer should user 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