Specify a column type in a column definition

The type of information that is included in each column in a report is specified by the value in the Column Type row in the column definition. Each column definition must contain at least one description (DESC) column and one amount (FD, WKS, or CALC) column. For a definition of each column type, see Column types.

This topic contains the following sections:

• Column types

• Financial Dimensions column

• Format a multiple currency report in a column definition

• Calculation column in a column definition

Column types

Specify a column type

1. In Report Designer, open the column definition to modify.

2. Double-click a cell in the Column Type row in the appropriate column.

3. Select a column type from the list. For a description of column types, see the following table.

Financial Dimensions column

The following Column Definition row definitions apply to columns with the Column Type FD (Amounts from Financial Dimensions).

Book Code/Attribute Category cell

The Book Code/Attribute Category cell identifies the book code for the data in the FD column. A column definition can include multiple actual, budget, and statistical columns, and can display different periods, such as current or year-to-date, and different amounts.

The list of book codes reflects the actual, budget, and statistical (non-financial) options that have been established in your financial data.

Fiscal Year cell

The Fiscal Year cell identifies the fiscal year to include in this column. The year can be relative to the base year that is specified when the report is generated. The following options are available.

Period cell

The Period cell identifies the fiscal periods to include in this column. The period can be relative to the base period that is specified when the report is generated. The following options are available.

You can go beyond fiscal year boundaries in any of the period specifications, and you can mix years in a range of periods. For example, if you specify the periods BASE- 5 (representing the past 6 periods) and run the report that has a base period of 2, the report will show data for the first two periods of the specified fiscal year and the last four periods of the previous fiscal year.

Specify the periods for an FD column

1. In Report Designer, open the column definition to modify.

2. In an FD column, double-click the cell in the Period row, and then select an option in the list.

3. In the formula bar (above the navigation pane) or in the Period cell, complete the formula, replacing the # character with the appropriate value. For formula definitions, see the table in Period cell.

Periods Covered cell

The Periods Covered cell identifies the amount to display in this column. This amount is relative to the value in the Fiscal Year and Period cells for this column. The following options are available.

Specify the periods covered for an FD column

1. In Report Designer, open the column definition to modify.

2. In an FD column, double-click the cell in the Periods Covered row, and select an option in the list. For a definition of the options, see the table in Periods Covered.

Attribute filter in a column definition

Attributes are financial data values that further define an account or transaction. Account attributes may include Asset, Liability, Revenue, and Expense. Transaction attributes may include Transaction Description and Transaction Apply Date. Attribute support may differ between Microsoft Dynamics ERP systems.

The Attribute Filter cell restricts data in FD columns to specific values or ranges for attribute categories. This can be used together with an ATTR column, but the ATTR column is not required. In the FD column, there is a limit on which accounts or transactions will be included in the report from the attribute filter.

For information about how to use account attributes and transaction attributes in the row definition, see the “Account modifiers” section in Specify Row Modifier cell in a row definition.

Apply an attribute filter for an FD column in a report

1. In Report Designer, open the column definition to modify.

2. Double-click the Attribute Filter cell for an FD column.

3. In the Attribute Filter dialog box, double-click a cell in the Attribute column, and then select the filter type.

4. To further limit the results, enter a range in the From and To columns. The From cell must contain a value.

5. Click OK.

Example of an attribute filter

The following example shows a section of a column description with an account attribute in the Book Code/Attribute Category row. The Attribute Filter for this column specifies the range of values to include in the report.

Dimension filter in a column definition

A dimension filter is used to restrict the FD column to specific dimension values.

The filter can include a single dimension, a range of dimensions, or a group of dimensions. The filter can also include dimension value sets. Because dimension values can vary, a dimension-based system does not have to correspond to an exact length.

The filter is applied, regardless of whether the report includes a reporting tree.

You can use a wildcard character (* or ?) in any position. When you specify multiple accounts, place a comma between accounts, as in the following example:

+Account=[1200], +Account=[1100], Department=[01?]

To receive all departments for a specific account, you can exclude the Department dimension in the dimension filter. For example,

+Account=[1100],Department

is treated the same as:

+Account=[1100]

You can also use any combination of alphanumeric characters for exact matching, and you can define partial dimensions. For example, Location = [10*] includes all location dimension values that begin with 10.

Apply a dimension filter for a column in a report

1. In Report Designer, open the column definition to modify.

2. Double-click the Dimension Filter cell for an FD column.

3. In the Dimensions dialog box, enter the filters that you want to apply.

4. Click OK.

Format a multiple currency report in a column definition

A multiple currency report can display amounts in the natural (local), functional (default), or reporting currency. A company’s functional currency is defined in the Microsoft Dynamics ERP system. Do not confuse this ERP setting with the operating system regional options setting where you can configure the default currency symbols that you want to use on reports.

The following currency-related cells are in the column definition:

• Currency Display – Specifies the type of currency (natural, functional, or reporting) in which the transactions are displayed. This functionality is sometimes referred to as currency translation, which is the ability to report general ledger amounts in a currency that may not be the functional currency of the company, and it may not be the currency in which the transaction was entered.

• Currency Filter – Specifies a currency filter. Only transactions entered in the selected currency are displayed in the report.

To determine a company’s functional currency, do the following:

1. In Report Designer, on the Company menu, click Companies to open the Companies dialog box.

2. Select a company, and then click View. The View Company dialog box opens. The currency defined for this company is shown under Regional options.

For more information about how to create a multiple currency report in Management Reporter, see Translate report currency.

Specify the currency in a multiple currency report

1. In Report Designer, open the column definition to modify.

   Note

   The options described here may differ based on the ERP system. For more information, see your Microsoft ERP system documentation.

2. Double-click the Currency Display cell in the appropriate FD column, and then select the option for displaying currency information: Natural/originating currency, Functional currency from company information, or the reporting currency.

3. Double-click the Currency Filter cell in the appropriate FD column, and then select the appropriate currency code in the list. Only transactions entered in this currency are displayed in the report.

Example for Currency Display and Currency Filter cells

Phyllis has made the following currency selections in her column definition:

• Currency Display – Functional (U.S. dollars)

• Currency Filter – Yen

Because of the currency filter selection, the report prints only transactions that were entered in Yen. Because of the currency display selection, the report displays those transactions in the functional currency (U.S. dollars).

Currency Filter and Currency Display combinations

The following table illustrates the report results for various combinations of the options in Currency Display and Currency Filter cells that could possibly occur because of the selections Phyllis made. The functional currency is U.S. dollars (USD).

* Conversion rate at approximately 100 yen per U.S. dollar.

** Sum of all transactions in functional currency.

Calculation column in a column definition

A Column Type of CALC in a column definition supports complex calculations in the Formula cell, and can include the +, -, *, and / operators, together with IF/THEN/ELSE statements. Additionally, a calculation column can refer to any other column, including subsequent columns. A calculation column can also include the Fiscal Year and Period to support headers for the column. The calculation formula can be up to 1024 characters long.

To express the calculation result as a percentage, use a special format override. For more information about format override, Select Format Override cell

Operators in calculation columns

To add, subtract, multiply, or divide columns, type the column letters in the order of computation, and then use the appropriate operator to separate each column letter. The following table explains the operators that you can use in a calculation column.

Operator	Example calculation	Description
+	A+C	Add the amount in column A to the amount in column C.
:	A:C A:C-D	Add a range of consecutive columns. For example, the formula A:C adds the sum of columns A through C, and the formula A:C-D adds the sums of columns A through C, and then subtracts the amount in column D.
-	A-C	Subtract the amount in column A from the amount in column C. Note You can also use the minus sign (-) to reverse the signs in a column. For example, use -A+B to add the reverse amount of column A to column B.
*	A*C	Multiply column A by column C.
/	A/C	Divide the amount in column A by the amount in column C.

Use a calculation formula in a column definition

1. In Report Designer, open the column definition to modify.

2. In the appropriate CALC column, type a formula in the Formula cell.

Complex calculations

A complex calculation can contain any combination of cell references, operators, values, and levels of nested parentheses.

For example, to compute the average of columns A and B, use the calculation formula of ((A+B)/2).

Specify report cells in a column calculation

You can refer to a specific report cell by typing a column letter and a row code. For example, B.100 refers to column B, row code 100.

You can divide a whole column by a specific report cell amount that is in the same column. For example, the calculation B/B.100 means that the column B should be divided by the value in column B, row code 100.

If the calculation references a column that depends on another column, the dependent column is resolved first. If you refer a column to another column that, refers back to the first column, a circular reference error results.

Multiply or divide in a column calculation

To multiply and divide columns, type the column letters in the order of computation, and then use the appropriate operator to separate each column letter. For more information about operators in a calculation column, see Operators in calculation columns.

Multiply or divide a column by base row

You can create a column that displays all of the values in a specified column as a percentage of a base number. This provides a method to show relationships between rows, such as a percentage of a sales row or a percentage of a total expenses row.

To multiply or divide each row in a specific column by a base row, type the column that will be used in the calculation, and then type *BASEROW or /BASEROW. For example, C*BASEROW or C/BASEROW.

Divide amount in column by the number of periods

You can divide the amount in a column by a specified number of periods. For example, the calculation B/Periods divides the value in column B by the number of periods in column B. If the calculation spans multiple columns, specify the number of periods to use in the calculation.

For example, the (B+C)/Periods formula adds the amounts in columns B and C, and then divides the result by the value of the Period for this column.

See Also

Column definition

Translate report currency

Operators in calculation columns

Advanced formatting options