BrightWork Reporter provides a robust reporting tool to build many types of reports in the BrightWork solution.  There is a feature in the report definition that allows you to extend the reporting content by using mathematical expression formulas in the report column.  New report columns can be created to process data from other columns and return additional data values to the reports.

An example of an expression column already in the report definitions is Finish Date.  This allows the report to return a finish date to the report regardless of whether it is the Actual Finish, Planned Finish, Current Finish, or Due Date.

2 Rules for Expression Formulas

There are two rules that you need to adhere to when using expressions in report columns.

• First, the columns that you use in the expression must be higher in the column order in the report definition.
• Second, expression formulas use the names of the report columns, not the list column names.

Rule 1

The column order is managed in the report definition editor.  Ensure that the columns you intend to use in the expression are higher in the column order.  This can be check in the Report Definition Editor by selecting “Change the order for expressions”.

The order can be adjusted up or down in the same manner as columns are ordered in a SharePoint list.

Rule 2

The second rule is you need to use the internal name of the report column, not the display name.  Here is the example of the Finish Date column that uses a conditional expression to find any finish date within the project data.

Creating an Expression Report Column

Expression columns can be created for 5 column types.  They are:

• A single line of text
• Number
• Date and Time
• Yes/No
• Currency.

You can use most of the common mathematical operators in your expressions.  There are also some handy functions like #Today# that can also be leveraged.  To create a new expression column –

1. Select “Add a new column” in the Report Editor.
2. Enter the column name and caption, column type and other items required.
3. Scroll down the page to the Expression section. Type in your expression
4. Save the column.

Here is an example of an expression column that sets a Yes/No column (LATE) to Yes if the Finish Date is < Todays Date.

Now that the column is added to the report definition, it can be used in any reporter webpart.  For example, we modify the All Projects report in the Project Office to include the column LATE and group by that column.  Edit the report configuration settings for the All Projects report.  It will then look like this:

Expressions are an easy way to enhance reporting with additional data based on your project data without having to enter it into the Project Site.  They can be used for comparisons, calculations, status indications, and many other uses. There are great examples in our knowledgebase.  More details can be found in this article – https://help.brightwork.com/15.5/working_with_expressions.htm