Add a budget file to a non-financial database (easy budget upload)
Last updated
Last updated
This page outlines how to add a budget to a non-financial (operational) database, for use in Phocas Analytics, using the Easy Budget Upload method. If you want to add a budget file for use in Phocas Financial Statements, see Add a budget file to a financial database.
User permissions: Administration > Databases and Sync
The Easy Budget Upload method is a simpler way to add a budget to a database, as Designer automatically detects the budget file and converts it into the required format. Depending on how the data in your source file is arranged, this method might not be suitable, for example, if it contains pivoted data. If that is the case, you need to add the budget the same way you would add any other type of item, and ensure you check the date format.
The process to add a budget file involves multiple steps, in different places, as outlined below.
Watch this Phocas Academy video: Easy Budget Upload
Export the raw data from the underlying database into a Microsoft Excel file. The data might come from Phocas, your ERP system or another source.
Prepare the data in the budget file according to the requirements outlined below, to ensure it is in the correct format for uploading to Phocas.
The basic requirements are:
The raw budget data must contain only a single measure.
Codes should be used, not names, for example, Rep007, not Justin Time.
The following table gives is an example of a typical budget in the required format:
Other requirements are as follows:
If the selected database is summarized by a custom period type that is not aligned with calendar periods, such as a fiscal period, ensure that the date column (YYYY-MM-DD), especially the day of the month, or DD, takes into account the appropriate fiscal period start date. This ensures your budget stream is summarized using the correct date range.
Example of a typical budget in the required format when using a custom period type such as fiscal period:
The column headers in the data source must be in one of the following date formats:
If using a full date (the first two examples above), typically the date should be the first day of the period. For example, for a May budget, the date required is 2017-05-01.
Save the budget file in CSV (preferred) or XLXS Data format, then close the file.
If you are creating a Balance Sheet budget, in addition to the budget file, you need to upload the opening balances. This data can be in the same file or a separate file. There are two ways to get your file into Phocas:
Toolbox > Upload files: When a budget file is setup in Designer, this method allow users with upload permission to upload an updated budget without having access to Designer. The file must be uploaded under the Shared User Uploads section for it to be available to an administrator in Designer. See Upload a file.
Designer: Open Designer in Phocas, then either upload or sync the file (see Get your data into Phocas). Either way, ensure you select the File has header row checkbox, to enable Designer to recognize the file as a budget file.
When you add the budget file to a stream, Designer converts (unpivots) the budget data into the required format and displays it in the new stream tab. All the dated columns are changed into a Moment column and a Measure column.
In Designer, click the New Stream tab.
Click the Edit button in the new stream tab, enter a more suitable name for the stream (such as Budget) and click Save.
Expand the data sources panel on the right and locate the budget file, then drag it into the stream’s Drag here box. Designer recognizes that you are loading a budget-type file and displays a message to that effect.
Click Yes to confirm you are adding a budget file.
Split the budget, if required. This option is only available for the first data source you add and your selection here determines the setup for subsequent data sources you add to the stream - expand the section below for more information.
Map the data in the budget file to the corresponding elements in the database. Typically, you need to map over the top of existing dimensions and measures.
Value → Measure > Value
Moment (date/period) → Date
Save your design changes and build the database to apply those changes. You can either build the database now or schedule a build for later.
View the budget in Phocas Analytics by selecting it as a stream or selecting Stream mode. See Change the stream and Use Stream mode for more information.
Sales Rep
Region
Jan 2018
Feb 2018
Mar 2018
Apr 2018
Rep007
41
10,000
12,000
10,000
15,000
Rep007
42
5,000
7,000
5,000
10,000
Rep008
41
22,000
22,000
20,000
28,000
Sales Rep
Region
2023-01-01
2023-01-29
2023-02-26
2-23-04-02
Rep007
41
10,000
12,000
10,000
15,000
Rep007
42
5,000
7,000
5,000
10,000
Rep008
41
22,000
22,000
20,000
28,000
Format
Example
YYYY-MM-DD
2017-10-27
yyyy-MM-dd HH:mm:ss
2017-10-27 09:26:01
MMM-yyyy
Oct-2017
MMM yyyy
Oct 2017
MMMM-yyyy
October-2017
MMMM yyyy
October 2017
MMM-yy
Oct-17
MMM yy
Oct 17
MMMM-yy
October-17
MMMM yy
October 17