Description

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). By configuring the BalanceTypesToLookAt parameter consistently with the Velixo project functions that you use, and can ensure there will not be a row where all "balance types to look at" are zero.

Syntax

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

The EXPANDPROJECTHISTORY function uses the following parameters (see our article on including multiple parameter values):

Parameter

Required/Optional

Description

Connection

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

Optional

The beginning financial period ID in MM-YYYY format

ToPeriod

Optional

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

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, however, your report will most 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 the BalanceTypesToLookAt parameter 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 parmeter:

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

💻 Example:

If you only uset he PROJECTTURNOVERAMOUNT and PROJECTTURNOVERQUANTITY functions and specify "TurnoverAmount,TurnoverQuantity" as the value of the BalanceTypesToLookAt parameter. 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 they have other non-zero project-related balances, such as budgeted or forecasted amounts.

Did this answer your question?