Report Definition in Management Reporter

By Annette Brown, Senior GP Implementation Consultant

Are you using FRx today and you know you need to move to a new reporting tool? Watch our recorded webinar, which covered Management Reporter and what it can offer you in reporting as well as how you can easily migrate from FRx.

In Management Reporter, what is the base period? 

Think of “base” as a parameter getting filled in when the report is run.  You fill this in by choosing a Report Date.

“Base” Period refers to the “month” chosen on the Report Definition.  The green Arrow is the “Base” Month, the Red arrow is the “Base” Year.

NOTE: Base Period is defined in the way you have set up the fiscal periods in your GP.  Some have just 12 to represent monthly buckets, some have 13 (and use 1 for an adjusting period), and others have set up 52 periods and their periods represent weeks.

Report Definition

“Base” is used in the Column Definition to adjust the time frame returned to the report.  This is how you make the report dynamic and give you the appropriate “Period” and “Year”.  It is what allows you to run the report for any Report Date chosen at run time.

Column Definition

Where can I get training on how to set up our reports with columns and rows?

InterDyn BMI offers in-depth training of a variety of classes and per-company based training classes. Annette Brown would be happy to discuss options if you are interested in further training.

How do you switch the report definition screen for multiple companies?

This all depends on if you are using a tree in your report and how your tree is set up. You can do this by clicking on the arrow in the company name.

However, in the below report definition example, I am using a tree. Therefore, it will pull the company from the tree setup.

Where can I get sample reports?

NOTE: You will have to be on at least CU6 to have this option and see sample reports.

To learn more, you can watch Let’s Learn Management Reporter on YouTube now.

Advertisements

Download Management Reporter 2012 CU9 Today!

Management Reporter 2012 CU9 has been posted for download!

This release contains enhancements to the Management Reporter Web Viewer around opening and refreshing reports along with additional troubleshooting information in the Configuration Console related to data issues in the ERP.

Here’s a summary of the new Management Reporter CU9 features:

•View a list of previous month’s reports and open them in the web viewer

•Refresh a report in the web viewer

•Publish a report from the web viewer

•Headers and footers are shown by default in the web viewer

•Enhanced data detection in the Configuration Console

•Support for SQL Server 2014

•Additional fixes for product defects

You can find the download for Management Reporter CU9 here: https://mbs.microsoft.com/customersource/northamerica/MR/downloads/service-packs/MROverview

Questions? Contacts us today!

 

Source

How To Link an External Spreadsheet to a Management Reporter Report

Have you ever wondered how you could link an External Spreadsheet to a Management Reporter report?

The following is an example of how to get your report to present GP and Excel data from two different spreadsheets.

The two screen shots below reflects the Excel spreadsheet data we want to use. This is a Management Reporter report.

EXCEL SPREADSHEET – Sheet 1:

Excel Spreadsheet 1

EXCEL SPREADSHEET – Sheet 2:

Excel Spreadsheet 2

To build the report, you may want to build the components in the following order – Row, Tree, Column and the Report Definition.
ROW DEFINITION:

Add the Row link to the Row format.  Edit>Row Link.  In the Row link window you can add more than Excel file link.

Row Links

If the Excel file that you are linking to has multiple columns that represent different periods (for example, a worksheet that has a detailed, 12-month Budget Forecast), you can use the /CPO (Column Period Offset) option to match the accounting periods in the Column Definition with the appropriate columns in the Excel worksheet.

You can use this technique with either the Combined Worksheet link or the Separate Worksheet link.
To use the /CPO option, in the row definition, type the cell address that points one column to the left of the first period (column). Then, add the /CPO option at the end of the cell address.

CPO

TREE FORMAT:

Add unit names to the tree that will define the Spreadsheet Information to review.  You will need to assign a Row Definition before you can select the Worksheet link.  The Worksheet Link Column (G) will allow you to select any link you’ve defined in the Row format.

Tree Format

COLUMN FORMAT:

Create a column type WKS and then in the Report Unit Field select the Summary Unit you created in your Reporting Tree definition.

Column Format

REPORT DEFINITION:

Create you Report Definition using your new Row, Tree and Column.  You will want to make sure you name the Use Row Definition from the Reporting Tree Check Box.

Report Definition

 

REPORT GENERATED:

Report Generated

Report Generated 2

Visit our website to learn more about Microsoft Dynamics GP and our full product line.

Contact InterDyn BMI today!

Read why InterDyn BMI is holding off on using the Management Reporter Data Mart for Dynamics GP

By Lorna Link, GP Product Manager

Starting with RU3, Management Reporter 2012 includes a new “Data Mart” data provider for Dynamics GP in addition to the one included with earlier versions, which is now referred to as the “Legacy Provider.”

The Legacy method pulls numbers direct from Dynamics GP tables and calculates balances on the fly. This method can be slow especially reporting against multiple companies.

The Data Mart method stores financial information already summarized in its own SQL database. Reports generated using the Data Mart are pulling those summarized numbers instead of calculating them, improving performance for a large quantity of data.

This integration is constantly running. When data changes in Dynamics GP those changes are rolling down in the Data Mart database.

With a few exceptions, Data Mart is Microsoft’s preferred data provider for Dynamics GP, and Microsoft is planning to discontinue Legacy, but the Data Mart has issues that need to be fixed.

Reporting from ‘Legacy’ is one Management Reporter company, Reporting from the Data Mart requires another Management Reporter company

InterDyn BMI has observed issues with the Data Mart data provider, where Management Reporter reports are not returning the data or behaving in a way that a user is expecting. There are more problems with every roll up and they seem to be data mart related, for example:

• Errors during generation
• Missing data on financial reports
• There is a recognized bug with GP2013 with multi-currency is duplicating entries with the Data Mart and Microsoft has said this won’t be fixed until early 2014.
• There is also a bug with the header descriptions not working.
• Issues with analytical accounting reports missing financial data.

RU7 was recently released, but did not directly address all of these issue. We anticipate the RU8 will resolve many of these issue. RU8 should be release in early 2014. At that time we will reevaluate the use of Data Mart for Dynamics GP.

If a client ends up needing the Data Mart or we need to move them to the Data Mart once all issues have been resolved, the time to switch to Legacy is minimal in comparison to the potential problems that would be incurred in the short term with the Data Mart. We would estimate 1-2 hours to switch from Legacy to Data Mart, as estimated below:

1. We would need to remove Legacy, which takes less than a minute.
2. Install the Data Mart, which takes 15 – 20 minutes.
3. Point reports to the different company – up to an hour depending upon the quantity of reports.

Our plan until February 2014 is to install only Legacy for both new implementations and upgrades. When RU8 has been released, the issues are expected to be resolved we’ll resume using the Data Mart unless there is a specific business need to install the Data Mart. We’ll continue to be mindful of the installation recommendations when customers are using Analytical Accounting and Multi Currency.

Visit our website to learn more about Microsoft Dynamics GP and our full product line.

Contact InterDyn BMI today!

Management Reporter and Professional Services Tools Library Fiscal Year Change

Written by Lorna Link, GP Product Manager

An interesting find from Brian Bell, GP Implementation Consultant – A client just reset their GP fiscal year end from 3/31 to 12/31 and found that Management Reporter doesn’t follow the same rules as GP.

We used a 9 month stub year GP’s fiscal year label went from 1996 to 1995 for the oldest historical year.  Management Reporter CALCULATES its fiscal year label from the last date of each fiscal year, so it’s using 1996 still as the oldest fiscal year and not using GP’s fiscal year labels at this point.

You can modify the FiscalCalendarView by running the following script against the DDM database:

–//////////////////////////////////
–Script start
–//////////////////////////////////

SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER VIEW [dbo].[FiscalCalendarView] AS SELECT TOP 100000 RANK() OVER(ORDER BY PER.OrganizationId, FY.Id, PER.Starts, PER.Ends) as PeriodSequenceNumber,
RANK() OVER(PARTITION BY FY.id, PER.OrganizationId ORDER BY PER.Starts) as PeriodNumber, PER.Id, PER.Starts as PeriodStart, PER.Ends as PeriodEnd, FY.Starts as YearStart, FY.Ends as YearEnd, CAST((SELECT MIN(Years.Name) FROM FiscalYear Years WHERE Years.OrganizationId = FY.OrganizationId) + DENSE_RANK() OVER(PARTITION BY PER.OrganizationId ORDER BY FY.Starts) – 1 AS Int) As FiscalYear, IsHistorical, PER.IsClosed, PER.OrganizationId FROM Period PER INNER JOIN FiscalYear FY ON PER.FiscalYearId = FY.Id AND PER.OrganizationId = FY.OrganizationId ORDER BY PER.Starts GO

–//////////////////////////////////
–Script end
–//////////////////////////////////

Note that if you have to re-create the DDM database for any reason, you will need to re-run the script provided above.

Visit our website to learn more about Microsoft Dynamics GP and our full product line.

Contact InterDyn BMI today!