A simple method for bringing a budget stream into a database, depending on the format of the file you are uploading, is to take advantage of Designer's ability to automatically detect a budget file and convert it accordingly. This is sometimes referred to as 'Easy Budget Upload'.
To add a budget stream using this method:
Make sure your budget data is in the right format (see below).
Upload or sync your budget file
Select Admin > Databases and choose the database you want to add a budget to (you'll need the correct permissions)
Select Design to view the database in Designer
Select New stream and name it (e.g. 'budget)
Locate your budget data from the data items panel on the right, and drag it on top of the 'drag here' box.
Select 'Yes' if asked whether to use this data as a budget item.
Map the appropriate columns in your budget data. Typically, you'll need to map over the top of existing dimensions and measures.
Build the database (or wait for it to build on its schedule)
View the budget in Phocas by selecting it as a stream, or choosing stream from the mode button.
Required budget format
The easiest format requirements for uploading budgets into Designer are as follows:
The raw budget data must contain only a single measure.
Codes should be used, not names (e.g. Rep007, not Justin Time).
Example of a typical budget in required format
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 |
Note about custom periods
If the selected database is summarized by a custom period type that isn't aligned with calendar periods, such as a fiscal period, make sure 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 will ensure 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.
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 |
Confirm you are uploading a budget file
Phocas will convert (unpivot) this format when you drag it into Designer. That is, Designer will recognise (because the column headers are like dates), that you are loading a budget-type file and ask if you want to process the file as a budget. If you select 'yes', all the dated columns will be changed into a 'moment' column and a 'measure' column.
Column headings
The column headers in the data source must be in one of the following date formats:
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 |
Note: If using a full date (the first two examples above), then typically the date should be the first day of the period. (e.g. for a May budget, the date required is 2017-05-01).
Header row
Note it is important that you tick 'File has header row' when you upload your file, as shown below.
Confirm you are uploading a budget file
Phocas will convert (unpivot) this format when you drag it into Designer. That is, Designer will recognise (because the column headers are like dates), that you are loading a budget-type file and ask if you want to process the file as a budget. If you select 'yes', all the dated columns will be changed into a 'moment' column and a 'measure' column.
Designer mapping
Once you have the data in Designer, map the value data (the 'Measure' heading) to Measures and map the moment data (the 'Period' heading) to Date.
'Easy Budget Upload' video on the Phocas Academy
Alternative method for uploading a budget file
Depending on how the data in your source file (e.g., csv, Excel, txt file) is arranged, the easy budget upload method may not be suitable, e.g., if it contains pivoted data. If that is the case, uploading the file takes a few extra steps - the most important of these will be to check the date format in Designer. See Design a database