Display Income & COGS Amount on a Project Search

To do this, please follow the steps below:

1. Navigate to Lists> Search> Saved Searches> New> Projects

2. On the Criteriatab> Standard subtab:

Use Expressions = T

Parens | Filter | Description |  Formula | Parens | And/Or

(  | Transaction: Account Type | is Income |  |  | Or

   | Transaction: Account Type | is Cost of Goods Sold |  | ) | And

   | Transaction: Posting | is true |  |  | 

3. On the Resultstab> Columns subtab:

Field | Summary Type | Function | Formula | When Orderd By Field | Custom Label

Name | Group

Transaction: Type |

Transaction: Number |

Transaction: Amount |

Formula (Currency) | Sum |  | Decode({transaction.accounttype},'Income',{transaction.amount},0.00) |  | Income

Formula (Currency) | Sum |  | Decode({transaction.accounttype},'Cost of Goods Sold',{transaction.amount},0.00)|  | COGS

4. Enter Search Title and click on Save & Run.

Note: The amount displayed on the search result is based on consolidated exchange rate. You have an option though to navigate to Home> Set Preferences> Restrict View> and specify your preferred subsidiary.

Alternatively, you can use this formula as well:

For now we do not have a complete profitability report wherein all transactions associated to a project are shown.  Below are the steps to make a search to find those transactions and make a profitability report for the project. 

1. Navigate to Reports>New Search.
2. Choose Project.
3. Under Criteria tab choose Transaction fields.  A new window will pop up.  Select Posting then set it to Yes.
4. Under Results tab, set the following Field:
   1. Name Customer
   2. Formula (Currency) then set the following expression on the Formula box: case when {transaction.accounttype} = 'Cost of Goods Sold' then {transaction.amount} when {transaction.accounttype} = 'Expense' then {transaction.amount} else 0.00 end

 The expression above will pull up the bills with account type using expense and COGS account

               3. Formula (Currency) then set the following expression on the Formula box: case when         {transaction.accounttype} = 'Income' then {transaction.amount} else 0.00 end

The expression above will be the revenue side.

              4. Formula (Currency) then set the following expression on the Formula box: case when {transaction.accounttype} = 'Cost of Goods Sold' then {transaction.amount} when {transaction.accounttype} = 'Expense' then {transaction.amount}*-1 when {transaction.accounttype} = 'Income' then {transaction.amount}else 0.00 end

This will represent the profit/loss of the project.

5. Save the search.

Note: Alternatively, you can set Posting = No to search for transactions that are not posting; in effect serves as a forecast of the possible profitability of the project.