Using the <Macro> Tag

Use the Macro tag to generate formulas that will run in Excel. Only the subset of formulas supported by Apache POI is supported (see http://poi.apache.org/spreadsheet/formula.html).

Example:

<Macro Heading="bob">${fullyBurdenedCost}*${countInParent}</Macro>

This will generate a column called "bob" that for each generated row contains the Excel formula "=$R1$C1 + $R1Rc2", where:

• R1 is the value of the current row

• C1 is the column name where fullyBurdened cost landed

• C2 is the column where countInParent landed.

This assumes that you have specified that the report generator should generate these two columns within the same <CSL> tag. Note that the name that you use for a column should be the name of the Heading attribute if there is one.

Other available syntax:

• ${^<colHeading>} refers to the <colHeading> cell in the previous row (will map to 0 if this is the first row)

• ${.<colHeading>} refers to the <colHeading> cell in the first row

• ${@<colHeading>} refers to the column (the entire column)

In addition, the report generator creates a named range that maps to the data values (it excludes the header row) for each column with the column header name. You can just refer to that name in your formula macro.

Note: If a non-existent column is referenced in a macro formula, the value "0" is substituted. A non-existent column can occur if you have a Macro spec that references columns for which the current "Node/CSL" elements have as yet generated no rows. For example, assume that your Node spec is looking for all leaf nodes, only some of which have the custom output "foo". If you use "foo" in a macro formula, there will be no column for "foo" in the output spreadsheet until we actually hit a node that causes us to create one.