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:

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

ReplyDeleteYou could use a statistical account to have a constant appear in an MR report.

DeleteI 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%.

ReplyDeleteYou 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.

DeleteI tried this with B=@480,E=@D.480

ReplyDeleteHowever it won't save, saying Unexpected character(s) were found at position 12 in the expression: ".480".

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.

DeleteThank you so much!!! This was extremely helpful!

ReplyDelete