Understanding of Configuration of Column Definition in Management Reporter
Column Definition: The column definition will define the vertical information of the Management Reporter.
1. Column Type:
The following are column types:
2. Book Code/Attribute Category:
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.
3. Fiscal Year/ Period/ Periods Covered:
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:
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:
A calculation column (CALC) in a column definition supports complex calculations and can include the +, –, *, and / operators, along with IF/THEN/ELSE statements. Additionally, a calculation column can refer to any other column, including subsequent columns. The formula can be up to 1024 characters long.
To express the calculation result as a percentage, use a special format mask.
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:
5. Column Width:
The Column Width cell specifies the number of characters to use for the width of this column on the printed report. Column width is particularly important for columns that contain amounts (column type CALC, WKS, or FD), descriptions (column type DESC) or fill (column type FILL). The default column widths are as follows:
14 characters (including cents, commas, and parentheses) for amount columns
3 characters for the description columns
characters for a fill column
The maximum width for any column is 255.
6. Extra Spaces Before Column:
The Extra Spaces Before Column cell specifies the width of a separator between the adjacent columns in the column definition. The Extra Spaces Before Column setting affects all column detail rows, but not the column header rows, for this column.
Use this option to separate groups of columns or to add a few spaces before the description so that the description column is indented from the left-justified titles that are in the report.
The default number of spaces between each column is two. You can change this option on the Settings tab in the report definition
7. Format/Currency Override:
The Format/Currency Override cell specifies the formatting of the decimal, currency, and percentage amounts in this column. This formatting overrides any formatting that is specified in the report definition or system defaults.
The Print Control cell can contain codes that adjust the display or the printing characteristics of a column.
8. Column Restrictions:
The following table describes the column restriction codes.
10. Reporting Unit:
This will be covered in Reporting tree definition
11. Currency Source:
Specifies the type of currency (natural, functional, or reporting) in which the transactions are displayed. This functionality is sometimes called 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.
12. Currency Filter:
Specifies a currency filter. Only transactions entered in the selected currency are displayed in the report.
13. XBRLCurrency /XBRL Dimension:
To use XBRL Currency and Dimension the company must be associated with an XBRL entity. Management Reporter supports XBRL, or eXtensible Business Reporting Language. XBRL functionality in Management Reporter allows you to prepare, publish, and exchange financial statements in a common format. Regulating authorities create taxonomies that regulate the tagging of data in this common format.
Companies must choose line item tags from the regulator’s taxonomy and map the tags to their financial figures. The XBRL functionality in Management Reporter automates this data-tagging process.
14. Dimension Filter:
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 adhere 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=, +Account=, Department=[01?]
To receive all departments for a specific account, you can exclude the Department dimension in the dimension filter. For example,
+Account=, the Department is treated the same as:
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.
15. Attribute Filter:
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 Descriptions 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 in conjunction 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.
16. Start Date/End Date:
The Start Date and End Date cells restrict data in FD columns to specific dates. This restriction is useful for daily or weekly sales reporting, cash analysis needs, and other date-sensitive reports.
You can enter a specific day of the period. For example, you can enter 3 to indicate the third day of the period. The ability to add a specific day of the period adds flexibility because you do not have to select the actual dates again when you run the report for the next period.
The Justification cell is used to apply justification formatting to a description column in a report. This option affects only the column descriptions, not the actual values.
Apply column justification
- In Report Designer, open the column definition to modify.
- Double-click the Justification cell.
- Select a value in the list.
None – No justification is applied.
Left – Left-aligns the column descriptions.
Centre – Centres the column descriptions.
Right – Right-aligns the column descriptions.
Here we have taken an example of Balance sheet Row Definition.
Click on the button to open the Column definition in Report Definition.
Latest posts by Waris Mansuri (see all)
- What is Functional Location in Asset management Dynamics 365 F&O? - June 4, 2021
- A Guide to Asset Management in D365 Finance and Operations - May 26, 2021
- Understanding the configuration of Row Definition in Management Reporter - September 17, 2019
4 thoughts on “Understanding of Configuration of Column Definition in Management Reporter”
Thanks for making and sharing this article with others ..Very much in detail & in-depth and mention each point with screenshot will help much.Thanks
Thank you very much Pankaj
Thanks Waris! Is it possible to show one of the segments in its own column? currently in column definition setting column type to ACCT shows the full account string. We want to see 2nd segment (Division) of each full account string (Natural-Division-Dept) also on a different column on report. Right now we’re exporting to excel and using the =mid() formula to parse the 2nd segment out of account string into its own column. Can we do this in MR column definition somehow?
If you want to show all dimension on different column than it is possible using Dimension filter on Column definition. e.g. Natural, Division and department on different column.