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 the following: 

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

Add a Formula to a Report

When a formula is added to a report, a new column containing data from the formula is automatically added as well.

1. Click Reporting on the upper navigation bar, and then click Reports & Data Sets.
2. Click Run Report next to the report to which a formula will be added.
   
   
3. Click Formula. 
4. Enter a Name for the new column which will contain the data resulting from the formula.
   
   
5. Select a formula from the Formula drop-down menu.
   • This will automatically insert it into the Formula box. A formula can also be manually entered into the Formula box.   
   • Click Formula Help for a list and descriptions of common formulas. 
6. Once the formula is in the Formula box, highlight the placeholder text that is used to refer to an existing column of data in the report.
   
   
7. Select the column from the Insert a Column drop-down menu or manually type the label of the column inside brackets.
   • Example: [Project Name]
   • When a column is selected from the drop-down menu, it will automatically insert where the text is highlighted.
     
     
8.  Click the Data Type drop-down menu and select the type of data that will result from the formula. 
9. If there is a specific format needed for the resulting data, select it in the Display Format drop-down. Otherwise, leave the drop-down blank.  
10. Click Add. 
    • The new column with the resulting data will be added to the report.
      
      
11. Click Save Changes.
    
    

    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.