Many of our Solver clients have come from using Microsoft’s Management Reporter and FRx. In fact, our first Solver client was using FRx and looking for a replacement. Thirteen years later comes this article.
Many organizations are using FRx or Management Reporter today. Microsoft Dynamics GP, Dynamics 365 Finance, and, perhaps, Dynamics SL users may be long-time users of those products. As a long-time GP consultant, I created many reports in those two reporting applications, going back to the DOS days.
A little history: Management Reporter took over from the discontinued FRx financial reporting tool in 2011. Then, in 2016, Microsoft announced it would cease further development of Management Reporter. While support for the tool will continue until 2026, it’s a good idea to explore alternative solutions for your financial reporting requirements now.
Maintaining these older solutions can present challenges. Some of those hurdles include:
- System compatibility: As you update hardware, you may run into issues.
- Lack of development: Both products no longer are updated.
- Feature loss: Management Reporter, for example, is losing functionality related to including Excel data for Dynamics 365 Finance clients.
- Need to include other data: Both products only work with the data provided by the General Ledger, but many organizations have data outside of the General Ledger that needs to be included.
If your organization has encountered any of these challenges or others, like wanting integrated planning, read on to learn about alternatives that work with all Microsoft Dynamics applications.
The FRx & Management Reporter Way
In FRx and Management Reporter, you created reports by combining rows, columns, and trees.
Figure 1 Row Definition in Management Reporter
One of the features in FRx and Management Reporter (F/MR) is the ability to include spreadsheets and links to other row definitions. For those who know, this functionality is done by creating a Row Link.
Figure 2 Row Links
In the above image, you can see the five types of links. Unfortunately, not all Dynamics clients are able to use F/MR, as Microsoft decrements the functionality by removing the ability to link spreadsheets.
For columns, which are used to determine the periods to be displayed and perform horizontal calculations, you use the Column Definition window.
Figure 3 Column Definition for Trended Periods
Both row definitions and column definitions can be quite long vertically and horizontally, respectively. Row definitions may utilize many non-printing rows used in calculations. Column definitions may utilize non-printing columns and calculations. In addition, the more periods that need to be displayed, the more columns you need.
Lastly, let’s not forget the reporting trees.
Figure 4 Reporting Tree
Reporting trees are very useful because they enable you to report at various levels of the organization. Business units can be rolled up and reported at a summary level. Reports can be distributed based on tree security. There are other features available in trees.
Put the three elements together and you have a report definition.
Figure 5 Report Definition
Users of F/MR are very familiar with these components. Why mention them? When considering another tool, you need to assess if and how the new tool will handle the functionality you need. Also, most people understand new things based on knowledge of other things. The knowledge helps you make a relatively seamless transition from one tool to another.
Microsoft Fabric & Power BI
Microsoft Power BI started as a standalone software as a service (SaaS) product designed for data integration, analysis, and visualization. Now, it has become part of Microsoft Fabric. You can still use Power BI on its own if that suits your needs, or you can leverage the enhanced features of Fabric alongside Power BI to do more with your data.
Many organizations are looking at Power BI and Fabric as replacements for F/MR. There are good reasons to consider these tools:
- Power BI and Fabric provide data analytics that enable organizations to see and use data outside of traditional report formats.
- There’s connectivity to SQL Server, other Fabric components, Business Central, and other technologies used by Microsoft applications.
- There is a wide range of information to help users learn the functionality.
- Power BI and Fabric, as Microsoft solutions, provide a natural fit for Dynamics 365.
Let’s look at reporting options in Power BI.
Figure 6 Power BI Report
Power BI reports are built on a collection of components:
- Source Data
- Data Model
- Power Queries
- Measures
The source data can be from multiple sources:
- Dynamics 365 Finance
- Dynamics 365 Business Central
- Excel
- Other
These sources are usually imported into the Power BI data model but can be a live connection depending upon the data source. Even when you have the option for a live connection, there are good reasons for importing into the Power BI data model. Please read this Microsoft Learn article on the two options.
Here is an example of the data model used for the report shown in Figure 6.
Figure 7 Data Model
Once you have access to the data, you can build measures to calculate the rows and columns in your report. Measures are created using Data Analytics Expressions (DAX). The key to creating financial reports in Power BI is DAX. This Microsoft Learn article can help you get started with DAX.
Here is an example of a DAX measure that calculates Revenue in Figure 6.
Revenue = CALCULATE(sum('Module General
Ledger'[monthly amount]),'Dimension Account'[power bi
report key]=1)*-1
This measure is calculating the revenue amount using the monthly amount from the General Ledger and using a filter based on a Power BI report key stored in a table. Every row with an amount is calculated using a measure. Like Excel functions, measures can reference other measures.
Reports can be provided to users in different ways. One option is to have users log in to Power BI and view the reports. A second option is to use Power Automate to distribute reports. There are also third-party solutions that can provide report distribution options.
Reports also can be shared using one of these options:
- Exporting to PowerPoint, PDF, and Excel
- Microsoft Teams
- Subscriptions
Another option for Power BI reports is Report Builder. Report Builder is like creating reports in SQL Server Reporting Services. It enables you to create paginated reports that can extend over multiple pages.
Creating and using reports in Power BI is much different than in F/MR. Whereas the latter reporting tools are intended for accountants because of their spreadsheet-like report design experience, Power BI is split between multiple roles: data engineer, report author, and report consumer.
Solver xFP&A
For a report design experience designed more for business users, Solver provides a data warehouse-based reporting and planning environment. Solver includes a Microsoft Azure SQL-based data warehouse that can be considered a data mart for reporting purposes. The data can be pulled from many sources, like Dynamics 365 Finance, Dynamics 365 Business Central, and Excel.
Figure 8 Solver Architecture
In Figure 8, you can see how the system is designed. Data is pulled from data sources and stored in the data warehouse. Reports, plans, and dashboards are built upon the data warehouse.
Reports are built using Excel with an add-in and deployed in the Solver portal.
Figure 9 Report Designer
To build reports, a user starts a new report from the portal to launch Report Designer. Report Designer is Excel plus a Solver add-in. In Figure 9, you can see the Report Designer pane, giving you access to the data in the data warehouse, Excel, where you place a combination of text, data fields, and formulas, and the Layout Editor, where you filter and group the data.
When learning new products, many people ask, “How do I do [insert function] in [product x]?” With Solver, the approach is more, “How would I do [insert function] in Excel?” For example, to sum amounts, you use the standard Excel SUM function.
Figure 10 Report Output
Many F/MR report users like the drill down functionality. In Figure 10, you can see there is a drill down option. Solver includes drill down functionality built-in, but report designers can create custom drill to definitions. Power BI reports don’t have traditional drill down functionality built-in, but report authors can create drill through reports, giving the same functionality.
Figure 11 Power BI Drill Through
One feature available in Management Reporter and older versions is the ability to include Excel spreadsheets. This functionality can be handled in Power BI by importing an Excel spreadsheet into the data model and in Solver. In Solver, you can import a spreadsheet or use a Planning template to enable the same spreadsheet data to be entered and stored in the Data Warehouse.
Allocations are another important area for organizations to manage in reports. In F/MR, organizations would use percentages from Excel or built into the reports and, perhaps, link spreadsheets with allocation percentages. In Solver, allocation data can be stored in the data warehouse and used in reports. Another option Solver provides is business rules. Rules enable calculations, allocations, eliminations, currency translations, and other processes to be automated.
Figure 12 Example of Allocation Report
One advantage Power BI and Solver have over F/MR is the ability to include non-financial data in reports. For example, sales, manufacturing, and payroll data can be used in reports in Power BI and Solver. Reports can be built that include data from multiple data sources, making the latter two reporting applications more flexible. In addition, Solver’s Planning functionality enables organizations to build plans based on non-financial data.
Figure 13 Personnel Planning Template in Solver
In Summary
Microsoft Dynamics users who need to build financial reports have options like Management Reporter, FRx, Power BI, and Solver. Whereas F/MR tools are no longer being developed, Power BI and Solver continue to be enhanced. In addition, Solver and Power BI enable reports to be built, including non-financial data. Lastly, Solver enables planning templates to be created using financial and non-financial data.
To learn more about Solver, Power BI, and Fabric services at Forvis Mazars, connect with us today for more information or to request a personalized demo.