How to create a Column Layout in FRx
Entry Type: Informational
Product: FRx
Application: FRx Desktop
Version Reported: All
Subject:
How to create a Column Layout in FRx
Possible Resolution:
Support for FRx has changed. Please see the following notice:
FRx Updates Notice By Microsoft
In FRx / Column Layout / New, complete these cells for General Ledger columns (defined as 'GL' in the 'Type' cell):
Actual or Budget
Double-click to select from the list of actual, budget and non-financial (statistical) options. A Column Layout can contain multiple actual, budget, and statistical columns for displaying different periods (current or year-to-date) and amounts. The list that appears reflects the actual, budget and statistical (non-financial) options established in General Ledger. Select the appropriate item from the list.
Fiscal Year
To use the base year specified at report generation time, enter "BASE" (without quotes). FRx supports up to ten years of historical data. To use other years, specify the number of years relative to the base year (using a + or - sign) for example:
- To specify the prior year, enter "BASE-1" or "-1" (without quotes).
- To specify the seventh prior year, enter "BASE-7" (without quotes).
- To specify the year after the base year, enter "BASE+1" (without quotes).
Period Code
Enter a base period code to indicate the fiscal periods to include in this column.
| Actual Period # | Enter the period number |
| Relative Period # | For regular reporting, always use a relative period so you do not have to change the Column Layout every period. Enter the period number relative to the base period (specified when generating the report) using the + and - signs. |
| Range of Periods | Enter a range of periods using TO (the standard FRx range indicator). You can use this with either absolute period numbers (for example, periods 3 TO 6) or with the relative periods (for example, -3 TO 1). |
| Transaction Detail Reports | Enter the period range in this period cell to include year-to-date transaction details in a column (or any range other than the current period). |
Current Per/YTD
Double-click this cell to select from the following list of options:
| CUR | This causes the column to reflect the current period activity. This type of column does not include beginning balances. |
| YTD | This causes the column to reflect year-to-date amounts. This type of column includes beginning balances. |
| CUR/BB | This reflects the beginning balance for the specified period. |
| YTD/BB | This reflects the beginning balance for the specified year. |
Adding Calculation Formulas
When the column amounts are the result of a calculation, you must enter "CALC" (without quotes) in the 'Type' cell and enter the formula in the 'Calc Formula' cell on the 'Column Layout' window. You can add, subtract, multiply, or divide columns. Any type of complex calculations can also be performed, including:
IF/THEN/ELSE statements within the formula:
| Add, Subtract | To add or subtract amounts in the columns, specify the column letter and the plus (+) and/or minus (-) sign. For example: A+B or A-B+C. You can also use the TO separator to add a range of consecutive columns; for example: A TO D. |
| Multiply or Divide | To multiply and divide columns, enter the column letters in the order in which they should be computed and separate each with the appropriate operator (* for multiplication, / for division). For example, B*D instructs FRx to multiply column B by column D. |
| Complex Calculations | A complex calculation can contain any combination of cell references, operators and values. For example, to compute an average of columns A and B, enter "((A+B)/2)" (without quotes). |
| There is no limit to the number of nested parentheses you can use. For example: (A+((B+C/2)+(C*.8)/2))+(F TO H)) | |
| Multiply or Divide by Base Row | You can create a column that displays all the values in a specified column as a percentage of base number. This feature provides a method to show relationships between rows, for example, percent of sales or percent of total expenses row. |
| To multiply or divide each row in a specific column by a base row, enter the column to be used in the calculation and *BASE or /BASE (for example: C*BASE or C/BASE). | |
| When you use a base row calculation, be sure each row format used with this Column Layout contains at least one row with a CBR (Change Base Row) format code. The CBR code indicates the row used in the calculation. |
Note: Use a special format mask to format the number as a percentage.
IF/THEN/ELSE statements:
- IF/THEN/ELSE statements allow any calculation to be conditional upon the results of any other column. You can refer to other columns, but not to a report cell in the IF statement; thus, any calculation must be applied to the entire column.
- Enter the calculation in the 'Calc formula' section of the column that receives the results of the calculation.
Note: You cannot place the results of a calculation in any other column; they must be in the column that contains the formula.
For example, the formula "IF B>100 THEN B ELSE C*1.25" translates to: If the amount in column B is greater than 100, place the value from column B into the CALC column. If the value is not greater than 100, multiply the value in a column C by 1.25 and place the result in the 'CALC' column.
- IF is always followed by a logic statement which evaluates to true or false. The formula used for both the THEN and ELSE clauses can contain references to any number of columns and may be as complex as needed, including multiple nested calculations. For example: IF (statement) THEN H+(G*((C+D+E-F)/.25)).
Special Formatting Options
The following cells apply special formatting to selected columns. Although some of the Print Control and Column Restrictions options are specific to GL columns, most options apply to all column types.
Column Width
FRx applies the column width differently based on your use of text and amounts.
- Column Width for Text Columns - Use this option to specify the number of characters to use for the width of this column on the printed report. This cell is particularly important for 'Desc-' and 'Fill-type' columns. If the column width is not specified here, FRx uses 30 characters for the description and 10 characters for a 'Fill-type' column.
- Column Width for Amount Columns - The default width of amount columns (CALC, WKS, and GL) is specified in the Catalog of Reports. Thus, a value entered in the column width cell overrides settings in the Catalog of Reports. In most cases, you should leave this cell blank unless you need to fine-tune individual columns within a report.
Extra Spaces Before Column
Enter the number of extra spaces to be placed in front of a given column. You can use this option to separate groups of columns and to add a few spaces before the description (so the description column is indented from the left-justified titles in the report). The default number of spaces between each column is two, as defined in the 'Formatting' tab in the 'Report Options' tab in the Catalog of Reports.
Special Format Mask
Use a special format mask to assign decimal, currency, and percentage formatting to your column amounts.
To assign a special format mask to your column amounts:
- In the column you want to assign the mask, double-click the 'Special Format Mask' cell in the Column Detail section. A dialog box opens.
- Select a format, and click 'OK'.
- Make any modifications to the special format mask in the Edit Bar.
Note: For example, a mask of #,### rounds to thousands.
When you generate your report, this format is used for this column. If no format is entered, the default format identified in the 'Amount & Date Format' of the 'Company Information' dialog box is used.
If you defined special formats in a row calculation and in the column, the row formatting overrides the column formatting.
Print Control
The following optional codes adjust the display or printing characteristics of the column. You can leave this cell blank to display the column without the effects of a print control.
| NP (Non-printing) | Select this code if the amounts in this column are part of a calculation only and should not be printed in the report. |
| BS (Balance Sheet) | Select this code to indicate the Balance Sheet Column to be printed on a combined Balance Sheet/Income Statement Report. Because the same Column Layout is used for both the Balance Sheet and the Income Statement, this code is required to identify the columns that apply to the Balance Sheet. |
| XCR (Reverse Credit Sign) | Select this code to reverse the sign of a calculation column based on whether the normal balance of a given row is a credit (indicated as "C" in the Norm Bal cell of the row format). |
| SR (Suppress Rounding) | Select this code to suppress a column from being rounded. |
| XO | Select this code to suppress a 'GL' column if all amounts are blank or zero. |
Using Conditional Columns
You can also select print control options for conditional columns:
| P<B | Displays this column only if the period is less than the base period. |
| P>B | Displays this column only if the period is greater than the base period. |
| P=B | Displays this column only if the period is equal to the base period. |
| P<=B | Displays this column only if the period is less than or equal to the base period. |
| P>=B | Displays this column only if the period is greater than or equal to the base period. |
Adding Column Restrictions
The following optional codes restrict certain types of data to display within a column. You can use multiple codes within a single column if necessary. To select a code, double-click 'Column Restrictions'.
| SU | If an underscore or double underscore (--- or ===) command is entered in the row format, selecting a column suppresses the underscore for that column only. For example, you might not want to underline amounts that are the result of a percentage calculation. |
| ST | Suppresses totals and shows only details in this column (for example, in statistical columns). |
| SD | Suppresses details and shows only TOT rows (from the row format) in this column. |
| DR | Restricts the amounts in the column to debit amounts. |
| CR | Restricts the amounts in the column to credit amounts. |
| ADJ | Restricts the amounts in the column to period adjustment amounts, if available. |
| XAD | Restricts the amounts in the column to exclude the period adjustment amounts, if available. |
| PT | Restricts the amounts in the column to include posted transactions only. |
| UPT | Restricts the amounts in the column to include unposted transactions only. |
Select a Reporting Unit
If the column type is 'GL', select a tree and reporting unit code in this cell to restrict a column to a specific reporting unit. This feature is useful when information is applicable to a certain unit only, or when you want to display reporting units side-by-side, for example departmental side-by-side comparison reporting.
To select a Reporting Unit from a list:
- Double-click the 'Reporting Unit' cell. A list of trees appears.
- Select a tree to use in conjunction with this Column Layout.
- When the list of reporting units appears, select a unit from the list.
Note: If you enter a reporting unit in the Column Layout, be sure to use the same tree when this column layout is used in a report. If not, the units from the tree may not match and the columns will be blank on the report.
To restrict the column to a specific currency:
- Double-click the 'Currency Code' cell to open the 'Currency Key' dialog box.
- Select a currency from the list as defined in General Ledger.
Account Filters
For columns where 'Type' is 'GL', the 'Account Filter' cell can restrict a column to specific General Ledger accounts. The account filter may contain one or more accounts that are the exact length of the full account code of the current company. The filter is applied regardless of whether a tree is used.
You can use wild card characters (?) in any or all positions. When you specify multiple accounts, place a comma between accounts:
???-1200-12??-???, ???-1200-13??-???
Using Account Filters with Reporting Units
The 'Account Filter' cell can work either in place of or in conjunction with the 'Reporting Unit' cell, which restricts a column to a specific reporting unit. For example, the 'Reporting Unit' cell can restrict the column to a certain department, Retail; whereas the Account Filter cell can further restrict the column to specific accounts (4???-????-001 Sales for Retail) within a department
Attribute Filters
The 'Attribute Filter' cell works in conjunction with the A_ATTR or T_ATTR column types and Attribute Categories. Attributes work like virtual account segments, and must be supported by your accounting system.
The 'Attribute Filter' cell restricts data in the 'GL' columns to specific values or ranges that apply to Account Attribute or Transaction Attribute categories. When you select 'A_ATTR' or 'T_ATTR' as the column type and assign an attribute category in the 'Attribute Category' row, you can use the 'Attribute Filter' row in 'GL' columns to restrict accounts that will be included in the report.
Restricting Columns to Specific Dates
The 'Start Date' and 'End Date' cells restrict data in 'GL' columns to specific dates. This technique is useful for daily or weekly sales reporting, cash analysis needs and other date-sensitive reports.
Note: For non-SQL databases, the dates apply to unposted transactions only.
To enter a single date:
Enter a start date in the in the 'Start Date' cell, for example, 03/04/2002. Using a single date includes only the transactions entered on that date in this column.
To enter a range of dates:
- Enter a start date in the 'Start Date' cell, for example, 12/04/2002.
- Enter an end date in the 'End Date' cell, for example, 01/15/2004.
Using a date range includes all the transactions entered during this date range in this column. For example, the date range defined in the previous steps begins December 4, 2002 and ends January 15, 2004.
Note: Any date or date range specified in the full date format overrides any period and year for the Fiscal Year and Period Code for this column.
