How to Do Cross Column Formulas in Management Reporter

Recently, there have been a number of forum posts related to cross-column equations (formulas) in Management Reporter. Users typically want to perform a mathematical operation like division on a value from one column and/or row to another column and/or row.

Users may even desire to perform numerous calculations in the same row. The syntax requirements for this seem to be confusing folks, so I wanted to provide a concrete tested example of this type of formula.

Here is an introduction to the core concepts in Management Reporter:

Use CAL for Calculations, instead of TOT for Totals. Performing complex math in a Row Definition requires the use of a CAL Format Code in Column C of the Row Definition.

Use the @ symbol to call out a specific row in a calculation. In order to include a reference to a specific row in a CAL formula, users must use the @ symbol. The format for usage is @row (i.e. @200).

Use a period separator to restrict output of a calculation to a particular column. In order to restrict the formula to a particular column, users must use a period separator, in much the same way periods are used in windows for file extension (i.e. .doc, .xls, .sql, etc.)  The format is column.row (i.e. C.200).

Use the = operator to define different formulas for specific columns. In order to define different formulas for different columns, users must use the = operator, in much the same way it is used in Excel. The format is column=formula (i.e. B=@200/C.200,C=@200/C.200)

Note: In this case, I have used an unnecessarily complicated solution to illustrate the flexibility of Management reporter. Simply typing @200/C.200, would produce identical results in this instance, as does typing B,C=@200/C.200…

This syntax can be very helpful when dealing with departmental break outs and budget versus actual columns.

Here is an illustration of these concepts in Management Reporter:

The output from this example is as follows:

How to Create and Run a SIMPLE Macro in Dynamics GP

For those new to Dynamics GP, macros require an introduction. There is a tool in Great Plains which allows a user to record their keystrokes and mouse clicks...  Tools > Macro > Record 

What you can do with this tool ranges from the simple to the complex.  

I will start with a high level overview of the macro process.  And like many of my blogs, it begins with a story.   Many moons ago, when I was a fledgling consultant, I was at a Great Plains event. Long before Great Plains was acquired by Microsoft.  A couple of gentlemen named Martin Hildebrand and Sean Mahoney from the Taylor Group, one of the preeminent Great Plains partners at the time, were having a discussion about the use of Macros...  

Apparently, you could record the act of adding an inventory item into Great Plains, then cut and paste the recorded Macro text into Microsoft Word.  In Word you could use a source file of inventory items in Excel (or Text) format in concert with the Mail Merge feature, typically used to automate form letter writing to replicate this simple macro code and combine it with the data from your list.  

The mail merge would duplicate the Macro code for each of the Inventory Items in the source file. Simply, paste the merged documents back into the Macro and run it in Great Plains.  Presto! The unbelievable result was the import of all the items in the source file.

That was it, using the knowledge acquired from this casual conversation, I dumped inventory items into an Excel file from Macola -- yes, Macola - fledgling GP consultants have to come from somewhere. 

Using these inventory records, a Macro and the Mail Merge process in Word, I created my first Macro, and before I boarded my plane, I had uploaded all of my inventory items into a test Great Plains database (which I am pretty sure was running on Btrieve). 

I never looked back – Macros could do things nothing else could.  Have a third party product, Macro.  Need to import data into custom fields, Macro.  Want to update records inserted using integration manager, Macro.  Want to delete a bunch or records from GP, Macro.  Want to update your counts when doing Physical Inventory – you guessed it, Macro!  I went absolutely bonkers for Macros.

This blog-post is meant to provide a simple example of Macro use.  In this instance we used macros to delete a number of customer records for a customer using WennSoft Equipment Management.  Let's call this operation, customer cleanup - subtitled, have the client remove undesirable records before you import them.

The first step in Macro recording is not, as you might suspect, to record entering a transaction.  It is to prepare to record a transaction.  You must ensure you touch every field you will require, in order to be able to map all the data necessary into the system - deleting records is no exception.  

Consequently, you need your import file - this will provide you a complete list of fields you need to find homes in GP for.  Once you have the list of fields, do a dry run on the window, to figure out if any dialogue boxes will pop up - any activity on the screen will be captured by the macro, superfluous activity is undesirable.  Once you've completed your dry run and know you can enter a transaction without errors/dialogues interrupting, you can record your Macro file.

You need to open the window, ensure it is in focus and the cursor is in the starting field and starting field is empty. Type the field value and move directly, via mouse, to the next required field - tabbing through fields will touch fields you don't need in your macro, which increases the length and complexity of your macro code unnecessarily. Worse yet, clicking on a look up button, rather than typing, will add a mess of useless lines to your Macro - trust me, don't do it.

Once you have completed your transaction click on the OK or Save button, in most cases your cursor will return to the starting field on the form.   If not, click clear, or something similar to get it there - it needs to be there, because macros require a loop to work properly.  By loop I mean, in order for Macros to work, they must cleanly move through the form, inserting data, save this data, and return to the first field on the form to begin processing the next record.

Now you have some Macro code - it should look something like this.

# DEXVERSION=11.00.0359.000 2 2

CommandExec dictionary 'default'  form 'Command_Sales' command 'RM_Customer_Maintenance'

NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'

  TypeTo field 'Customer Number' , 'SAMPLE'

  MoveTo field 'Delete Button'

  ClickHit field 'Delete Button'

# Are you sure you want to delete this customer record?

NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog

  ClickHit field OK 

It will be much longer than this, if you're doing something substantial, but this is an example.  What is important to note, is, as I said, Macros run in loops, so DO NOT cut and paste the entire macro into Word.

What you want is the start and end of your transaction:

NewActiveWin dictionary 'WennSoft Products'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'

  TypeTo field 'Customer Number' , '«CustomerID»'

  MoveTo field 'Delete Button'

  ClickHit field 'Delete Button'

# Are you sure you want to delete this customer record?

NewActiveWin dictionary 'WennSoft Products'  form DiaLog window DiaLog

  ClickHit field OK 

Now that you have your transaction loop pasted into Microsoft Word, go to the Mailings tab in Word Navigation and Start Mail Merge > Letters. Then Select Recipients > Use an Existing List. Then browse to the Source file and select it.  Now you should have list of Merge Fields to choose from.  Select the area inside the single quotes around the customer ID, 'SAMPLE' you entered and select Insert Merge Field, choosing the <<CustomerID>> merge field - it should now look roughly like the image below.

Now choose the Finish & Merge button from your Word Mailings navigation.   This will create a new Word file with one transaction for each line in your source file.  Select all the records by clicking on Ctrl+A, copy them all by selection Ctrl+C and paste them back into your Macro File using Ctrl+V (Ctrl+P prints things), perform a 'save as' function, so you have your original Macro file as a template should you need it (this does not always go right on the first attempt).

Open Dynamics GP, navigate to the window you want to import your data into (this is critical path).  From the now open window choose Tools > Macro > Play and browse to your the macro file which contains all the records you wish to import.  Great Plains will begin to rapidly enter the information on your behalf, or in this case, delete and confirm the deletion of the customers in your list.

Finer Points:

When editing Macros you might want to use a product like Notepad++.

When troubleshooting macros, which will tell you what line they encounter a problem on, and not much else, you definitely want to be using Notepad++ (Notepad++ does not pay me for endorsements). 

https://notepad-plus-plus.org/

Notepad++ shows the line numbers in a text file, and when a macro stops, the dialogue box will note what line it stopped on. If your macro is thousands of lines long, you'll appreciate not having to manually count the line numbers.

You may also notice, when the macro stops, it stops on the record it was last working on, so if you don't have Notepad++ you can search your macro file for the master record your macro stopped processing on.

When saving a macro file, make sure you do not save it with txt extension, if you do, change it back to the .mac extension prior to running your macro. 

Apple keeps associating .mac files with Apple's QuickTime application - it cannot successfully open them - believe me, I have tried. You do not have to change the file association, as you will be browsing to your macro file from within Dynamics GP, and it will see them, even if the association has been made by Apple.

When editing macros, right click on Macros and select Edit or Open With > Select program, choose Notepad++.

Helpful Hint... When entering a significant number of records, it is not uncommon for something to interrupt the normal processing of a macro (they can be temperamental). If this happens, keep calm. It is possible to find the place your macro stopped, remove the preceding data/lines from the macro, which have already been imported and restart the macro, once you've fixed the problem. 

It is not uncommon to do this multiple times during the testing process. This can be invaluable, as you can note what caused the problems, and fix the problems in the live environment prior to running the macro there, where it should run uninterrupted.

One potential cause of problems is touching the mouse, which can take the focus off the window you're importing data into. When this happens, you'll get an error that roughly states the window is not active/or field does not exist in active window. These problems are best avoided, by not touching the mouse.

NOTE: A word about single quotes (').  For Macros, this character is CRITICAL - it defines the boundaries for the data entered by the macro (i.e. 'DATA').  From time to time, you will experience difficulties with imports, because data contains single quotes (apostrophes). Customer and Vendor Descriptions like O'Doule's, O'Malley & O'Reilly will reek havoc on a macro.  If you experience this type of error, you can perform data transformation, by using scripts to remove the undesirable character, or request the customer clean up the data prior to import.

Be not discouraged... I have been doing this for a very long time, I have had people, in-a-fit, going out of their mind, when a Macro isn't doing what they want, call me exasperated. I can typically look at the macro and tell them what the problem is.  Your initial attempts might fail, but keep after it.  I plan to write a few more blog posts on this subject covering intermediate and advanced applications for Macros.

Finally, A WARNING! DO NOT comment and say you deleted the entire customer list in your Live company with no backup while testing this example... There are so many things wrong with that sentence, I can't begin to enumerate them.  I will indeed laugh at you... No, I won't laugh, I may even cry and commiserate with you. Please leave yourself some options by having a backup handy.

If you're going to run macros, test them. When running them, make a backup first. Macros can update a lot of data, very rapidly. Watch your macro run, if it looks like it's doing something you don't like, you can stop it fairly easily. Almost any movement or click of the mouse will interrupt your process (conversely, when things are going right, leave the mouse alone).