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.
Note: Any column referenced in an expression must be positioned above the expression column in the list of columns in the DataTable in the reporter definition file - see Change the Column Order for more information.
Please see https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx for more information from Microsoft on data column expressions.
Supported expression columns:
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.
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.
An example of an expression in a date time column would be:
IIF(#Today#<DueDate,DueDate,NULL)
This expression would return the Due Date value of any overdue items.
An example of a Yes/No expression would be:
IIF(#Today#<DateDue,1,0)
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).
An example of such a currency expression would be:
IIF(#Today#<DateDue,10000,0)
This expression would return a penalty of $10,000 (assuming dollars) for any overdue items.
Examples of advanced expressions:
<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.
<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).
<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%.
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.
Tip! If you need to reference a list's name in an expression, the name to use is __FilterListName. Note that there are two underscores in the name.
Unsupported column types:
Multiple lines of text
Choice (menu to choose from)
Lookup (information already on this site)
Person or Group
Hyperlink or Picture