| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Info |
|---|
Related topics |
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| Info |
|---|
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. |
| Info |
|---|
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.
1. Get the raw data
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.
2. Prepare the budget file
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
Note about custom isn't aligned make sure that will ensure .Column Note: then (e.g. )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 database3. Save and close the budget file
Save the budget file in CSV (preferred) or XLXS Data format, then close the file.
4. Upload (or sync) the budget 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.
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.
5. Add (connect) the budget file to a stream
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.
| Expand | ||
|---|---|---|
| ||
By default, when you add a budget file the data is not split, and the total value goes against the whole month. You might prefer to split the total value proportionately into the relevant days in the month. Your budget data might come from multiple files (data sources) that you add to the same stream. The option to split the budget is only available when you add the first file to the stream, following the steps above. If you add any other files to the stream afterward, their data will be handled in the same way because all items in a stream have to be on the same calendar. There are three options for splitting the first budget file. Any subsequent files are split (or not) in the same way. Do not split - This is the default behavior. The data remains as it is; it is not split. Each month has its own value (measure).  Working days - This option allows you to select from one of your custom working days calendars. The data is split by the number of working days in that calendar month and spread proportionality across each of the defined working days. Each of those days has the same value (measure).  Calendar days - The data is split proportionality across the number of days in the standard calendar, so each day has the same value (measure).  |
6. Map the budget data
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
7. Save and build the database
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.
8. View the budget
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.
Process steps
| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Panel | ||
|---|---|---|
| ||
Video |