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.
Note
The Column type codes do not apply to all accounting systems. If you select a type that is not valid with your accounting system, that column is blank in the report.
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
In Report Designer, open the column definition to modify.
Double-click a cell in the Column Type row in the appropriate column.
Select a column type from the list. For a description of column types, see the following table.
Column type code |
Description |
|---|---|
FD |
Use this code to display financial data, or to display data from an Excel spreadsheet when you use a Link to Financial Dimensions column or a Link to Worksheet column in the row definition. When you select the FD column type, default settings are automatically specified in the following rows:
These default settings can be changed. For more information, see Financial Dimensions column. |
CALC |
Use this code to display the result of a simple or complex calculation specified in the Formula cell. For more information, see the “Use a calculation formula” section in Advanced formatting options. |
DESC |
Use this code to place the row description from the row definition. Although this column is frequently the first column in the report, the description column can be in any position. |
ROW |
Use this code to display the individual row codes for financial rows from the Row Code column in the row definition. For more information, see Specify a row code in a row definition. |
ACCT (Account codes) |
Use this code to display the financial data segment values or dimension values that apply to each row. For account and transaction detail reports, the fully-qualified account is printed, for example, 110140-070-0101. If ranges have been specified in the Link to Financial Dimensions column in an associated row definition, the range is enclosed in square brackets and is treated as if it were a single value. For example, [110140:110700]-070-[0101:0200]. For financial reports and high-level reports that may be a combination of several accounts, the financial data link from the row definition is printed, for example, 1100:1200. |
FILL |
Use this code to fill the cell with a character that is enclosed in single quotation marks. If you do not enter a character, the column is empty. For example, to fill a column with an ellipsis (...), enter FILL '.'. |
PAGE |
Use this code to insert a vertical page break in the report. The columns that are to the right of the PAGE column appear on a different page. |
WKS |
Use this code to display data that is pulled from an Excel spreadsheet. When you select the WKS column type, default settings are automatically specified in the following rows:
These default settings can be changed. For more information about base periods, see Management Reporter and Microsoft Excel. |
ATTR |
If your accounting system supports the use of attributes, use this code to display an account or transaction attribute in the column. An attribute, which must apply to a single full account, extracts underlying account or transaction information from the financial data. Account-level attributes display data from the account and transaction-level attributes display data that occurred at the time the transaction was posted. For more information about how to use attributes together with a row definition, see the “Account modifiers” section in Specify Row Modifier cell in a row definition. When you select ATTR as the column type, specify the Attribute Category in the Book Code/Attribute Category detail row of the column definition. For more information about attribute filters, see Attribute filter in a column definition. |
XBRL ELEMENT |
If you are using XBRL, use this code to display the XBRL element that is associated with this column type. For more information about XBRL, see (DEU) Management Reporter and XBRL. |
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.
Option |
Description |
|---|---|
BASE |
Use the base year specified at report time. |
BASE+# |
Use the year that is # years after the base year. For example, to use the third year after the base year, type BASE+3. |
BASE-# |
Use the year that is # years before the base year. For example, to use the prior year, type BASE-1. |
# |
Enter the actual fiscal year. |
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.
Option |
Description |
|---|---|
BASE |
Use the base period. |
BASE+# |
Use the period that is # periods after the base period. For example, to use the third period after the base period, type BASE+3. |
BASE-# |
Use the period that is # periods before the base period. For example, to use the prior period, type BASE-1. |
BASE-#:BASE |
Use multiple periods from before the base period through the base period. For example, to use the three prior periods and the base period, type BASE-3:BASE. |
BASE:BASE+# |
Use multiple periods from the base period through several periods after the base period. For example, to use the base period and the following two periods, type BASE:BASE+2. |
BASE-#:BASE+# |
Use multiple periods from before the base period to after the base period. For example, to use the three prior periods, the base period, and the following two periods, type BASE-3:BASE+2. |
1:BASE |
Use multiple periods from the first period through the base period. |
# |
Always use a specific period number. We do not recommend that you use this option because it reduces the flexibility of the column definition. |
#:# |
Always use a specific range of periods. We do not recommend that you use this option because it reduces the flexibility of the column definition. |
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
In Report Designer, open the column definition to modify.
In an FD column, double-click the cell in the Period row, and then select an option in the list.
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.
Option |
Description |
|---|---|
PERIODIC |
Displays the sum of the activity for the current period or range of periods. |
PERIODIC/BB |
Displays the beginning balance for the current period or range of periods. |
YTD |
Displays the sum of the year-to-date activity. |
YTD/BB |
Displays the beginning balance for the year. |
Specify the periods covered for an FD column
In Report Designer, open the column definition to modify.
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.
Note
To see which attributes your ERP system supports, see the integration guide for your system.
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
In Report Designer, open the column definition to modify.
Double-click the Attribute Filter cell for an FD column.
In the Attribute Filter dialog box, double-click a cell in the Attribute column, and then select the filter type.
To further limit the results, enter a range in the From and To columns. The From cell must contain a value.
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.
|
A |
B |
|---|---|---|
Column Type |
DESC |
FD |
Book Code/Attribute Category |
|
ACTUAL |
Fiscal Year |
|
BASE |
Period |
|
1:BASE |
Periods Covered |
|
PERIODIC |
... |
|
|
Column Width |
30 |
|
... |
|
|
Attribute Filter |
|
Reference=[01:10] |
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
In Report Designer, open the column definition to modify.
Double-click the Dimension Filter cell for an FD column.
In the Dimensions dialog box, enter the filters that you want to apply.
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.
Note
When creating reports using multiple currencies, you must make a selection in the report definition. On the Report tab, select the Include all reporting currencies check box.
To determine a company’s functional currency, do the following:
In Report Designer, on the Company menu, click Companies to open the Companies dialog box.
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
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.
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.
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).
.gif "Dn451208.collapse_all(en-us,TechNet.10).gif")
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).
Currency Display cell |
Currency Filter cell |
Report result |
|---|---|---|
Natural/originating currency |
YEN |
Y6,000 – The result shows only transactions entered in YEN. |
Functional currency from company information |
YEN |
$60* - The result shows only transactions entered in YEN and displays them in USD. |
Functional currency from company information |
empty |
$2,310** - The result shows all data in the functional currency specified in the company information. |
Natural/originating currency |
empty |
$2,250 – The result shows all amounts in the currency in which the transaction was conducted. |
* 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
Note
The results of calculation formulas do not include the values in nonprinting column ranges. For example, A:D will print zero, whereas A+B+ C for nonprinting values will calculate the value.
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. .gif "Note") 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
In Report Designer, open the column definition to modify.
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.
Note
This calculation might be incorrect if you change the calculation priority for the report. You can set the calculation priority on the Settings tab of the report definition.
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.
Note
When you use a base row calculation in a column definition, make sure that each row definition that is used with this column definition contains at least one base row for calculations. For more information, see the “Select the base row for a column calculation” section in Related formulas/rows/units.
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
Operators in calculation columns