Phocas has a new user documentation site. This site will be retired soon.

Database tab

Owned by Helen Gosper
Last updated: Oct 24, 2024 by Denise McGettigan
8 min read

Add a Database tab in your financial (Profit and Loss) budget workbook to connect to data in another database and automatically drive the values in the account rows in the Main tab. For example, you can use your Sales database to drive the Revenue accounts in the Main tab of your financial budget.

You can also manually link to the data in the Database tab via formulas, which is a useful option if the new database does not easily map to the existing database.

Database driver tabs are not available in operational budgets but you can define additional measures in those budget setups to drive the budget data.

The Main tab setup impacts the Database tab setup

The hierarchy and layout of the budget’s Main tab is determined by its setup. In the case of a financial budget, the budget displays in the P&L template that you selected with the number of levels you set. The following image shows how the budget setup impacts the driver tab setup. You can see settings for the category and other dimension levels (Country and Account) in the budget. Later in the driver tab setup, the dimensions in the new database need to be mapped (matched) to those in the existing database.

image-20240122-020600.png

The Database tab drives the values on the Main tab

After the Database tab is set up, the values in that tab drive the values in the account row on the Main tab. There is no workflow for the driven rows.

image-20240122-021440.png

The Database tab and Reference tab are very similar, as they both connect to another database to display additional data in the budget workbook. The two key differences are as follows:

  • Automatic driving of budget values - The purpose of the Database tab is to automatically drive budget values in the Main tab. This is achieved through the mapping step in the Database tab setup, where you match the dimensions in the two databases. As the Reference tab setup doesn’t have a mapping step, no dimensions are mapped, so no values are automatically driven. However, you can manually drive values by referring to the reference data in formulas.

  • Flexible hierarchy - As explained in the section above, the hierarchy of the Database tab is based on that of the Main tab. So, for example, if there are three levels in the Main tab, the Database tab will also have three levels. The Reference tab is independent of the Main tab, so you are free to determine the levels in the hierarchy to organize the data as required.

Do you need to add a new Database tab? If there’s already one in the workbook, save time by cloning that tab and editing its setup.

  1. Add the tab using one of these methods:

    • Click the Tab menu > Add tab > Database.

    • Click the Add button at the bottom of the workbook and select the Database.   

  2. Set up the Database tab. The setup process involves several steps and settings organized into tabs. Many settings are automatically selected for you to save you time.

The settings in the Configuration and hierarchy tab determine where the other data comes from, how it will be organized in the tab, and what it will drive in the Main tab.

Configuration

  1. Edit the name of the new tab if required. 

  2. Select the Database from which to source the data, along with the corresponding stream and value measure. The available databases and their stream and measure options depend on whatever is in your organization's Phocas site. The Budgets and Forecasts module is based on the Financial Statements module, so there will always be a financial database option. However, you might have other options, such as a Sales or Purchasing database.

  3. Select the baseline data structure:

    • Stream: Fill the tab with values from the selected data stream as a starting point that you can adjust later. For example, you could use your current year's actuals or the previous year’s budget values.

    • Zeros: Fill the tab with zeros ($0.00) so you can start with a clean slate.

  4. (Applicable when using stream values) Change the default offset if required. If you're budgeting by year, the offset defaults to -1, and if you’re budgeting by month, it defaults to -12.

  5. Set the measure format to determine how the measures (numerical values) display in the grid.

  6. Leave the Include ### suspense items checkbox selected if you want unclassified items to display in the Database tab. If you do not want to include those items, clear the checkbox.

  7. Leave the Allow users to edit values checkbox selected if you want other users to edit the data in the Database tab. If you want to prevent users from editing the data (deactivate all workflows), clear the checkbox. You might want to do this at the end of the budget period to lock in the final budget values.

Hierarchy

  1. (Applicable when linking to a financial database) Select the template to determine the layout of the contents on the new tab:

    • The available templates depend on the types of Profit and Loss statements your organization has created in the Financial Statements module.

    • The template determines the layout of the data, along with the account mapping. It also applies the Reverse Sign setting (displays a negative number as a positive, or vice versa) from the corresponding financial statement.

    • For example, if you are bringing in data from last year’s Profit and Loss statement, you might want to use the same Profit and Loss template as you used in the budget setup so the data is laid out the same in both the Main and Database tabs. The following images show the difference a template can make to how the data displays in the tab.

  2. Leave the default Driver based on setting as it is if you want all the Profit and Loss categories in the Main tab to be driven by the data in the Database tab. If you want to drive only one or more specific categories, click the dropdown arrow and clear the checkboxes of the categories you don’t want to drive.

  3. (If applicable) Change the default mapping applied to the data levels (hierarchy) if required.

    • By default, the levels in the Main tab setup are included in the Database tab. For example, if the Main tab of the budget has one level (Country dimension), it is also included in the Database tab setup. You cannot remove these levels, but you can change the dimensions in the database to which they are mapped. Select the required dimension from the dropdown list.

    • You can filter the dimensions to select one or more specific entities. Click the Filter dimensions button next to the level and select the entities you want to include, then click out of the entity list to apply your selection.

  4. (If applicable) Select the dimension in the Database tab that you want to map to the account level in the Main tab.

    • By default, this is the lowest level in the budget at which you want to enter values, and this is where the workflow sits. For example, you might want to budget by Sales Rep instead of account.

    • If you select a template in step 8 above, this setting is unavailable, as the account mapping comes from the template. If you add other levels, you can change where the workflow sits.

  5. (If applicable) Add levels to determine how the data in the tab is organized.

    • By default, the Database tab has the same levels as the Main tab, but you can add levels below the mapped account level. Then, you can select the level at which the workflow sits.

    • The number of levels you can add is limited by the number of levels in the Main tab (not including the Category). For example, suppose the Main tab has three levels: Category, Country, and Account. You do not count Category, so two levels will be inherited by the Database tab. You can add up to four more levels in the Database tab setup.

    • The additional levels you add are unmapped, which means they do not drive any rows in the Main tab.

    • Click Add level and select the dimension you want to add. Repeat to add more levels. Select the Workflow option on the applicable level.

  6. (If applicable) Apply a tab filter: Select the Filter… checkbox > select the dimension that you want to filter (you can't select one that’s been added as a level) > select the entity you want to use as the filter > click out of the window to apply your selection. See Budget filter for more information, as the concept is the same.

  7. Click Next.

The Measures tab allows you to define additional measures to drive the budget data. By default, one measure input row is included. You can either leave it as it is and click Next to proceed with the Database tab setup (see the next section below) or configure the measure and add additional measures.

Read the Include additional measures in the workbook page for more information.

Map (match) the dimensions in the Database tab to the dimensions in the Main tab, then click Next. You might need additional mapping if the Main tab has multiple levels. For example, if the Main tab has two dimension levels, Country and Branch, you must do two mappings, one for Country and another for Branch.

  • If you have many entities, search for one by code, name, or other property. To limit the number of entities displayed, you might find it helpful to select the Not mapped option from the All dropdown list at the top right.

  • To automatically map entities (get Phocas to perform the mapping activity based on identical entities), click the blue Auto match button above the grid.

  • To manually map individual entities, select the entity, then select the corresponding entity in the Main tab (in the window that displays).

  • To manually map all entities to the same entity in the Main tab, click the Select all button in the top left corner of the grid, then select the corresponding entity (in the window that displays).

  1. Click Save and finish. The new tab opens with the data from the other database or zero values if you selected that baseline data structure.

  2. Review the new tab and the corresponding, automatically driven values in the Main tab. If required, edit the tab setup before editing any budget values.

  3. Manually link to the new data via formulas in the Main tab and other tabs, as required. This is useful if the new database does not easily map to the existing database.