Microsoft Management Reporter: using days in a month in a row calculation

Microsoft Management Reporter is a robust program that offers diverse reporting with real-time data. It allows users to easily create, generate, secure and publish financial statements. 

Reporting includes Profit and Loss (P&L) statements, balance sheets, and cash flow reports.

Microsoft Dynamics GP cannot create these reports out of the box, though it does have a built-in report feature called Advanced Financials. However, this feature is not user friendly and it doesn’t provide the robust features Management Reporter does. Even with the advanced reporting, there are some calculations that are more difficult as they are not options in Management Reporter, such as calculations on the days of the month.

The Problem:

Out of the box, Management Reporter only supplies calculations for periods and not for days of the month. The program pulls only the period information from the fiscal year setup within Microsoft Dynamics GP. I had a client who wanted to break down the calculations based on the number of days within each period. By default, Management Reporter doesn’t have this capability. This means you cannot calculate based on the number of days within any given period and can only rely on calculations based on the number of periods.

One common workaround for this issue is to create an Excel document with the number of days for each period, then pull that data into Management Reporter to add to an existing report. This requires storing the Excel sheet on a network drive so it’s accessible to everyone who would run the report. To pull the dates into Management Reporter, you would have to link the report to the Excel sheet with the breakdown of days for each month. Directions for this process can be found here.

This is a lengthy process and the same steps are required for each report that uses the days in a period. A further downside to this method is it requires another program, Microsoft Excel, to pull the period dates into Management Reporter.

The Solution:

A better solution utilizes the two programs you’re already working with, Microsoft Dynamics GP and Management Reporter. Instead of using Excel to graph the data, you would create a unit account and record the number of days in each period. By using unit accounts, you can pull the days of the month into a calculation within Management Reporter with little upkeep. To do so, take these simple steps:

• In the Unit Account window in Microsoft Dynamics GP, assign an account number.
• In each period, the net change will be the amount of days within that specific period.
• In Management Reporter, specify the unit account in a non-print row.
• You can now apply the days of a month to a row calculation.

Once created, users can add the unit account into the row format as a hidden row. This will pull the number of days in a period into the report. Users can apply that row in any number of calculations. For example, if a user wants the average daily revenue for a period, they can divide the revenue by the unit account and have the daily average.

Management Reporter is a complex program that we have many workarounds for. If you’re having an issue with a calculation in Management Reporter or wonder if a specific report is possible, please reach out to an Enavate expert to discuss what we can do for your reporting needs.

In addition, if you want to learn how to better manage and distribute reports, watch our on-demand webinar: Management Reporter: Tips and Tricks for Streamlined Report Distribution.

About the Author

Samantha Vislay, MCSE is an Dynamics GP Techno-Functional Consultant located in Pittsburgh, Pennsylvania and has been working with Dynamics GP since 2015. She is skilled in training, implementation, integrations and customizations.