EXPANDPROJECTHISTORY

Overview

The EXPANDPROJECTHISTORY function returns a list of only those projects and related entities that have non-zero Project Module amounts or quantities over the specified financial period range.

The main purpose of the function is to help you avoid zero balance/quantity rows in your project reports (similar to what ACCOUNTSWITHHISTORY does in the GL module). Configuring the BalanceTypesToLookAt argument consistently with the Velixo project functions that you use can ensure there will not be a row where all "balance types to look at" are zero.

Syntax

CODE

=EXPANDPROJECTHISTORY(ConnectionName, Project, Task, ForecastRevision, AccountGroup, CostCode, InventoryItem, Branch, FromPeriod, ToPeriod, IncludeInactive, ExpansionOrder, BalanceTypesToLookAt)

Arguments

The EXPANDPROJECTHISTORY function uses the following arguments:

See our article on filtering Velixo functions for information on how to include/exclude specific projects, tasks, revisions, account groups, cost codes, inventory items, and/or branches.

Argument	Required/Optional	Description
`ConnectionName`	Required	The name of the connection as configured in the Connection Manager
`Project`	Required	The Project(s) to check for project history. Default: `all`
`Task`	Optional	The Task(s) to check for project history. Default: `all`
`ForecastRevision`	Optional	The Project Forecast Revision name(s) to check for project history. Default: `all`
`AccountGroup`	Optional	The Account Group(s) to check for project history. Default: `all`
`CostCode`	Optional	The Cost Code(s) to check for project history. Default: `all`
`InventoryItem`	Optional	The Inventory Item(s) to check for project history. Default: `all`
`Branch`	Optional	The Branch(es) to check for the presence of project history. Default: `all`
`FromPeriod`	Required	The beginning financial period ID in MM-YYYY format
`ToPeriod`	Required	The ending financial period ID in MM-YYYY format
`IncludeInactive`	Optional	`TRUE`/ `FALSE` value indicating if inactive projects, tasks, account groups, cost codes, and inventory items are to be included in the results. Default: `TRUE`
`ExpansionOrder`	Optional	Using the letters `PTRACI`, a list of the columns and their order to be included in the results. Default: `PTRACI`. See below for details
`BalanceTypesToLookAt`	Optional	List of balance types (separated by commas) to be considered as "history". If left blank, any balance type with a non-zero amount or quantity will determine history presence

What entities get returned by this function

The EXPANDPROJECTHISTORY function is able to return a table with the following columns, in the following default order:

Project code
Task code
Revision number of the project budget forecast
Account group code
Cost code
Inventory item ID

However, the columns (and the order) returned by this function can be controlled by using the ExpansionOrder argument. Its default value is PTRACI, according to the first letters in the list above.

For example: specifying "PT" will only return those Project and Task codes (in that order) with non-zero balances.

Ensuring no zero rows are returned

If a particular combination of project, task, etc. is not returned by the function, it means that there are no project-related amounts or quantities of interest, as determined by the optional BalanceTypesToLookAt argument.

However, by default, the function considers all project-related amounts and quantities, such as:

Any actual amounts or quantities
Any budgeted amounts or quantities
Any forecasted amounts or quantities, original and revised
Any committed amounts or quantities, original and revised, open and invoiced
Amounts to invoice
Change order amounts
Costs to complete and cost at completion

Most of the time, your report will likely involve only some of these amounts and not all of them - for example, your report may only contain Velixo functions for actuals and budgets.

In such cases, to avoid zero rows in your report, we recommend that you use theBalanceTypesToLookAt argument and synchronize it with the data that your report needs to display.

This will allow you to narrow down the specific types of quantities and amounts that the function needs to consider when deciding whether to return a particular combination.

Balance Types

The following options are available for the BalanceTypesToLookAt argument:

AmountToInvoice
BeginningAmount
BeginningQuantity
BudgetAmount
BudgetQuantity
ChangeOrderAmount
ChangeOrderQuantity
CommittedAmount
CommittedInvoicedAmount
CommittedInvoicedQuantity

CommittedOpenAmount
CommittedOpenQuantity
CommittedQuantity
CommittedReceivedQuantity

CostAtCompletion,
CostToComplete
EndingAmount
EndingQuantity
ForecastAmount

ForecastQuantity
ForecastRevisedAmount
ForecastRevisedQuantity
RevisedAmount,
RevisedQuantity
RevisedCommittedAmount
RevisedCommittedQuantity
TurnoverAmount
TurnoverQuantity

Examples

Here are a few simple examples to get you started. Explore the use of the various arguments to get exactly the data you are looking for.

Example 1 - return all project codes

CODE

=EXPANDPROJECTHISTORY("Demo","*",,,,,,,"01-2018","12-2019",,"P")

Description

Returns the Project Codes for all projects with activity (history) during the periods from 01-2018 to 12-2019 (inclusive)

Result

(cell references have been used for some of the arguments in this example)

Example 2 - return all project and task codes (sorted by project)

CODE

=EXPANDPROJECTHISTORY("Demo","*",,,,,,,"01-2018","12-2019",,"PT")

Description

Returns the Project Codes and Task Codes for all tasks with activity (history) during the periods from 01-2018 to 12-2019 (inclusive). The only difference between this example and example 1 is ExpansionOrder argument ("PT" instead of just "P").

Result

(cell references have been used for some of the arguments in this example)

Example 3 - return all project and task codes (sorted by task)

CODE

=EXPANDPROJECTHISTORY("Demo","*",,,,,,,"01-2018","12-2019",,"TP")

Description

Returns the Task Codes and Project Codes (sorted by Task) for all tasks with activity (history) during the periods from 01-2018 to 12-2019 (inclusive). The only difference between this example and above example is the ExpansionOrder argument ("TP" instead of "PT").

Result

(cell references have been used for some of the arguments in this example)

Example 4 - return all project and some task codes

CODE

=EXPANDPROJECTHISTORY("Demo","*",,,,,,,"01-2018","12-2019",,"TAP")

Description

Returns the Task Codes (excluding task 01), Account Groups, and Project Codes for all tasks with activity (history) during the periods from 01-2018 to 12-2019 (inclusive).

Result

Note that task 01 is not included:

(cell references have been used for the arguments in this example)

Example 5 - only consider TurnoverAmount and TurnoverQuantity balances

If you only set the PROJECTTURNOVERAMOUNT and PROJECTTURNOVERQUANTITY functions and specify "TurnoverAmount,TurnoverQuantity" as the value of the BalanceTypesToLookAt argument, the function will then only return combinations that have non-zero actual turnover amount or quantity (rows that have zero in both will not be returned, regardless of whether or not they have other non-zero project-related balances, such as budgeted or forecasted amounts)

CODE

=EXPANDPROJECTHISTORY("Demo","*",,,,,,,"01-2018","12-2019",,"TAP","TurnoverAmount,TurnoverQuantity)

Description

Returns the Task Codes (excluding task 01), Account Groups, and Project Codes for all tasks with Turnover Amount or Turnover Quantity activity (history) during the periods from 01-2018 to 12-2019 (inclusive).

The only difference between this example and the prior example is the use of the BalanceTypesToLookAt argument.

Result

Note that, because only certain balances are being taken into consideration, the results are very different from the previous example:

(cell references have been used for the arguments in this example)