User-Defined Columns in the BFA Workbook

Did You Know?

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 the BFA workbook, you can configure one or more of these user-defined columns through the Setup sheet in BFA. Instructions below assume that these new columns will be added at the site level (i.e., all users will then see the columns through the Site Settings.)

To open the Setup sheet in BFA:

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

To configure your BFA workbook with user-defined columns (site settings):

1. Select Site Settings from the Settings ribbon and click OK at the confirmation message that appears.
   
2. Look through the system-defined fields (in rows 56-57) 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, Posted CO EAC Revisions), enter a Location number greater than zero, then change the Show Column setting from N to Y for any of the BFA modes (IntBU = Initial Budget; BU = Budget Revision; PA = Project Analysis; FC = Forecast):
      
3. 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).
4. Change the Title, Location, Width and/or Alignment of any of the user-defined columns (as you would with any column).
5. Change the Show Column (row 19) to Y for any user-defined column you want to be visible. Note: you cannot show a column that has a Location of zero.
6. 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.
7. 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.
8. Save your Site Settings somewhere on your computer.
   
9. Go to the Manage Dashboard, Templates tool.
10. Filter for BFA Site Settings.
11. Click the icon to upload your new Site Settings, overwriting the one that already exists. Note: while it is possible for you to have more than one Site Settings file in the template library, only one Site Setting can be active at a time.
    

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.

• 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.