ReportMagic

[Xlsx.AddAnalysis:]NEW

Adds an Analysis tab to the XLSX output document.

Purpose

Adds an Analysis tab to the XLSX output document, based on an Excel table in an existing worksheet created by your report.

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. Does not provide the ability to specify an Excel file as input - in other words, the source worksheet MUST have already been created by other macros in your document. Typically, this macro would be used to produce multiple variants (e.g. using different pivot table fields) of pivot chart and table analyses, as a result of data produced by [xxx.Analysis:] macros.

Parameter	Type	Deprecation Message	Preferred Parameter	Presence	Purpose	Options	Default
inputWorksheetName	String			Mandatory	The worksheet name that contains the Excel Table that holds the source of data. The first table found in the worksheet is used (and it must be a 'proper' Excel table, not just columns and data). Note: the [xxx.Analysis:] macros all append ' Data' onto the name of the worksheet which holds the fact table, so if you are using this macro in combination with one of those, ensure you add that onto the name of the worksheet specified here.	N/A	N/A
addChart	Boolean			Optional	Whether to add a chart to the Analytics worksheet.	true false	true
addTitle	Boolean			Optional	Whether to add a title to the Analytics worksheet.	true false	true
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 can be any of the header names in the source data worksheet.	N/A	N/A
columnGrandTotals	Boolean			Optional	Whether to add column grand totals to the pivot table.	true false	true
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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	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 can be any of the header names in the source data worksheet.	N/A	N/A
worksheetName	String			Optional	The name to use for the analytics worksheet (which will contain the pivot table and chart). 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	Analytics

Examples (1)

Example 1:

This example first uses the [LogicMonitor.ResourceGroupAnalysis:] macro to produce the data (and analysis) worksheets, and then uses a [Xlsx.Analysis:] macro to produce a variant of the original analysis but with different pivot table-related parameters. Note that because the first macro by design appends the text ' Data' onto the name of the data worksheet, this is used by the subsequent macro in the 'inputWorksheetName' parameter. The possible values used in the pivot table parameters are exactly the same as those used by the [LogicMonitor.ResourceGroupAnalysis:] macro and correspond to the headings in that source table. Note that the [Xlsx.AddAnalysis:] macro specifies the optional 'Sum' aggregation and the Excel number format (here, of 3 decimal places) in the 'pivotTableValueFields' parameter: