Select Columns

In Step 3 of the Report Wizard, you can select the columns to be included in your report. You can select the data you wish to include in your report. Each piece of data will be a column in your report. This page will include all the possible reportable data types associated with the type of report you are running, including, but not limited to project information, custom fields and more.

Different sections containing data fields, which pertain to the report data you selected in Step 1, will be available for selection in this step.

For a Process Summary report, you will see sections containing data related to Project Information, Project Custom fields, Process Information, Process Custom fields).

e-Builder also allows you to split the rows on the report when there is more than one value, for any multiple pick list custom field, multiple check box form field, or multiple pick list process data field. The report will display the results on separate lines instead of displaying them on one line.

When the Return row for each multivalued field value check box is selected, if the result for a multivalued field is regions Eastern, Northern, and Southern, they will be displayed in the report this way:

RP_MultiValNew71

Instead of this way:

RP_MultiValOld71

In addition to the fields listed on the Select Columns page you can add your own formula columns. A formula column will allow you to perform mathematical functions on the data in one or more columns.

The Formula Builder gives you a way to have report columns automatically populate with data. Both standard and unique data can be included by creating a formula using existing project information stored in e-Builder.

Formulas can range in complexity. The most straightforward formulas might include functions that automatically insert the current date and time into a report column. More complex formulas might include mathematical operations that provide the average square footage of X-ray rooms or school gyms. Another example might be the sum of the current contract values on all commitment and commitment changes.

Setting up these data fields is optional, but they can reduce the time it takes you to fill-out data fields and make information readily available without having to search through the system for it.

Alphanumeric fields such as an address and strictly alphabetical fields such as the project names, cannot be combined with mathematical operators (+-/*) to build a formula, otherwise an error will occur. When using mathematical operators, only numeric fields can be used.

Use a formula column with a Cost column divided by a budget custom field for Square Feet to calculate the cost per square foot on a project.

Functions

Functions are predefined expressions that allow you to input data to complete assignment expressions. The following options are available:

Field

Description
Now()

Returns current date and time in GMT:

  1. Select this function.
  2. Click Insert.
  3. Select a field from the Assign to Field drop-down arrow.
  4. Click Save.

Today()

Returns current date and time in your time zone (The run as user):

  1. Select this function.
  2. Click Insert.
  3. Select a field from the Assign to Field drop-down arrow.
  4. Click Save.

MinDate(value1, value2)

Minimum date (applies to date fields only): Returns the smaller of two specified date values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

MaxDate(value1, value2)

Maximum date (applies to date fields only): Returns the larger of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

DateDiff(datepart, start, end)

 

 

 

Returns a value for the difference between a start and end date.

The datepart, start and end parameters should be enclosed with double quotes if you are not using data fields, as outlined in the steps below.

Start and end are expressions that should be resolved to a date, using any valid date formats, such yyyy-mm-dd, mm/dd/yyyy, etc.

The table below lists all valid datepart arguments and the abbreviations that can be used to replace them.

datepart

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s
  1. Select the DateDiff(datepart, start, end) function and click Insert.

    2. The function displays as DateDiff( , , ).

  2. Place your cursor before the first comma.
  3. Enter double quotes + [valid datepart or abbreviation] + double quotes.

    4. So far, the formula should look similar to: DateDiff("year", ,)

  4. Place your cursor before the second comma.
  5. To enter a data field, click the Select Field drop-down arrow and make a selection.

    6. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  6. Place your cursor after the second comma.
  7. To enter a data field, click the Select Field drop-down arrow and make a selection.

    8. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  8. Click Done.

DateAdd(datepart, number, date)

 

 

 

Returns a date with the given amount added to it.

The datepart, number and date parameters should be enclosed with double quotes if you are not using data fields, as outlined in the steps below.

Number is an expression that can be resolved to an integer that is added to a datepart of date. If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

Date is an expression that should be resolved to a date, using any valid date formats, such yyyy-mm-dd, mm/dd/yyy, etc.

The table below lists all valid datepart arguments and the abbreviations that can be used to replace them.

datepart

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s
  1. Select the DateAdd(datepart, number, end) function and click Insert. The function displays as DateAdd( , , ).
  2. Place your cursor before the first comma.
  3. Enter double quotes + [valid datepart or abbreviation] + double quotes. So far, the formula should look similar to: DateAdd(“year”, ,).
  4. Place your cursor before the second comma.
  1. To enter a data field, click the Select Field drop-down arrow and make a selection.

    2. —Or—

    Enter a numeric value.

  2. Place your cursor after the second comma.
  3. To enter a data field, click the Select Field drop-down arrow and make a selection.

    4. —Or—

    To enter a date, enter the [Date (value) function located in the Functions drop-down menu].

    For the (value) enter the end date value. For information on the [Date (value)] function, see Date(value) below.

  4. Click Done.

Date (value)

Allows you to convert a date in string form to a date type. Value is the string value.

Examples:

Formula

Sample Output

Date("12.10.2013")

12.10.2013

Date("12-10-2013")

12.10.2013

Date("12/10/2013")

12.10.2013

IIFDate( 1 > 2, Date("12.10.2013"), Date("12.12.2013"))

12.10.2013

MaxDate(Date("12.10.2013"), Date("12.12.2013"))

12.10.2013
IsNullDate(date) Allows you to determine whether or not a value exists for this date field. If it does, then it will be factored into the formula.
IsNullString(value)

Allows you to determine whether or not a value exists for this string field. If it does, then it will be factored into the formula.

RemoveWhiteSpace(value)

Returns the given value with extra spaces removed. This function removes both leading and trailing spaces from the text as well as converting blocks of multiple spaces within the text into a single space character. (e.g. the text becomes the text'). Value is the text to remove extra spaces from.

Trim (value) Removes white space from the beginning and end of the given value. (Value) is the text to strip preceding and following white space from.
Find (similar to VB Instr)

Finds a string within a string & returns the character number where the search string is found.

Left (source value, length number)

Use to find a specified number of characters from the left (beginning) of a string.
Mid (source value, start position number, length number)

Returns the middle x characters of a string, starting at position y within the string.
Right (source value, length number) Returns a specified number of characters from the right (end) of a string.

Substitute (source value, search value, substitution value)

Changes characters within a string.

Replace(source value, start position number, length):

Used to replace characters within a text.
Rev (source value) Returns a reversed string. For example, REV(Luke) returns ekuL.
Len (source value) Returns the length of the inputted variable

IIFString (Condition, true string, false string)

 

 

If and only if (applies to text fields only):

Evaluates the given condition and returns the text for true string if the evaluation succeeds or the text for false string if the evaluation fails.

Condition: Boolean expression used to determine which text to return.

True String: If condition evaluates success then this text value is returned.

False string: If condition evaluates failure then this text value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow, and then click Insert.
  4. Place your cursor before the second comma. To use a data field for the "if true value", make a selection from the Select Field drop-down menu and click Insert.

    5. —Or—

    To enter your own value, enter double quotes + [true string value] + double quotes.

  5. Place your cursor after the second comma.

    6. To use a data field for the "if false value", make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false string value] + double quotes.

  6. Click Done.

IIFNumber (Condition, true string, false string)

 

 

If and only if (applies to number fields only):

Evaluates the given condition and returns the number in true number if the evaluation succeeds or the number in falsenumber if the evaluation fails.

Condition: Boolean expression used to determine which number to return.

True String: If condition evaluates success then this number value is returned.

False string: If condition evaluates failure then this number value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow (e.g. Contract Sum), and then click Insert.
  4. Place your cursor before the second comma. To use a data field for the "if true value", make a selection from the Select Field drop-down menu and click Insert.

    5. —Or—

    To enter your own value, enter double quotes + [true number value] + double quotes.

  1. Place your cursor after the second comma.

    2. To use a data field for the "if false value", make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false number value] + double quotes.

  2. Click Done.

IIFDate(Condition, true string, false string)

 

If and only if (applies to date fields only):

Evaluates the given condition and returns the date in true date if the evaluation succeeds or the date in false date if the evaluation fails.

Condition: Boolean expression used to determine which date to return.

True String: If condition evaluates success then this date value is returned.

False string: If condition evaluates failure then this date value is returned.

  1. Select this function.
  2. Click Insert.
  3. Place your cursor before the first comma, enter the condition by selecting a data field from the Select Field drop-down arrow (e.g. Contract Sum), and then click Insert.
  4. Place your cursor before the second comma.

    5. To use a data field for the "if true value", make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [true string value] + double quotes.

  5. Place your cursor after the second comma.

    6. To use a data field for the "if false value", make a selection from the Select Field drop-down menu and click Insert.

    —Or—

    To enter your own value, enter double quotes + [false string value] + double quotes.

  6. Click Done.

MinNumber(value1, value2)

Minimum number: Returns the smaller of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.
MaxNumber(value1, value2) Maximum number: Returns the larger of two specified numeric values. Value1 is the first of the two numeric values to compare. Value2 is the second of the two numeric values to compare.

IsInList(list, value)

Returns true if a value is contained in either (1) the selected values of a Multi Pick List Custom Field column, or (2) the values in a comma delimited string. IsInList() must be used as part of a condition in formulas like IIFString, IIFNumber, or IIFDate.

List is a Multi Pick List Custom Field column, or comma delimited string.

Value is the value to search for.

Examples:

Formula

Sample Output

IIFString(IsInList({CustomProject.ProjectMPLCustomField}, 2), 2 was in the list, 2 was not in the list)

2 was in the list

IIFString(IsInList(“one, two, three”, “two”), “two is in the list”, “two is not in the list”)

two is in the list

Str(value)

–Converts a value to a string. This function gives you the ability to output a string or empty value. Value is the value you want to convert to a string. conversion of date diff..works best for string or number

Formula

Sample Output

Str(1+2)

3

IIFString (1>2, "", Str(DateDiff("d", Today (), {Portals.CreateDate})))

5

Done

Click to save the formula. If the expression is invalid, an error message displays for you to make necessary corrections.

Cancel

Click to cancel the operation and close this window.

To select columns to include in your report

  1. In step 3 of the Report Wizard, select the check boxes for the data you wish to include as columns in your report.

    The columns listed will vary based on the type of data selected in Step 1 of the Report Wizard.

    Some of the columns will already be selected when you arrive at this step. To add or remove columns from the report, select or clear the check box next to the name of the column. To add or remove all of the columns in one section click the Select All or Deselect All link corresponding with the name of that section.

  2. To add formula columns, click Add New Formula Column. The Formula Builder popup displays where you can define the formula name and format.Formula Builder

    1. In the Formula Builder window, enter a name for the formula column in the Label field. This is a required field.
    2. From the Decimal Places drop down list, select a preferred decimal placement for integer values.
    3. In the Use Parentheses for Negative Values field, choose one of the following options:
      • Yes: Select this option to use parentheses around negative values. This is the default selection.
      • No: Select this option if you do not want negative values to be denoted by parentheses or distinguished at all.
    4. Within the Formula section, choose a field name from the Select Field drop-down list then click Insert.

      The selections in this drop-down arrow will also vary depending on the fields configured. Moreover, if the workflow is for a cost-related process, it will contain many more fields, specific to cost.

    5. From the Operators section, click on the operator you wish to add.

      Operators are used to build formulas between data fields. Alphanumeric fields such as an address and strictly alphabetical fields such as the project names, cannot be combined with mathematical operators (+-/*) to build a formula, otherwise an error will occur. When using mathematical operators, only numeric fields can be used.

    6. From the Functions drop-down list, choose the mathematical function to use and click Insert. The selections are displayed in the formula preview section.
    7. Continue this process until the formula value is fully defined.
    8. Click Done. Any errors in the formula are displayed below the preview section, if present. If there are no errors, the formula column is added to the Report Wizard.
    9. Select the check box next to the newly added formula column label to add it as a column to the report.

  3. Select the Return row for each multivalued field value check box to display the values of a data type which may have multiple values into separate columns.
  4. Click Next to proceed to Step 4 in the Report Wizard.