Cell-level Calculations in the Line Definition Formula field

Cell-level calculations in the Line Definition Formula field (or using the Calculation window for that field) are fundamentally constructed using the period (.) to delineate between column and line “Code” cell reference values. The calculation (or “placement”) is then constructed in the Line Definition Formula field referencing these cell coordinates.

Command Syntax:

[Column Definition Code]=[Column Definition Code].[Line Definition Code]

[Column Definition Code]=[Column Definition Code].[Line Definition Code] / [Column Definition Code].[Line Definition Code]

EXAMPLE:

B=[B].[1127]

This references one column’s row code value to “place” it in another cell (row) location.

-OR-

B=[B].[FIRST]/[B].[SECOND]

This uses two different row code values to return a calculated value in the column.

SAMPLE REPORT SCENARIO 1:

Based on the first syntax example above, the following details how to reference a row value to “place” it in another cell (row) location.

The Line Definition includes the following:

The Column Definition includes the following:

Your report without the placement calculation in the row Code = ‘1114’ would return the following:

Your report with the placement calculation in the row Code = ‘1114’ would return the following:

In the example above, only the resulting, specific data value of this reference will displayed on the row/column. (Also, the same value will be placed in row 1114 and 1127 for column B.)

This use of cell-level references is also referred to as the “Value Placement” technique. “Value Placement” allows you to pull a desired amount (value) from one row and column to display it on a different row and column, resulting in a scenario where the value overlays the original column and row value. For more information and an example, refer to the following MSX Message Board topic: How Do I Display a YTD value on a single row in a Line Definition

SAMPLE REPORT SCENARIO 2:

Based on the second syntax example above, the following details how to reference two cell locations in a calculation. Here, the calculation will return a value where a cell value is divided by the amount referenced in another cell:

B=[B].[1114]/[B].[1127]

The Line Definition includes the following:

The Column Definition includes the following:

The report without the calculation in the row Code = ‘1114’ would return the following:

The report with the calculation in the row Code = ‘1114’ would return the following: