Scenario :
The Customer Profitability Report in NetSuite do not source values from transaction records. Hence, it is sourcing from the entity (customer) record. As a result, when there is a need for the customer to change the Sales Rep on the entity record, the results to be generated by the report will only include the current Sales Rep as per the Customer Record. The customer is then wondering if there is a way to make the report source from transaction records instead of the entity record.
A Saved Search version of the report may be created. However, limitations such as the inability of the Saved Search to pull up the Cost when Type = Sales Order raised the need to create two Saved Searches for the purpose.
1. For Invoice/Cash Sale (with Item Fulfillment) created from Sales Order
2. For Stand-alone Invoice/Cash Sale
Solution :
To create a Transaction Saved Search version of Customer Profitability Report for all Cash Sale/Invoice created from Sales Order (Status = Billed)
- Navigate to Reports > Saved Searches > All Saved Searches > New
- Click Transaction
- Search Title: Enter Title
- Click Criteria
- Click Standard
- Filters:
- Type: is any of Sales Order, Item Fulfillment
- Main Line: is false
- Account Type: is Cost of Goods Sold
- Created From: Status: is sales Order: Billed
Note: Always click Add once Filters are selected and adjusted as needed.
- Click Results
- Click Columns
Fields: - Select Created From
- Customer Label: Enter Sales Order
- Summary Type: Group
- Select Document Number
- Summary Type: Group
- Customer Label: Enter Item Fulfillment
- Select Amount
- Summary Type: Sum
- Customer Label: Enter Total Cost
- Select Created From Fields…Amount
- Summary Type: Sum
- Customer Label: Enter Total Revenue
- Select Foreign (Currency)
- Formula: {createdfrom.amount} - {amount}
- Customer Label: Enter Profit
- Click Save & Run
- Navigate to Reports > Saved Searches > All Saved Searches > New
- Click Transaction
- Search Title: Enter Title
- Click Criteria
- Click Standard
- Filters:
- Posting: is True
Type: is any of Cash Sale, Invoice - Created From Fields...Type: is none of Vendor Return autorization, Sales Order, Transfer order
- Tax Line: is false
Note: Always click Add once Filters are selected and adjusted as needed.
- Posting: is True
- Click Results
- Click Columns
- Fields:
- Select Internal ID
- Summary Type: Group
- Select Name
- Summary Type: Group
- Custom Label: Customer
- Select Sales Rep
- Summary Type: Group
- Select Type
- Summary Type: Group
- Select Formula (Currency)
- Summary Type: Sum
- Formula: case when {accounttype} = 'Income' THEN {amount} ELSE 0 END
- Customer Label: Enter Total Revenue
- Select Formula (Currency)
- Summary Type: Sum
- Formula: case when {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END
- Customer Label: Enter Total Cost
- Select Formula (Currency)
- Summary Type: Sum
- Formula: (case when {accounttype} = 'Income' THEN {amount} ELSE 0 END)-(case when {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END)
- Customer Label: Total Profit
Note: Always click Add once Filters are selected and adjusted as needed.
- Click Save & Run