A Management Reporter Battle: Row and Column Formulas

Tom Schroder / October 17, 2016
 A Management Reporter Battle: Row and Column Formulas

I had a client with an MR report that included a Row that was defined as a conditional calculation similar to this:

IF @200 < 0 THEN 0 ELSE @200*.33

The column definition had Unit restricted columns that were set to NP (no print), and then a calculated column that totaled those NP columns.  Then there is a Tree for the various units, rolling up into a Summary.  The Unit Name for the Summary line was SUMMARY OF ALL UNITS, and the Unit Description was SUMMARY.

The calculation for the Row worked fine for the individual units.  We would get 33% of line 200 or 0 if line 200 was negative.  The problem came on the Summary report, where line 200 would display the total of the NP columns (as desired) but the calculated row would show the total of the calculations from the units.  What the client wanted was to see 33% of the total for line 200.  

Changing the calculation method to column and then row caused more issues in other places.  My solution, which like most solutions sounds very simple now that we know it, was to create two NP rows defined as:

300        IF @200 < 0 THEN 0 ELSE @200*.33

310        @200*.33

The “printing row” is defined as:

400        IF @UNIT (SUMMARY OF ALL ROWS) THEN @300 ELSE @310

Well, that was almost the solution.  Turned out MR really didn’t like that unit name!  I would get an error on the row and it wouldn’t save.  I tried single quotes, double quotes, square brackets, anything I could think of, but I didn’t find anything that worked.  So I gave in and changed the Unit Name to SUMMARY.  This worked splendidly, after changing my column definition (because the unit restrictions there were all looking for SUMMARY OF ALL UNITS)!

Tom Schroder
About the Author

Tom Schroder, MBA, is the lead Consultant responsible for the HBS Dynamics GP Help Desk. With 15+ years of experience supporting Dynamics GP, Tom excels at troubleshoot, report writing, and more. 

Blog post currently doesn't have any comments.