Sunday, October 19, 2014

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:



7 comments:

  1. I'm merely trying to get a constant to appear in one cell of a MR report.

    ReplyDelete
    Replies
    1. You could use a statistical account to have a constant appear in an MR report.

      Delete
  2. I am trying to get a contribution margin but on a monthly basis and YTD. I.e. based on your row definition.... row 200 divided by row 80. But I keep getting a flat percentage value across my report. i tried @1450/@400 (My rows) but im getting 400%.

    ReplyDelete
    Replies
    1. You may be getting 1450 / 400 (or 3.625), which is being rounded up. You are missing indicators in your formula's for which columns in the report are involved in the formula.

      Delete
  3. I tried this with B=@480,E=@D.480
    However it won't save, saying Unexpected character(s) were found at position 12 in the expression: ".480".

    ReplyDelete
    Replies
    1. Position 12 is the period in D.480, which is where the unexpected character was encountered. Try removing the @ from in front of @D.480. You will note in my example above, periods are not used in conjunctions with @ symbols.

      Delete
  4. Thank you so much!!! This was extremely helpful!

    ReplyDelete