Vertically merge cells (downwards) that have the same data.
Compatibility
The macro can be used in the highlighted input document types only. A greyed-out icon indicates not supported.
Usage
For a cell to merge down, all previously indexed columns should be identical vertically, not just the immediately previous one. This can be overridden using the 'ignorePreviousColumns' parameter, to achieve a vertical column merge (as long as all the cell values in the column selected are identical). Only the top-most cell style is retained. The macro assumes a header row which it ignores - however you may set ignoreHeaderRow to false to override. Note: the columns being merged, and (unless ignorePreviousColumns is set) the columns to their left, must not already contain merged cells in the rows being merged, or the output of this macro would be unpredictable; the macro reports an error rather than producing it. Merged cells elsewhere in the table, including a merged header row, are fine.
Updates
In Report Studio and HTML, a table that contains a nested table is now handled correctly: the nested table's rows and cells are no longer counted as belonging to the outer table, so row and column numbers match the table the author can see and a nested table is never changed by a macro aimed at the table around it.
History (2)
The macro now refuses to run, and explains why, when a column it has been asked to merge already contains merged cells in the rows being merged, instead of attempting a second merge with unpredictable results. The message names the row and column it objected to. Merged cells elsewhere in the table, including a merged header row, do not prevent the merge.
Added the 'ignorePreviousColumns' parameter to allow vertical column merges without checking all previous columns.
Behaviour (10)
| Parameter | Type | Presence | Purpose | Options | Default |
| List<ExecutionResult> | Optional | If specified, asserts the expected execution result of the macro. Accepts a single value or a semicolon-separated list of acceptable values (e.g. 'Success;Warning'). The macro executes normally; if the actual result matches any of the desired values, the result is converted to Success. If the actual result does not match, the result is converted to MacroError with a descriptive message. This is primarily used for testing and diagnostic purposes. Valid values are: 'Unknown', 'Success', 'MacroError', 'WorkerStopped', 'Running', 'Warning', 'NeverRun', 'Cancelled', 'Pending', 'Paused', 'SystemError', 'Deferred' and 'Stopped'. |
|
N/A | |
| Boolean | Optional | Should NCalc expression evaluation throw error on Overflow |
|
true | |
| String | Optional | If specified, asserts the expected output type of the macro result. The macro executes normally; if the actual type does not match, a macro error is generated. Requires 'storeAs', 'storeAsHidden', or 'storeFormattedValueAs' to be set for typed validation. Valid types include CLR names (e.g. Int32, Int64, Single, Double, Boolean, String, JArray, JObject) and C# keyword aliases (e.g. int, long, float, double, bool, string, uint, ulong, short, ushort, byte, sbyte, decimal, char, object). The special value 'Number' matches any numeric type. | N/A | N/A | |
| String | Optional | If specified, asserts the expected output value of the macro result. The macro executes normally; if the actual value does not match, a macro error is generated. When 'storeAs' or 'storeAsHidden' is set, the stored variable value is compared. Otherwise, the document output text is compared. | N/A | N/A | |
| String | Optional | The condition that must be true in order for the macro to be executed/evaluated. Must either evaluate to true or false, for example: "3+5=8" or "contains('abcd', 'z')". | N/A | true | |
| Boolean | Optional | Whether to skip the first row of the table, treating it as a header. Cells that are already merged in the template, such as the lower half of a two-row header, are left unchanged whatever this is set to. |
|
true | |
| Boolean | Optional | Whether to merge each listed column on its own values alone. By default a cell only merges down when the columns to its left match over the same rows, so that a merge never joins rows that differ elsewhere in the table. |
|
false | |
| MacroMode | Optional | The mode in which variables are stored. In legacy mode (default for Schedules), the variable created is a string and formatted. In normal mode (default for Report Studio), the output variable is stored as a strongly-typed object, e.g. an Int32 or a JArray etc., rather than a formatted string. |
|
Legacy | |
| ObfuscationType | Optional | Obfuscation type. Use obfuscation to write reports where sensitive data is hidden. When used, ReportMagic guarantees that the same input string will map to the same output string for the whole of the report (but the next time the report runs, it will most likely map to a different value). If you use obfuscation, the property in your macro will not show up and instead, you will see a fake item of the obfuscation type chosen. |
|
None | |
| String | Optional | If specified, adds a warning message for this macro. This is processed as an NCalc, and the warning message will ALWAYS be present and will be the value of the evaluated NCalc expression. | N/A | N/A |
Output (2)
| Parameter | Type | Presence | Purpose | Options | Default |
| String | Optional | The text to display should the macro fail to execute. Note that a poorly-specified macro (e.g. omitting mandatory parameters) will still result in an error message. | N/A | N/A | |
| String | Optional | The name of a variable to create should the macro fail to execute. The variable will be a text variable, and will contain either the failure text (only if the failureText parameter is set), otherwise it will contain the exception / failure message. | N/A | N/A |
General (2)
| Parameter | Type | Presence | Purpose | Options | Default |
| List<Int32> | Mandatory | The column indices, separated by a semi-colon, to consider for merge. Column 1 is the leftmost column. You can specify them in any order. | N/A | N/A | |
| String | Optional | Add a comment to make your document template more readable. The comment is discarded in the output document. | N/A | N/A |
Examples (3)
Example 1
This example vertically merges cells in columns 1, 2 and 3 that have the same cell value:
[List.Table: values=a^b^c^d^e^f;a^b^c^d^e^f;a^b^c^d^e^f;a^b^c^d^e^f]
[Table.MergeCells: columnIndices=1;2;3]Example 2
This example vertically merges cells in column 2 that has the same cell value ('z') in every cell:
[List.Table: values=a^z^e;b^z^f;c^z^g;d^z^h]
[Table.MergeCells: columnIndices=2]Example 3
This example vertically merges cells in columns 3, 4 and 5 that have the same cell values. So Column 3 merges the first 2 cells that contain the letter 'c', then the next 2 cells that also contain the letter 'c'. Column 4 is merged into one column containing just 'd'. Column 5 merges the first three cells of 'e', and the last two cells of 'e':
[List.Table: values=Column 1^Column 2^Column 3^Column 4^Column 5^Column 6;x^x^c^d^e^f;y^y^c^d^e^f;z^z^qq^d^e^f;x^x^c^d^qq^f;y^y^c^d^e^f;z^z^qq^d^e^f]
[Table.MergeCells: columnIndices=3;4;5, ignorePreviousColumns=true]
