Financial reporting in Dynamics 365 can feel like navigating a labyrinth – time-consuming, complex, and demanding of valuable resources. But what if there was a tool that could transform this process, empower you to generate reports with ease and efficiency? Dynamics 365 offer Management Reporter – a powerful add-in that unlocks a new world of financial reporting possibilities.
In this blog I'll try to explain various components that make up Management Reporter. Shedding light on each element and its role in the bigger picture. By understanding these components, you'll be well-equipped to unlock the potential of Management Reporter and streamline your financial reporting.
Building Blocks of Management Reporter:
Management reporter consist of components that we can combine into report definition to build a financial report. Followings are the four main building blocks
a. Row definition (Mandatory in Report definition
b. Column Definition (Mandatory in Report definition) c. Reporting Tree (Optional in Report definition) d. Report Definition
1. Selecting Dimensions to Use in MR
In D365, we may have multiple financial dimensions on which we are recording data and while creating financial report created through Management reporter we require the same level of granularity in our reports. To enable FD on MR, do the following steps
Go to General Ledger > Ledger Setup > Financial reporting setup
Move required financial dimensions from available to selected
In attributes we can include vendor and customer so we can use them in our reports for filtering and report design
Once FD and attributes selected click on save
2. Running MR
To launch MR tool. Go to General Ledger > Inquiries and Reports > Financial Reports
Click on New
An app will be downloaded on your system and it will launch the MR.
3. Row Definition:
In Management Reporter, imagine row definitions as the building blocks for your financial report lines. These definitions act like a recipe, specifying the data source (Main accounts) for each row, how it should be formatted (currency, decimals), and even any calculations needed (percentages, logical functions). The true power lies in their ability to standardize report creation. By defining a row definition once, you ensure all reports featuring that row type present the data consistently.
They're also reusable, saving time by eliminating the need to redefine common rows across reports. Plus, row definitions offer flexibility, allowing you to incorporate complex calculations directly within your reports.
To create new Row definition, click on New and then click Row definition
A blank canvas having cells, will open, lets explore each element in row definition.
A - Row Code: The Row Code is a unique identifier for each row in the report. It helps in referencing rows in formulas and other report elements.
B - Description: The Description provides a concise label or explanation for what the row represents. This is the text that will typically appear in the report to describe the data in the row.
C - Format Code: The Format Code defines the type of row and its function within the report. Common format codes include "CAL" for calculated rows, "TOT" for total rows, and "DESC" for description-only rows. For none, it will pick its description from Value defined in Link to Financial dimension
D - Related Formulas / Rows / Units: This powerful feature allows you to connect the row to other elements in your report, offering various possibilities.
a. You can write formulas within a row to calculate specific values based on other data in the report.
b. You can link a row to other rows, enabling you to create hierarchies or group related data.
E - Format Override: This allows you to override the format code defined in the column definition for this specific row.
Example: If the column definition uses a general currency format, you might want to use a specific currency format for a foreign currency account in a particular row.
F - Normal Balance: This indicates whether or not we want to reverse the Account balance, for example, Revenue have credit amount and we leave normal balance blank, our report will amount with negative sign. By selecting C in it, the sign of amount will gets reversed and revenue amount on report will show with positive balance
G - Print Control: Print Control determines if and when the row should be printed on the report. Blank means it is set to always print. It can be changed like suppressing print of rows if the amount is zero or only print under certain conditions, or suppress printing under specific circumstances.
H - Column Restriction: Column Restriction specifies which columns in the report the row's data should appear. This is useful for multi-column reports where different types of data are shown in different columns.
Example:
Column Restriction: A
(the data for this row will appear only in columns A)
Column Restriction: B, E
(the data for this row will appear in columns B and E)
This functionality can be used to show a stepped layout in a report where all data rows in column A and total show in column B
I - Row Modifier: This allows you to override the settings in the column definition for a specific row. You can modify the following aspects:
Account modifiers: Change the way accounts are treated in the row, such as using beginning balances, specific periods, or offset periods.
Book code modifiers: Use a different book code for the row compared to the default book code. It can be use if you want to reference budget data instead of Actuals
Attributes: Include additional attributes with the row code. Using attribute you can define from which posting layer you wants to pick data. If you are maintaining separate book for taxes, using this feature you can pick data from the custom layer.
J - Link to Financial Dimensions: This allows you to connect the row to specific financial dimensions in your Dynamics 365 Finance system. Main account is also one of your FD so we can hardcode main accounts against each row or create combination of main accounts
Sample Row Definition
Let’s look at a portion of row definition that how it looks. I've blurred out some detail but the overall concepts can be understood
So, here is a Revenue portion of an income statement report
Description column has the narration that will reflect on income statement report in each row
Link to Financial dimension contains the ledger accounts.
Normal Balance: As revenues are in –ve (credit), so Normal Balance indicating that it is a C and on income statement it will reflect as positive figures.
CBR in Format code: CBR (Calculated Based on Row) is a format code used in row definitions. If there's a need to display a column as a percentage of a total (such as revenue), the initial step is to set this up in the row definition using the CBR format code. In the 'Related Formulas/Rows/Units' field, specify the row that will serve as the denominator in the percentage calculation.
In above screenshot on the first row I've set row 370, which is basically total revenue
, as CBR
TOT in Format Code: TOT is basically total of other rows, we can give the range of rows like in Row 370 we are instructing the system to sum value from row 130 to row 340, the : sign represents the range
4. Column Definition
In Management Reporter, column definitions act like the architects of your report's structure. They determine the information displayed in each vertical section, moving beyond the line-by-line control of row definitions. Column definitions focus on the data perspective, allowing you to show budget vs actual figures, year-to-date comparisons, or departmental breakdowns within a single report. Like rows, they can dictate formatting and even include calculations to manipulate data within the column itself. The significance lies in their ability to offer flexibility. You can present data from various angles while maintaining a consistent report structure.
They're also reusable, saving time by creating templates for common data perspectives used across different reports. Ultimately, column definitions empower you to tailor your reports to specific audiences by providing the right level of detail for their needs.
To create new Row definition, click on New and then click Column definition
A blank canvas having cells, will open, lets explore each element in column definition.
1 - Column Type: Specifies the type of data that the column will display, such as amount, description, period, or calculations. Most common types we use are:
1. FD (Financial Dimension): Used for columns displaying financial amounts.
2. DESC (Description): Used for text descriptions.
3. CALC (Calculation): Used for columns that perform calculations.
2 - Book code / Attribute Category: Defines the source of the data, such as actuals, cashflow forecast.
3 - Fiscal Year: By default, Fiscal year is Base, it means that it will consider the fiscal year of the date we give in parameter while running the report. So if we want to show previous year data, we can select as Base -1
4 - Period: By default, Period is Base, which means that it is the range of date from month start to date selected in date parameter while running the report. Using this logic we can define “Base +” and “Base - “ with multiple combinations to get required data.
5 - Period Covered: By default it is periodic, if in our d365 period is define as months, then periodic means it will get us the data for from start of month up till date selected in report parameter. If we want to get year to date data, we will select YTD in the periodic
6 - Formula: If in our Column type, we’ve selected CALC, Then this will enable and we can enter functions in this field If you are showing different main account values in columns and require to show a column consolidating all values, you can sum columns like B+C+D
7 - Column Width: By default, it is Auto fit which means the column width will adjust according to the character in it, But is we want column to be with one specific width, we can write digit in here. Maximum character length is 255
8 - Extra spaces before column: Adds additional spaces before the column content for better readability
9 - Format / Currency override: Applies specific formatting (e.g., number, currency, percentage) or overrides the default currency.
10 - Print control: Controls how the column is printed (e.g., suppress zeros, hide if empty).
11 - Column Restrictions: Limits the data displayed in the column based on conditions or expressions.
12 - Reporting Unit: Restricts data to a specific reporting unit (e.g., company, department). It gets data from Reporting Tree
13 - Currency Display: It shows that from which currency amount needs to be fetched, Reporting currency, transaction currency or accounting currency
14 - Currency Filter: Filters data in column based on specific currencies.
15 - Dimension Filter: Filters data based on specific dimension values (e.g., State, Bank, Department).
16 - Attribute Filter: Filters data based on specific attribute values
17 - Start Date: Specifies the beginning date of the date range you want to include in the report.
18 - End Date: Defines the ending date of the date range for the data displayed in the column.
19 - Justification: Sets the alignment of the column content (e.g., left, right, centre).
20 - Line numbers Display: This column type is dedicated to displaying sequential line numbers for your report data. And this option is only available when our column type is Line Number
Sample Column Definition
Let’s have a look at a Column definition of an income statement report
Column A will show the description of Rows, in our case it will be main account names
Column B is basically quantity column, some accounts are created as statistical accounts and have some specific Main account sequence, so in Dimension filter it is applied to show data only from statistical accounts
Column C is showing Amounts, so in Dimension filter Main account range is defined so it only picks data for amount and not for statistical accounts
Column D is a calculated column, it shows the value in comparison with the total revenue, so in Formula we used C/BASEROW , C is amount column and Base row we defined in row definition as CBR (Refer to CBR in Format code)
Column E to Column G is copy of Column A to Column C, the only difference is the Periods covered, Column A to C are periodic whereas Column E to G shows YTD data
5. Reporting Tree
In Management Reporter, reporting trees act as your organizational chart for financial data, mirroring your company structure. Departments branch out from a central core, just like in a tree. These trees group related rows (think departments in a report) under specific branches, promoting a clean layout and unlocking powerful features. Users can collapse and expand for focused analysis or a quick high-level view. Reporting trees even offer flexibility in data roll-ups, saving time by allowing users to drill down to specific areas of interest. They also enhance security by controlling data access based on user permissions and department visibility. The reusable nature of reporting trees makes them a valuable asset, allowing you to leverage them across multiple reports.
To create new Reporting Tree, click on New and then click Reporting tree definition
A blank canvas having cells, will open, lets explore each element in Reporting Tree
A - Company: This is the root node, representing your entire organization within Dynamics 365. All other elements branch out from here.
B - Unit Name: These are sub-nodes under the Company. Each Unit Name represents a specific segment within your organization, like a department, subsidiary, or cost center. You can have multiple Unit Names with unique structures.
C - Unit Description: Here, you can provide a brief description for each Unit Name, further clarifying its purpose within the report.
D - Dimensions: Dimensions allow you to categorize and analyze your data based on additional attributes like customer, product, or location. You can configure which dimensions are relevant for each Unit Name
E - Row Definition: This defines the structure of each row in your report. It specifies which financial data (accounts, calculations, etc.) will be displayed and how they are grouped.
F - Financial Dimension Link: This element establishes a connection between the Row Definition and specific dimension values. It allows you to filter data based on specific dimension combinations within a Unit Name.
G - Page Options:
H - Rollup %: This allows you to define how percentages or other calculated values are aggregated when rolling up data from lower levels to higher levels in the reporting tree.
I - Unit Security: This empowers you to restrict access to specific Unit Names and their data based on user. This ensures data confidentiality within your organization.
J - Additional Text: This provides a space to include any additional text or notes associated with a particular Unit Name, enhancing the clarity and context of your reports.
Sample Reporting Tree
Let’s have a look at a Reporting Tree
Column A define that we are picking data from USMF legal entity
Column B shows break down our data into multiple units. First row is consolidating (give data of all departments) and after that each department will show their separate data in report
Column C is further description of Unit name
Column D is filtering the unit on the basis of corresponding dimensions
Column E is showing to which row definition this reporting tree is tagged
Column F shows to which financial dimension we will be picking the data . FD1 in our case is Main accounts so the unit will be filtering Main accounts data on the basis of Dimensions defined in column D
Column I define which user can access the specific unit name. If we wants to expose manager of lot to their specific lot, in this column we need to define that
6. Report Definition
Once all the base elements of a report is created I.e., Row definition, column definition and reporting tree, we need to combine those elements in report defilation
In new report definition we will select relevant row, column and reporting tree from drop downs
In new report definition we will select relevant row, column and reporting tree from drop downs
Hope this blog help you in learning Management reporter. Cheers!!!
Comments