deployment strategy

BrightWork Tip: Use Expression Formulas in BrightWork Reporting

June 9, 2017 by

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

 

Change Order

 

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

Change Column Order

 

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.

Edit Report Column

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.

Expression Formulas

 

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:

Report Usage

 

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

 

Scott Footlik

Scott is a  Project Management consultant with over 25 years of experience in the development of Project Management organizations, process and toolset implementation. He has extensive experience with organization change and adoption of project management best practices.

Scott’s hobbies include Flying and taking cruises with his wife.  He earned his private pilots’ license at 17 and also has an instrument rating.  When he is not consulting, he loves to fly around the southwestern united states or in the Caribbean on a cruise ship.
Scott Footlik

Latest posts by Scott Footlik (see all)

    Pin It on Pinterest

    Share This