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!