Management Reporter for Dynamics ERP – An Analysis
Microsoft has released the Management Reporter for Microsoft Dynamics ERP. However, not many are clear on what the new reporting tool brings to them and what it can and cannot do. I spent some time to understand the tool better and have listed down my analysis of the features the tool provides as well as what it does well and what it could do better.
Management Reporter provides a tool that natively connects with the Microsoft Dynamics ERP General Ledger and allows business users to create reports without any IT intervention. The tool will replace the two reporting solutions in place FRx which is used for financial reports and Enterprise Reporting which is also used for financial reports but typically when you need multi-company consolidation. In essence what Management Reporter does is provide a single solution for both Management Reporting and Consolidation. That certainly is a step up from where things are today.
The core components of the solution include the following
- Management Reporter Designer:
A power user tool where you can define the report elements and what the report will look like and how it will work. The report designer provides for Report Definitions, Row and Column definitions, support formulas and financial dimensions and allows you to format the report elements. For example, the figure below shows a Row definition for an Income Statement Report. Notice that in the drop down you can include the type of Row e.g. TOT is a Total of other Rows, CAL is a complex calculation, DES is a Description such as the Row Code 130 called Revenue here etc. Column J here lists the link to financial dimensions which can include the GL Account or other dimensions such as Department, Cost Center, Division, Line of Business etc. There is native support for wild cards. For example my Sales Row in this example is the sum of All my GL accounts that start with 41 minus 4109. So the formula is written as +Natural=[41??]-Natural=. The ?? are the wild cards so any GL account that starts with 41 will be included in this calculation. The Natural reflects that I am referring to the Natural GL Account. If I was referring to another Dimension say DEPARTMENT then I would use that instead.
Figure 1. Microsoft Dynamics Management Reporter Row Definitions for an Income Statement Report
These Row definitions can be used across Multiple Report Definitions so once I define an Income Statement Row Definition I can use it in several reports such as Divisional Income Statements, Income Statement Month by Month, Income Statement Rolling Quarter etc. I do not need to create the same Row definition again. All I would do is change the column definition I use for these reports (provided I am showing the same row lines as on this report).
This combination of which Report uses which Row and Column Definition is defined in a Report Definition. For example, the screen below shows multiple Report definitions in the left navigation bar for Balance Sheet Reports, Cash Flow Reports, Consolidation Reports, Trial Balance Reports and Income Statement Reports.
Figure 2 Report Definition Folders for different types of reports
Expanding the Income Statement Reports shows several Income Statements (IS) such as a Consolidated Side by Side comparing two divisions, Income Statement, Income Statement 12 month Period Trend etc. The report definition open is an Income Statement Rolling Quarter by Department that combines the Row Definition that we had above – Income Statement Detail with the Column Definition that supports a the rolling quarter format. This Row Definition is also used in the IS 12 month Period trend report and in the Income Statement Side by Side that compares two departments.
Figure 3. Dynamics Report Definition of an Income Statement Rolling Quarter by Department
- Report Library:
Quite simply, this is a library of all the reports that users can view. However it is a lot more than that. Reports can be setup with a Reporting Tree that I touched upon in the Management Report Designer section. As an example, the report below is an Income Statement that I can see at a company level, or a Region level or a Division within that region level. For example the report shows Fabrikam Works at the company level, San Francisco Region and Denver Region below that and then the respective Sales and Service Divisions below it .e.g. San Francisco Sales Division and San Francisco Service Division. I also have a node in this tree that takes it one level further which is the Retail and Wholesale within Sales and similarly Lab and Studio within Service. The first question that comes to mind is whether the hierarchy has to be straight jacketed with one definition. Fortunately, it doesn't you can pull reports via multiple hierarchies and reporting trees. The level of flexibility if quite good and the product team has definitely done good work in putting this together.
Figure 4. Reporting Tree in Management Reporter showing 4 levels of Company, Region, Division and Sub-division
I've also included some sample additional reports here just to show what all else is possible. Below is an example of an Income statement using the row definition you saw in the Report designer section of this blow with a column definition that supports a monthly trend.
Figure 5. Income Statement from Microsoft Dynamics ERP showing a Monthly Trend using the Row definition from Figure 1
This row definition is again used in the report below but this time shows a 12 month Income statement with Actual Vs. Budget and highlighting whether it is a favorable change Vs. Unfavorable change. I thought that last piece was really cool because most reporting tools can show a net change and the percentage change but Management reporter goes one step further to actually color code it as favorable or unfavorable. For example, Sales were higher than budget so the change is shown as favorable but Sales returns are also higher than budget but this is shown as unfavorable. So the tool is more than a report writer – it also has a certain amount of Business Intelligence (BI) capability built in it. This is quite simply done by marking whether the typical entry is a Credit and Debit. It's quite simple but I've seen other ERP and reporting systems stumble on this so it is refreshing to see Microsoft get this right.
Figure 6. Example of an Actual Vs. Budget Income Statement in the Management Reporter using the Row definition from Figure 1
Another report output example is a Detailed Trial Balance report showing both the YTD and the numbers for the relevant period.
Figure 7. Example of a Trial Balance Report in the Management Reporter for Microsoft Dynamics
Finally – this report takes the cake. Management Reporter brings true consolidation capability. This report shows USA and Canada and then the Consolidated numbers. From what I understand the one limitation right now is that the multi-currency conversion is not yet there but will be there in the next version. Dynamics AX handles this through the consolidation company so it should not be an issue for someone using Dynamics AX but could be users of other ERP systems till the next version of Management Reporter comes out (which is not far away).
Figure 8. Native Consolidation Capability in Management Reporter for Microsoft Dynamics
- Security Framework:
The security framework in Management Report is built in a very similar fashion to the Dynamics AX ERP. Authentication is supported by Windows Authentication so there is native integration with Active Directory. Authorization is supported within the tool. Security can be defined at a report level, folder level or the tree/node level. Security is defined in the Management Report Designer and you can differentiate which users need Report Library access (view access) as opposed to those that need Report Designer access (design access). The screen shot below shows how permissions can be assigned at a folder or node level or you can expand that to assign permissions at the individual report level. Permissions can be inherited from top level folders.
Figure 9. Reporting Permissions can be set up at a folder, node or Report level in Management Reporter
Why choose Management Reporter?
So a natural question is why choose Management Reporter. Other than Microsoft stating that it is the reporting roadmap for Microsoft Dynamics and is putting its dollars for reporting in the Management Reporter – there are several reasons too. I've listed my top 10 with the most valuable at the end.
- Easy to use: Management Reports works and looks a lot like FRx – so for those that are used to FRx will not have a tough time using the tool. Finally like the rest of Microsoft Dynamics, it uses the look and feel of Microsoft Outlook with a left navigation bar (the "magic bar") and a point and click that is easy to understand work with.
- Multi-lingual support: Management Reporter supports multiple languages. Not a biggie for us folks in the USA but I can see it being big in French speaking Canada our neighbors further south and certainly in most of Europe.
- Architecture: The product was not a Band-Aid from a legacy product but was completely rewritten using .NET and C#. This provides a very high degree of flexibility and the tool can scale for your data as your organization grows.
- 64-bit compatibility: Anyone that has tried to install FRx on a 64-bit OS will know why this is important. Today's server OS have moved to 64-bit but today's reporting tools have not. Management reporter glides on 64-bit and is built for it. It dances and sings on a 64-bit OS. I saw at least about a 30% improvement in performance of reports if not more over FRx – that's significant!
- Report Customizability: I really liked the amount of flexibility that the Management Reporter offers. It allows you to gain total control and generate report in minutes as well as personalize reports for things like company logo, set column and page breaks and apply format over-rides. Page breaks – yes! So basic yet missing in so many reporting tools but it's there here and again well implemented
- Out of the box integration with Microsoft Dynamics: Pre-defined financial logic to tie to the GL filters for Microsoft Dynamics ERP.
- Integration with Dimensions: Yes! Dimension filters – can use a range or set of dimensions to support things like divisional reporting. A big deal for Dynamics AX users.
- Native XBRL Support: Why is this a big deal you ask? See this link on the SEC website to understand the adoption of XBRL in the financial community for filings and to see a preview of XBRL in action. Management Reporter provides native XBRL support with tagging for XBRL prior to report generation – note the word prior here. You do NOT need to generate the report before you can do the XBRL tagging. Seems such a simple requirement yet 95% of the reporting tools that do support XBRL (most don't even support that) ask for the XBRL tagging after the report is generated – which means you have to tag the report every time you generate it. Management Reporter lets you do the tagging before and get done with it. If you are a public company paying or planning to pay a lot of money to an outside service like Bowne this will hit home. Also the recent initiatives suggest while XBRL is voluntary – in a few years this may become mandatory for public companies. So you want a solution that can support this natively.
- Active Directory Integration: I always found the additional login for FRx inconvenient. Just like Microsoft Dynamics AX Management Reporter integrates natively with Active directory allowing you to open the reports without an additional login (some of the other ERPs are not integrated to Active Directory that is why I call AX out)
- Native Multi-company and Multi-source Consolidation: Yes – this is the big one. Having native multi-company consolidation is very nice. While it was never a big issue for Microsoft Dynamics AX users due to the inbuilt consolidation company – it is an issue for the rest of the Microsoft Dynamics ERP products. Even for Dynamics AX it is a step up because the Consolidation company does consolidate the data – which means your size of the database increases every time you run consolidation. So ad-hoc consolidation will be a nice win. Also Management Reporter provides multisource consolidation with flexible top down analysis so if you are one of those organizations that use multiple ERP systems for some reasons this will be a great tool for you to get rid of the excel consolidation.
Finally, I believe Management Reporter can save you some license dollars. If you are running reports in the ERP you can have your financial users use the Management Reporter tool and only require the light license as opposed to the full ERP license. That's always a nice thing! Sandeep Walia is the President & CEO of Ignify. Ignify is a technology provider of ERP, CRM, and eCommerce software solutions to businesses and public sector organizations. Ignify is a Microsoft Dynamics Inner Circle Partner and ranked in the top 18 Microsoft Dynamics partners. Ignify has been included as the fastest growing business in North America for 3 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine. Sandeep was ranked in 2010 in the Microsoft Dynamics Top 100 Most Influential People List by DynamicsWorld.