About Columns in Report Specifications

Sage 300 ERP

About Columns in Report Specifications

Most of a financial report specification is a template for the report it produces. For example, all report formatting (such as column widths, fonts, highlighting and underlining) appears on the financial report specification.

The first four columns of the report specification (columns A through D) contain important control information.

Note: You can begin a specification in any column, as long as the Spec range begins with the same column.

Sample Report Specification

In this example, row 1 is a default row. To use the default formulas in subsequent rows, place a backslash in the appropriate spreadsheet cells.

Column A

The contents of column A of each row (the first column of the Spec range) determine the purpose of all columns in the row. There are five possible types of rows in a report specification:

  • Comment row. If column A starts with .. (two periods), the row is a comment row.
  • "As is" row. If column A is blank, the row is part of the report and will appear "as is" on the final report.
  • Default row. If column A starts with \\, the row is used for setting up defaults for the remaining rows on the report.

    You can have more than one default specification row; each applies to the rows between it and the next default row.

  • Title row. If column A starts with \T, the row is used to define title lines on the final report. You can have up to five title rows, which will appear at the top of each tab of the report (immediately under the header lines).
  • Body specification row. Rows that start with account number references are called "body specification rows."
Note:
  • If column A contains any other data, Financial Reporter assumes that the data is a reference to one or more account numbers.
  • If an account number appears in column A, it generally means that all printed columns in the row will contain data from that account.

Columns B, C and D

Columns B, C and D filter accounts specified in column A (columns 2 through 4 of the Spec range) to refine the account selection. For example:

  • Column A can specify a range of accounts.
  • Column B can contain a filter to restrict the range of accounts in column A to a particular account group (or to an account type or account name).

    Column B can also refer to the filter for posted transactions, if Column D refers to listing by posted transaction details or by posted transaction consolidated details.

    For more information, see About Selection Criteria for Filtering Accounts.

  • Column C can remove any lines from the report that have zero balances (use Z to omit zero-balance lines) or meet a condition such as "balances less than $1,000".
  • Column D can consolidate the balances of the range of accounts, consolidate by account segment, list each account separately, consolidate all values from retrieved optional fields, or print the optional field value of the first retrieved record. This can also be consolidated by transaction date and posting sequence.

    Use T to generate a single total line for an account range. If you leave column D blank, or specify a D, Financial Reporter will list the details for each account in the range.

Column D constants for use in the spec area are the indicators for listing by transaction details, by transaction consolidated total, and by transaction total. Values must begin with P.

Column D Constants

List By

Cell Value of Column D

Transaction details in posted transaction order

PD

Transaction consolidated total

PT

Transaction consolidated by account

P(POSTRAN)

Transaction consolidated by journal date

P(POSTDATE)

Transaction consolidated by posting sequence

P(POSTSEQ)

Transactions can also be listed by consolidated account segments and account groups as listings by accounts. When consolidating by a specific account order in listing by transactions, the column D value must start with P instead of with D.

Example: P(ACSEGVAL02) is the consolidation of transactions with account segment 2 retrieving records from a transaction, while D(ACSEGVAL02) is the consolidation of account history with account segment 2 retrieving records from an account.

To list the details by transaction, the spec must provide a correct sorting order, as illustrated in the following table:

Sorting Order

Exclusive Sorting Order

Meaning

POSTTRAN

By Posted Transaction, in account order

POSTDATE

By Date Order

POSTSEQ

By Posting Sequence Order

POSTACCT

By Account Posting Sequence

Note:
  • If the account reference is specified with exclusive sorting order at column A from a spec line and the order is an account order (for example, 1000:9000|ACCT), Financial Reporter does not generate the transaction details of the spec line.
  • If the consolidation value in column D is P(POSTTRAN), the sorting order must be POSTTRAN.
  • If the consolidation value in column D is P(POSTDATE), the sorting order must be POSTDATE.
  • If the consolidation value in column D is P(POSTSEQ), the sorting order must be POSTSEQ.
  • If the consolidating total is retrieved by any defined account switches, like P(ACSEGVAL02), it can be sorted by any defined account order, similar to listing by accounts.
  • Listing by transaction is only limited to the inquiry of transaction records from the inquired period.
  • Use T to generate a single total line for an account range. If you leave column D blank, or specify a D, Financial Reporter lists the details for each account in the range.

Column E

The report template actually starts in Column E, the fifth column of the Spec range. Any value or formula placed in the report template area is part of the final report.

The following example shows a report specification displayed in formula view.

image\spec-sht.gif

In this example, the first three lines of column F that appear on the report are:

  • =FR("CONAME"), a Financial Reporter function that displays the company name on the statement.
  • The text "Schedule of Expenses."
  • The text and function ""For the period ending " &FR("END")." The function inserts the period end date from the database.

Row 9 of the specification sets the default contents of statement columns. Column A of this row starts with two backslashes.

The next row with text contains the headings for columns G, H, and I. These headings are printed "as is."

Note: You use formatting commands in Excel to underline these column headings.

The next row with text will be expanded on the printed report to include all accounts in the range between 6000 and 6999 that meet other selection criteria specified in cells B, C, and D, or entered at print time on the Print Statements screen. In the spreadsheet's formula view, the row looks like this:

image\ebx_1385558925.gif

The backslash (\) in the three columns on the right indicates that each cell value will be generated from the default formula set for the column. (Financial Reporter inserts the account reference in column A into any default formula that requires the account reference parameter. For example, the default formula for column G is =FRAMTA("NETP"). FRAMTA is a Financial Reporter function that prints the account period balances or net amounts from the account fiscal sets. In this case, the formula specifies the net amount for the current period.)

When Financial Reporter processes this cell of the statement template, it expands the formula with the account reference from column A. (For example, =FRAMTA("NETP", "6000").)

The remaining two columns also use a default formula; in this case, bringing in the net changes for the quarter to date and the year to date.

The final row in our example is the total line:

Note: You use formatting commands in Excel to format cells with top and bottom borders.

Notice also that the total formula for each column refers to a single cell (or list of cells) in the specification range. In our example, the single cell in the specification range formula will be replaced in the report range formula by a range reference that contains as many rows as there are accounts in the account range specified on line 12.

Related Topics