We’ve seen quite a bit of discussion lately around linking to Microsoft Excel files in Management Reporter. This is a great way to get additional data into your reports that’s outside of your Microsoft Dynamics ERP’s general ledger.
A couple of quick tips to successfully pull in data from Microsoft Excel workbooks into Management Reporter reports:
1. Make sure the Management Reporter process service user has access to the folder where the workbook is stored. (This is noted in the installation guide as well.) This doesn’t mean that the file has to be on the Management Reporter server, just that the process service user has to be able to get to it.
2. If you are using a single workbook with multiple worksheets, make sure you enter the path in the Workbook Path column and the worksheet (tab) name in the Worksheet Name column.
To refresh your memory on how to set everything up, take a look at this post on creating consolidations using non-Dynamics data.
In Row Definitions, what is the syntax for linking the correct tab in a workbook with multiple tabs? In FRx, it was [worsheetname.xlsx]tabname
There is a field for the tab name. See column I in Jill's picture above
My question still stands. The picture in Jill's post above is form Reporting Tree Definitions, not from Rows. I am using a Row but no Tree. Row Definitions do not have a field for the tab name. How do you link to a tab from within Row Definitions? When I use brackets as in FRx, I get the error message "The worksheet file must have an .xls or .xlsx extension." What is the proper sytax?
When you choose Financial Dimensions + Worksheet in the row definition, you will see three fields.
1. External workbook path - the path to the workbook
2. Management Reporter report - the location of the report in the Management Reporter report library
3. Worksheet name - the tab or worksheet name in the workbook
Hope that helps!