Expressions let you add columns that calculate a value to BrightWork Report reporter definition files. The return type of an expression is determined by the DataType of the column.

Expression Columns

Supported expression columns:

Single line of text

An example of an expression in a text column would be:

IIF(#Today#<DateDue,'Overdue','On Target')

This expression would return a text value of 'Overdue' for any overdue items and 'On Target' for others.

Number (1, 1.0, 100)

An example of an expression in a number column would be:

IIF(ActualNumberOfAnomalies>PlannedNumberOfAnomalies,ActualNumberOfAnomalies - PlannedNumberOfAnomalies,0)

This expression compares two number columns, 'PlannedNumberOfAnomalies' and 'ActualNumberOfAnomalies'. It determines if the number of actual anomalies exceeds the number of planned anomalies. If it does, the difference is displayed, otherwise 0 is displayed.

Date and Time

An example of an expression in a date time column would be:


This expression would return the Due Date value of any overdue items.

Yes/No (check box)

An example of a Yes/No expression would be:


This expression would return a Boolean value of true (1) for any overdue items. This would be displayed as a Yes in the report and No if the result was false (0).

Currency ($, ¥, €)

An example of such a currency expression would be:


This expression would return a penalty of $10,000 (assuming dollars) for any overdue items.

Advanced Expressions

Examples of advanced expressions:

Showing the Start Date

<DateTimeColumn name="StartDateExpression" caption="Start Date" displayFormat="d" expression="ISNULL(StartDateActual, ISNULL(ScheduledStartDate,StartDatePlanned))" />

This expression returns the Actual Start Date (StartDateActual) if it exists.

If it does not exist, it returns the Scheduled Start Date (ScheduledStartDate) if it exists, otherwise it returns the Planned Start Date (StartDatePlanned) whether it exists or not.

Showing the Due Date

<DateTimeColumn name="FinishDateExpression" caption="Finish Date" displayFormat="d" expression="ISNULL(ISNULL(ScheduledFinishDate,FinishDatePlanned),DateDue)" />

This example returns the Scheduled Finish Date (ScheduledFinishDate) if it exists.

If it does not exist, the expression returns the Planned Finish Date (FinishDatePlanned) if it exists, otherwise it returns the Due Date (DateDue).

Showing the Actual Cost

<CurrencyColumn name="ActCost" caption="Actual Cost" expression="IIF(ActualCost>0,ActualCost,IIF(PercentComplete>0,PlannedCost*PercentComplete,0))" />

This expression returns the Actual Cost (ActCost) if the Actual Cost value is greater than 0. If it is not, or the value does not exist, the expression returns an estimate of the Actual Cost by multiplying the Planned Cost (PlannedCost) by the Percent Complete (PercentComplete), having checked that the Percent Complete is not at 0%.

Stripping text from a value

This example tests whether a text value contains the letters "hr" or "hrs" (hours), and if found, trims these letters in order to return a number. It requires several columns to arrive at a value.

<TextColumn name="WorkText" caption="Work1 (Text)" defaultValue="0 hr" />
<TextColumn name="WorkText2" caption="Work2 (Text)" expression="IIF(WorkText='','0 hr',WorkText)" />
<NumberColumn name="Work" caption="Task Work" expression="IIF(SUBSTRING(WorkText2,LEN(WorkText2)-1,LEN(WorkText2))='hr', ISNULL(TRIM(SUBSTRING(WorkText2,1,LEN(WorkText2)-3)),0), ISNULL(TRIM(SUBSTRING(WorkText2,1,LEN(WorkText2)-4)),0))" />

First a text value is mapped to the 'WorkText' column before being passed to the 'WorkText2' column to check if it is empty. The 'Work' column then tests whether this value uses the text hr or hrs, and if it does, trims this text away, leaving the number.

Unsupported Column Types

Unsupported column types:

  • Multiple lines of text

  • Choice (menu to choose from)

  • Lookup (information already on this site)

  • Person or Group

  • Hyperlink or Picture

Was this article useful?

Back to Top