How to Do Financial Reports in Dynamics 365 Finance & Operations

Financial statements like balance sheets and income statements in Finance & Operations can be confusing until you understand the basic structure of all reports.

Once you grasp that, and set up your foundation of rows, columns, and trees, you can quickly write many relevant reports.

Table of Contents

• What Is Financial Reporting in D365FO?
• Where Is Management Reporter in D365FO?
• Rows, Columns, and Trees
• Finance Report Designer
• Defining Rows
• Defining Columns
• Report Definition
• How to Create a Balance Sheet Report
• How to Create an Income Statement Report
• Advantage of Financial Reports in D365FO
• Limitations of Financial Reports in D365FO

Note: Even though most customers, commentators, and consultants still call it Dynamics 365 Finance & Operations or D365FO, Microsoft now technically licenses it as Dynamics 365 Finance and Supply Chain Management.

What Is Financial Reporting in Dynamics 365 Finance & Operations?

The tool used to generate reports from the general ledger in D365 F&O is called financial reports.

To get to financial reports in Dynamics 365 Finance & Operations, navigate to Modules > General ledger > Financial reports.

Where Is Management Reporter in Dynamics 365 Finance & Operations?

Financial reports in F&O has basically the same functionality as Management Reporter does in Dynamics AX (and even Dynamics GP).

So, if you are looking for Management Reporter in F&O, financial reports is the right tool for you.

Rows, Columns, and Trees

All financial reports in Dynamics 365 Finance & Operations are made up of 3 components: rows, columns, and trees.

Rows are the lines of each report. These may be accounts or groups of accounts that represent, for instance, sales vs cost of sales vs expenses.

Columns are usually the periods you are comparing, like current month and year to date. They can also be comparatives, like percent of sales or percent of variance between the budget for a month and the month actual.

Columns also look after the headers of the report.

Trees allow the user to produce multiple reports, usually by department or division, that roll up to a top-level report.

Trees are optional. If you use no tree, your report will show figures for all departments and divisions together.

Reports in D365FO also require a report definition.

Dynamics 365 Finance Report Designer

You can view and generate reports directly from the financial reports section of D365FO. But if you choose to “edit” or create a “new” report in Dynamics 365 financial reports, you may need to download the Finance Report Designer application.

The report designer application will open a window that lets you define your report, like so:

Depending on your browser, you may receive an error message when you first try to open this application, saying “Application cannot be started. Contact the application vendor.”

However, you usually don’t need to contact the vendor, and may be able to solve the problem by getting a certain Edge or Chrome extension, such as Meta4 ClickOnce Launcher.

Defining Rows in Report Designer for D365FO

1. Click on Row Definitions in the left-hand side of the Report Designer window. Then either click on an existing definition to edit or click on “New”.
2. For a given row, add a simple description in column B, like “Net Sales.” And then in column J, double click again to link to the financial dimensions.
3. To define the account categories for those financial dimensions, double-click in the furthest-right column in the next window that pops up. That column’s label is not always visible at first, but it is MAINACCOUNTCATEGORY.FYI, here’s the advantage of using categories.)
4. Click the “”…” buttons beside the “From:” and “To:” fields to select the categories you want to include from a dropdown. In this case, we’ll include both Sales (SALES) and Sales Returns and Discounts (SALERETDIS) to get Net Sales.
5. Save your row definition with a new name.

Defining Columns in Report Designer for D365FO

Columns are mostly used for showing different periods or years in a report.

1. Click on Column Definitions in the left-hand side of the window. Then either click on an existing definition to edit or click on “New”.
2. Set headers for each column. Header 2 is useful for inserting dynamic values, like the calendar month to which the column refers.
3. Set the fiscal year for each column (usually either “BASE” or “BASE-1”).
4. Set the periods and periods covered. For instance, a column with Fiscal Year BASE- and Periods Covered “Periodic” would show figures for the current month in the previous fiscal year.
5. Save your row definition with a new name.

Report Definition in Report Designer for D365FO

Report definition is where you select the row definition, column definition, and trees for the report. In the report definition, you also set:

• Parameters for the base period for each column.
• Rounding: For instance, do you want this report rounded to the nearest thousand dollars or million dollars?
• Where you want the report created. By default, the system puts the report into a library, but you can also publish it as an Excel spreadsheet, etc.

Select Report Definition from the right-hand menu and either choose an existing report to edit, or create a new one. Then in the “Building blocks” section, choose the row definition and column definition.

Later when you want to run the same report for a different period, you can just regenerate after changing the Base period, and the Base year as required. The system can be set to the system date minus one (S-1), so it auto generates the previous month’s statements.

Tip: Reporting by Categories vs Reporting by Account Numbers

The advantage of using “categories” of accounts to build a report is that the report will automatically add new accounts within the category. For instance, if you add a new savings account, it will automatically be added to a balance sheet report built by categories.

On the other hand, if you do the row spec for your balance sheet by account number instead of category, you’ll have to remember to check that each new bank account, etc., gets properly to the report.

Examples of Financial Reports

How to Create a Balance Sheet Report in Dynamics 365 F&O

To make a common balance sheet by categories in financial reports in Finance & Operations, you could set it up as follows:

1. Set your rows as cash, accounts receivable, inventory, prepaid, fixed assets, other assets, accounts payable, taxes payable, other payable, long-term debt, owners’ equity, and net P&L YTD.
2. Set your columns as current month, last month, and last-year current month.
3. Don’t use trees unless you are reporting for a multi-company organization, in which case you would use trees by company.
4. For report definition, use the balance sheet row, balance sheet column, and no tree (unless it’s multi-company).

How to Create an Income Statement Report in Dynamics 365 F&O

To make a common income statement by categories in Dynamics 365 Finance & Operations, you could set it up as follows:

1. Set your rows as sales, cost of sales, direct expense, and admin expense.
2. Set your columns as current month, last-year current month, current year to date, last-year current YTD. You could also add, between those, percentage of sales. (Your board of directors will ask if percentage of sales has changed.)
3. Set your trees to department, division, or both.
4. For report definition, use the income statement row, income statement column, and trees for department and/or division.

Advantage of Financial Reports in D365

The financial reports tool has a significant advantage over SSRS or even Power BI: It allows you to do nonsequential groupings of accounts, like liabilities and assets. If a certain account switches from a liability to an asset, financial reports in D365 can still report on it correctly.

SSRS and Power BI work sequentially through accounts, taking, for instance, all accounts numbered 1000-1999 in a certain group. So it can be much easier to write a financial statement in financial reports than in SSRS or even Power BI.

Limitations of Financial Reports in Dynamics 365 Finance & Operations

There are 3 important limitations of financial reports, which are that it is GL only, has limited granularity, and it isn’t speedy.

• General ledger only: Financial reports works with the General Ledger only. You can’t conveniently bring in data from other sources.
• Limited granularity: Financial reports works at the account summary level. For instance, if you don’t have a separate GL code for each employee, and you want to drill into their meal expenses and see who spent how much, financial reports won’t do that.
• Not speedy: This is why financial reports is usually configured to work in the background, so you can perform other tasks in your ERP while you wait.

If you want to get more granular or bring in other data sources, you are pushing the limits of financial reports in D365FO, and it might be time to engage a partner and explore Power BI.

Advanced Financial Reports in Dynamics 365 F&O

Some customers need more advanced reporting features than are practical for them to build in-house with the financial reports tool. Common scenarios we see are:

• You have a specific sub-report in Excel that you are currently punching in manually, taking the info from General Ledger. (For instance, you may need to do this to get details on amounts spent on meals and entertainment.) But you want to automate the data entry and be able to drill in and report on who spent what.
• You want a drilldown sub-report that shows certain lines or calculations for a business- or industry-specific allocation report. We see that in forestry for instance, where it’s common to want reporting on “income per board-foot.”
• You want to have detailed reporting on sales and cost of goods numbers for specific product types, plus comparative percentages. (For instance, this would let a computer hardware business see the margin on monitors, CPUs, service, and more.) You can technically do this in financial reports, but it would be a good use case for Power BI.

If you want advanced reporting features like these, you should contact us or another Dynamics partner you trust about configuring/customizing your reports, or leveraging Power BI.

Financial Reports Training for Dynamics

The most efficient kind of training is usually to have a partner train the trainer at your organization, focusing the training on a small number of your team members, to help them become power users.

In our training sessions, we usually show those members of your team how to write the category income statement and category balance sheet. We do those with you as the training tool. We generally teach on the rows first, then columns, then trees, and then report definition.

Sometimes the columns used for these 2 reports become templates that work across the business.

We also show those team members how to make cross references to help them flag any errors. That way you can rely on the numbers you see in your system.

To learn about other ways to show and share data in Dynamics 365 F&O, read how to:

• Save a Query
• Create and Share Saved Views

You may also find our glossary of Dynamics 365 F&O terms helpful.

Here at Encore we support customers on Finance & Operations (including with the transition from AX to D365). To discuss training, report configuration, Power BI, or other aspects of your business software with our experts, please contact us.