Dynamics Community Forum Thread Details

Management Reporter (MR) - Is it possible to have multiple formulas on the same column?

Hi all.

I'm currently building a report to have each line of business numbers with after reallocating overhead costs.

Since we want to keep the transactions in Admin department as it is on GP, thought building a MR report would be the best way to do so.

Everything seems to be working so far, except for the fact that I cannot put multiple formulas on the same column - I can use one percentage (ex. 30%) but is there a way to use multiple percentages as below?

I was hoping I could have the actual numbers on one column and another column for percentages so I can easily change the percentage every month. The last column would give the multiplication of the two.

Your help would be greatly appreciated!!

Categories:

Microsoft Dynamics GP

That's not really a way to do that. The column will only allow you to have one formula; for example B* C or B*.30. What might work is to link to Excel and then add the percentage that way. You'd add the percentages to the Excel file and then Column C would be formatted as a WKS column. The numbers from Excel would appear in that column. You could then add the formula to Column D -- B*C.

Below is an example of how to add an external link to Excel, in MR.

Row format with a link to account 1100 and link to an External Worksheet. The link to the worksheet is referencing cell A2. This could be formatted with the /CPO switch as well. Adding the /CPO to the cell reference would move the reference to the right, based on the period. For example, if the report was run for Period 4, the reference would move 4 columns to the right and I would get data from E2.

To add the Link to External Worksheet (Column K), click Edit | Row Links. You can also double-click in the header of Column J. It will then open the window below. Click New and then select External Worksheet for the Link Type. The Link name will default with then name WKS1. You can change if you choose. This name is what will appear in the Worksheet Link column in the tree.

Column definition with an FD and WKS column. The amounts from the GL accounts will be placed in the FD column, while the amounts from the External Worksheet link will be placed in the WKS column.

Because the report uses an External Worksheet link, a tree must be used. The tree is used to tell MR where the Excel file is located. This tree has an example of two different ways to link to the file. You would not need both rows 2 and 3. Row 2 will pull both GL and Excel data and put them in one unit. Row 3 will pull Excel data only.

In order to link to the Excel file, you must select the row that has the Link to External Worksheet. Once you select that, you can select FD1 (the GL data) and select WKS1 (the Excel data). Then in Column H, select the path to the Excel file. If you needed to reference a specific worksheet from the Excel file, you would enter the worksheet name in Column I.

In the report definition, you must check the option Use row definition from reporting tree. If this is not selected, MR will not use the links in the tree.