Add a Formula to a Report

After running a new report or opening a saved report, there are many actions you can take to work with the data. One of these options is adding a formula to the report. A formula allows you to perform a function on the data in a report. Some of these functions include: 

• Perform mathematical functions on the data in your report.
• Use logical functions to replace data.
• Remove unwanted spaces or shorten text.
• Format a number as currency or a percentage.
• Format dates and time.
• Remove decimal points and round numbers.
• Calculate elapsed time.
• Count the number of characters in a response.

Add a Formula to a Report

When you add a formula to a report, a new column is automatically added as well, containing the data resulting from the formula. Follow the steps below to add a formula to a report. 

1. On your report, click Formula. 
2. Enter a Name for the new column, which will contain the data resulting from the formula.
   
   
3. Select a formula from the Formula drop-down. This will automatically insert it into the Formula box.
   • You may also type a formula into the Formula box.   
   • You can also click Formula Help for a list and descriptions of common formulas. 
4. Once your formula is in the Formula box, place your cursor or highlight the placeholder text where you need to refer to an existing column of data in your report.
   
   
5. Select the column from the Insert a Column drop-down, or manually type the label of the column inside brackets (e.g. [Project Name]). If you select the column from the drop-down, it will automatically insert where you placed your cursor or highlighted text.
   
   
6.  Click the Data Type drop-down and select the type of data that will result from the formula. 
7. If there is a specific format in which you want to see the resulting data, select it in the Display Format drop-down. Otherwise, leave the drop-down blank.  
8. Click Add. 
9. You'll now see the new column with the resulting data added to the report.
   
   
10. Click Save Changes to save all changes you make to the report.
    
    

    Common Reporting Formulas

    The dropdown list when adding a formula to a report contains many useful formulas. Below are some additional commonly used formulas for reference:

    • Rename values (e.g. to create anonymous labels for evaluators in the report):
      • IIF([Column] = “Bob”, “Evaluator 1”,IIF([Column] = “Joe”, “Evaluator 2”,IIF([Column] = “Mary”, “Evaluator 3”,IIF([Column] = “Jane”, “Evaluator 4”,IIF([Column] = “Sally”, “Evaluator 5”, 0)))))
    • Correct blank number cells that should display a 0:
      • [Column]+0
    • Combine columns:
      
      • [Column]+[Column]
    • Formula for "contains":
      • IIF(InStr(1,[Column], "What you're checking if it contains", 1)<>0, "Output if it does contain", "Output if it doesn't contain")
    • Put installment (or decision date, payment date, etc.) into correct fiscal year:
      • IIF(Month([Installment Due Date])<7,Year([Installment Due Date]),Year([Installment Due Date])-(-1))
        • The "7" in the formula above indicates the month. In this example, 7 for July.