Column Formats
Column formats can be defined in the following Setup screens:
There are two options to setup Column formats:
a) Grid Layout Tab
This format supports Copy and Paste.
However as indicated transposes columns to rows. For example row 2 will appears as column 2 on the report.
b) Column Layout Tab
This is an alternative style report writer for those who prefer a WYSIWYG/ visual preview more similar to the column designer in Managment Reporter or FRx. For example, ColNum 1 will print as the first column on the report.
This tab does not support copy and paste.
I) Add a New Report/Form
For the purposes of this article we will use the Define Budgets Forms (Custom Budget Entry) screens. And when the other Form and/or Report have an additional feature, it will be discussed in turn.
From the pull down menu select the "CompanyDB". This is a required field. Then click on the "Add New Form" button and the following screen will appear:
1) Enter the Form/Report Name; and
2) Save.
Upon savings the following screen appears; the default is to the "Grid Layout". We will create reports in the "Grid Layout" format and then view the equivalent in the "Column Layout".
Please note that the new form name has been entered and is highlighted in yellow, as have the now available for entry column entry fields.
The "Form Enabled" checkbox is automatically checked. This means that the form will appear in appropriate dropdown menus. Unchecking this box will remove the form from the dropdown menus.
3) "Primary BudgetID for Account List" is a required field. Please select the appropriate entry from the pulldown menu, and then save your selection.
Please note this selection determines which BudgetID accounts are drilled into from the Dashboard to the Account Detail on the Custom Budget Entry screen.
We will now define the Report Attributes that are available in the column entry fields. We will then provide example of setups.
A) This denotes the position of the Column on the report;
B) Enter the name you wish to provide to the Column here;
C) The following Data Entry Types are available:
i) Read Only - this is used when the column is to be populated with existing BudgetID (Column D) data, for example a prior year budget. It used in conjunction with Columns D, E, F, G and K;
ii) Variance Value - this calculates the difference in value between two columns [ColX - ColY] and is used in conjunction with Columns H, I and O;
iii) Variance Pcnt - this calculates percentage difference between two columns [(ColX - ColY)/ColY} and is used in conjunction with Columns H, I and O;
iv) Remaining to be Spent - this calculates the total of subtracting two columns from a single column {ColX - ColY - ColZ) and is used in conjunction with Columns H, I, J and O;
v) Custom Calc - this provides the ability to build custom calculations referencing other columns. The format to reference another column is {4}, namely the curly brackets and the column number. A number of math formulas and functions are supported, click here for a listing: functions
vi) Data Entry - this is only available on the Define Custom Budgets Entry Forms, and used to create budget data entry field on the form;
vii) Comments - this is only available on the Define Custom Reports and provides the ability to add comments to the screen; and
viii) Comment History - this is only available on the Define Custom Reports.
D) Select the BudgetID that you wish have displayed. In order for the BudgetID to be available here for selection, on the Manage Budgets screen it needs to selected as a:
i) "InReports" and "InSummary" for reporting out purposes; and
ii) "IsBudgeted" to be available for Data Entry, C vi) above.
E) and F) These two columns work in conjunction with each other and with C). Displayed in E) are the months associated with your Fiscal Year. The options in F) are either Month, Year-to-Date (YTD).
Assuming the fiscal year runs from January to December, by selecting December in E) and Month in F), the data displayed would be for December.
By selecting December in E) and YTD in F), the data displayed would be the total from January through December.
G) These selections determine the level at which data is reported and are explained in the Report Level - GL Code, Object or Object-Rollup article.
H) I) and J) are entry fields used to designate which Columns should be used in calculations. For example, when designating the columns for the Variance Value, see C) ii) above, ColX would be entered in H), say 4 (which means use the data in Column 4), and ColY would be entered in I), say 5. Therefore the calculation would be Col4 - Col5.
K) The data in the columns can either be expressed as Values, therefore as dollars ($) or as percentages (%).
L) Columns can be hidden and therefore not print on the final entry/report screens. These columns can used for calculation or reference purposes. To hide a columns check the "Hidden" box for the column you wish to hide. Uncheck the box to display the column. By checking the red box, all columns will be hidden because it will check all boxes for all columns.
M) This determines the background color of the column.
N) This determines the color of the text in the column.
O) This either displays the pre-formulated calculations as selected in C)ii, iii, or iv above or provides the entry field should C) v), namely the custom calculation be selected.
Example 1 - Displaying Actual and/or Budget data (ReadOnly functionality)
To display actual data, the "ReadOnly" option needs to be selected in the Data Entry Type columns.
In the example, actual data from 2014 was selected for the first columns and as the "Month" was selected in the Month/YTD columns, data from December 2014 is displayed in this column.
In column 2, the "YTD" option was selected in the Month/YTD column, and this results in data from January through December 2014 been displayed in this column.
In the 3rd column, the FY15 Budget data was selected for YTD as well. As the "GLCode" report level was selected for all columns, the data is reporting out at the full Great Plains Accounting software account level. Please note that careful consideration of the Column name can be helpful when viewing the final report product.
The below is the Column Layout for the above Grid Layout report.
Example 2 - Using Variance Value Functionality.
In the below example Column 4 will display the values in Column 2 less the values in Column 3. Please note that the calculation is activated by entering the column numbers in the "1st Calc Factor" and the "2nd Calc Factor" fields. The highlighted in yellow formula in the "FormulaResolved" cell is automatically generated.
The below is the Column Layout for the above Grid Layout report.
The below is how the report looks when run:
The "Variance Pcnt" and "Remaining to Spend" functionality is setup in a similar fashion to this "Variance Value" example.
Example 3 - Using the Custom Calc Functionality
This functionality can be used to calculate "what if" scenarios. This functionality is only available in the Grid Layout tab.
In our example we are using the Custom Calc functionality to work out what a 12 month budget would be when using December 2014 as the base amount multiplying it by 12 (for 12 months) and then adding in a Cost of Living (inflationary) amount of 3 percent (1.03).
The formula for this calculation as entered into the FormulaResolved cell is: ({1}*12)*1.03) where {1} represents the data in column 1.
The below is how the report looks when run:
It is important to note that data on Custom Budget Entry will be entered on the Accounts Details tab in the month of December and only this December data will be reflected on the Dashboard tab. Please see Define Custom Budgets Entry Forms
for more details and recommended Form format.
The selection of the "2016 test" for the Primary BudgetID for Account List determines that the drill down from the Budget Entry/Dashboard tab to the Account Detail tab will be to the "2016 test" account accounts.
The below is how the report looks when run:
Below is the "Account Details" tab when the 000-4117-01 is drilled into.
Please note per our settings above that the Budget ID is the "2016 test" and that the $1,000,000 which was entered on the Dashboard screen is entered into the December column.
Example 5 - Adding Color to a Column and/or Text
Double click in in the "Background Color" cell for adding color to a column and/or in the "Text Color" cell to add color to the text, and the following Color box will appear. Select the color you wish and then click on "OK".
Then "Save" your work to activate the selection.
And below is how it will look when the report is run:
II) Clone a Form
1) Select the Form you wish to clone;
2) Click the "Clone Form" button which will bring up the "Create new budget form" box;
3) Enter the name that you wish to call the clone;
4) Save.
Once the "Create new budget form" is saved the below screen will appear, namely the cloned form with its new name. This form can be edited without impacting the form from which it was cloned.
Right-click Menu
"Reset Formula" can be used on the column that is using "Custom Calc" - use will clear the calculation.
"Set Formula = Year Total" will up columns 1 - 12 and is useful in the "Define Budget Forms (Basic Budget Entry)" because that screen defaults to a column for each month of the fiscal year. Similarly for "Set Formula = Q1 Total" which adds up columns 1 - 3.
