Versions Compared

Old Version 42

changes.mady.by.user Helen Gosper

Saved on

compared with

New Version 43

changes.mady.by.user Denise McGettigan

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel3
outlinefalse
excludeBudget stream,Related topics
typelist
printablefalse
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:

  1. Make sure your budget data is in the right format (see below).  

  2. Upload or sync your budget file

  3. Select Admin > Databases and choose the database you want to add a budget to (you'll need the correct permissions)

  4. Select Design to view the database in Designer 

  5. Select New stream and name it (e.g. 'budget)

  6. Locate your budget data from the data items panel on the right, and drag it on top of the 'drag here' box.

  7. Select 'Yes' if asked whether to use this data as a budget item.

  8. Map the appropriate columns in your budget data. Typically, you'll need to map over the top of existing dimensions and measures.  

  9. Build the database (or wait for it to build on its schedule)

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

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:

  1. The raw budget data must contain only a single measure.

  2. Codes should be used, not names

(e.g.

  1. , for example, Rep007, not Justin Time

)

  1. .

Example

The following table gives is an example of a typical budget in the 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

(info) Note about custom

Other requirements are as follows:

Expand
titleCustom periods

If the selected database is summarized by a custom period type that

isn't aligned

is not aligned with calendar periods, such as a fiscal period,

make sure that

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

will ensure

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

.

:

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

Column

Expand
titleDate 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

Info
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 example, for a May budget, the date required is 2017-05-01.

3. Save and close the budget file

Save the budget file in CSV (preferred)

.

Header row

Note it is important that you tick 'File has header row' when you upload your file, as shown below.

Image Removed

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),

or XLXS Data format, then close the file.

4. Upload (or sync) the budget file

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.

Image Added

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.

5. Add (connect) the budget file to a stream

  1. In Designer, click the New Stream tab.

  2. Click the Edit button in the new stream tab, enter a more suitable name for the stream (such as Budget) and click Save.

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

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.Image Removed

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.

Image Removed

Image Removed

'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

    • displays a message to that effect. Click Yes.

      Image Added

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

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

Image Added

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 by selecting it as a stream, or choosing stream from the mode button.

Process steps

Table of Contents
minLevel1
maxLevel3
outlinefalse
excludeBudget stream,Related topics
typelist
printablefalse

Related pages

Panel
bgColor#E3FCEF

Video

Easy Budget Upload