ReportMagic

[List.Analysis:]

Adds a List Analysis tab to the XLSX output document.

Macro Compatibility

The macro can be used in the highlighted input document types only. A greyed-out icon indicates not supported.

Usage

Does not work in Report Studio.

Parameter	Type	Deprecation Message	Preferred Parameter	Presence	Purpose	Options	Default
values	List<String>			Mandatory	The list of input values, delimited by the delimiter character. Each item should be carat ('^') delimited. There should be a header row e.g. A^B^C;1^2^3;4^5^6	N/A	N/A
addChart	Boolean			Optional	Whether to add a chart to the Analytics worksheet.	true false	false
addTitle	Boolean			Optional	Whether to add a title to the Analytics worksheet.	true false	false
columnFields	List<String>	Deprecated	pivotTableColumnFields		The pivot table column fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Columns' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
columnGrandTotals	Boolean			Optional	Whether to add column grand totals to the pivot table.	true false	false
filterFields	List<String>	Deprecated	pivotTableFilterFields		The pivot table filter fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Filters' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
pivotTableColumnFields	List<String>	Use instead of: columnFields		Optional	The pivot table column fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Columns' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
pivotTableFilterFields	List<String>	Use instead of: filterFields		Optional	The pivot table filter fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Filters' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
pivotTableRowFields	List<String>	Use instead of: rowFields		Optional	The pivot table row fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Rows' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
pivotTableValueFields	List<String>	Use instead of: valueFields		Optional	The pivot table value fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Values' section. For each column name, you can specify the aggregation to use via the ^ character e.g. column1^Sum. Omit this to use the default Count aggregation. Valid aggregation values are: 'Average', 'Count', 'CountNumbers', 'Max', 'Min', 'Product', 'StdDev', 'StdDevP', 'Sum', 'Var' or 'VarP'. For each column name, if and only if you have specified an aggregation, you can also specify the number format to use in the pivot table and chart. To do this, use an additional caret separator and specify the number format. For example: column1^Sum^0.00 would use 2 decimal places for the format. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be B;C.	N/A	N/A
rowFields	List<String>	Deprecated	pivotTableRowFields		The pivot table row fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Rows' section. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be A;B.	N/A	N/A
rowGrandTotals	Boolean			Optional	Whether to add row grand totals to the pivot table.	true false	false
valueFields	List<String>	Deprecated	pivotTableValueFields		The pivot table value fields. In Excel's PivotTable Fields UI, these correspond to the items in the 'Values' section. For each column name, you can specify the aggregation to use via the ^ character e.g. column1^Sum. Omit this to use the default Count aggregation. Valid aggregation values are: 'Average', 'Count', 'CountNumbers', 'Max', 'Min', 'Product', 'StdDev', 'StdDevP', 'Sum', 'Var' or 'VarP'. For each column name, if and only if you have specified an aggregation, you can also specify the number format to use in the pivot table and chart. To do this, use an additional caret separator and specify the number format. For example: column1^Sum^0.00 would use 2 decimal places for the format. These should be from the header row e.g. from A^B^C;1^2^3;4^5^6, this may be B;C.	N/A	N/A
worksheetName	String			Optional	The name to use for the analytics worksheet (which will contain the pivot table and chart). The data worksheet (which will contain the 'fact table') uses this name plus the suffix ' Data'. If a worksheet by this name already exists, the new name will have a number appended, e.g. Analytics1, Analytics2, etc. Excel does not allow blank names, names above 31 characters, and the following characters: :, /, \, ?, *, [, ].	N/A	List Analytics

Examples (2)

Example 1:

[List.Analysis: worksheetName=worksheet1, values=A^B^C;1^2^3;4^5^6]

Example 2:

This example customises configures the pivot table with various items: