Using FORMULAS

The Executive Briefing Service includes the ability for you to define “formulas” on the analysis grid (AG) reports. Formulas allow you to create a new field on the report based on whatever criteria or calculation you define. Clicking the FORMULA button on an analysis grid (AG) report brings up the screen which allows you to enter your definition:

Clicking on the “Formula Help” button will give you detailed instructions on what functions are available along with some examples. We’ll show some examples below, but we encourage you to look at the “Formula Help” contents, as they provide a more detailed explanation than we will cover here.

Enter the Name of the new “calculated” field you are creating

Pick the first field that you will use in the calculation and hit the “Insert” button to add it to the formula definition:

Enter the “operator” for the formula

Operators are things like multiply, divide, etc. Here’s the list of operators that are available

Operator Description

– Negation
^ Exponentiation
* Multiplication
/ Division
\ Integer Division
Mod Modulus (Remainder)
+ Addition
– Subtraction
+ String Concatenation
= Equals
<> Not Equals
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To
Not Logical Not
And Logical And
Or Logical Or
( and ) Parenthesis to manage precedence

We’ll add a “slash” to indicate that we want to divide two numbers

Enter more fields, operators, etc. to complete your formula definition

Specify the type of resulting data this formula creates (number, text date, datetime)

Set the display format for the results

Click the “Add” button, and you will now see an additional field (which you have defined), which can be included in your report, just like any other field.

You’ll also see your newly defined field automatically added to your report as the last column

Your newly created formula field also now appears just like all the other fields under the LAYOUT button

What’s up with the “NaN”?

So you may have noticed that when “Actual Hours” is zero, we get a result of “NaN” (since you cannot divide by zero). To fix that, we can modify the formula so that it only does the “division” if Actual Hours is not zero. We do this using the “IIF” conditional function