User-Defined Columns in BFA Workbook

The Budget/Forecast/Analysis (BFA) workbook offers 15 user-defined columns for information not already displayed through system-defined columns. If you want to display information not currently available in your BFA workbook, you can configure one or more of these user-defined columns through the Setup sheet in BFA.

To open the Setup sheet in BFA:

1. Open your BFA workbook from the Project Analysis option on your project’s drop-down menu:
   
2. Open the Setup worksheet by clicking the tab at the bottom.
   

To configure your BFA workbook with user-defined columns:

1. Look through the system-defined fields to see if one of them, perhaps not currently visible, already tracks and displays the information you want.
   1. If you find such a column (for example, CO Expense Risk), change the Show Column setting from N to Y:
      
      
   2. Also specify a Location greater than zero.
2. If you still need user-defined columns, scroll right until you find them.
   
   1. There are three types of user-defined columns available.
      1. User Calc Fields columns hold results of calculations. These columns do not accept user input, but rather use formulas and data within the Data worksheet to calculate results. If the data changes between opening of the BFA workbook, so do these results. Nothing is saved to SQL.
      2. User Save Text columns hold any text values (see Notes below).
      3. User Save Amount columns hold any numeric amounts (see Notes below).
3. Change the Title, Location, Width and/or Alignment of any of the user-defined columns (as you would with any column).
4. Change the Show Column (row 19) to Y for any user-defined column you want to be visible.
5. Select a Format for any user-defined column as appropriate (see Notes below).
   1. Text = alphanumeric characters treated as text.
   2. Currency = numbers treated as currency, as defined in the Amounts setting elsewhere on the Setup worksheet.
   3. Currency2 = numbers treated as currency, with two decimal places.
   4. Currency4 = numbers treated as currency, with four decimal places.
   5. Units2 = numbers treated as numerals, with two decimal places.
   6. Units4 = numbers treated as numerals, with four decimal places.
   7. Percentage = numbers treated as percentages.
   8. Percentage2 = numbers treated as percentages, with two decimal places
   9. Date = numbers treated as dates.
6. For each User Calc Field column that you want to use, indicate a formula in the Formula row, for example:
   
   Note: you can use the F3 key while in the User Calc Field cell to pop up a Defined Names list. Selecting Defined Names for your formula is recommended. While direct references to a cell like “B3” will work, the probability of error when filters and sorts are applied to the data is great.
7. Save your settings.
   

Notes:

• If you select Currency, Currency2, or Currency4 as the Format for a user-defined column, both a Totals as of total and a Filter total cell will appear at the top of that User Calc Field column.

• Instructions given above are for local settings. User-defined columns can also be configured as part of site settings. See Changing BFA Workbook Site Settings for more information.
• For more information about the User Save Text and User Save Amount columns, see KBA-01524.
• For information about the related BudgetConfig | UDSSourceMode rule, see KBA-01176.