Exporting to Pivot Tables

When performing a table export to an XLSX file, you can create a pivot table using the Excel Advanced Features.

Pivot Tables can only be created for XLSX files; they cannot be created for XLS files. If you are using a version of Excel earlier than 2007, you can download the Microsoft Office Compatibility Pack from Microsoft, which will allow Excel 2000 and higher to open XLSX files. The option to create a Pivot Table is not available for a Summary export, since pivot Tables use tabular “raw” data as a source, not summarized data. Monarch Classic will export the table data as a worksheet and the Pivot Table as a separate worksheet, which refers to the exported data.

Exporting to pivot tables

Select Create Export on the Export View Ribbon. The Create Export dialog displays.
Define the name of the export.
Select a Table on the Source View selector.
Select Microsoft XSLX or Microsoft XLSM as the Export File Type.
Select Edit Advanced Options (at the top of the dialog). The Export Design window displays.
On the Table View panel:
- Enter the Export Name.
- Select the Filter and Sort definitions that will be applied to the export.
On the Excel Output panel, click on the Advanced tab to display the Advanced options.
Check Include Pivot Table sheet.
Add the desired pivot fields and pivot values for the Pivot Table from the Monarch Classic table fields. These are divided into different types: Report Filter (pivot field), Row Labels (pivot field), Column Labels (pivot field), and Values (pivot value).

Note: Anyone familiar with Pivot Tables will recognize these elements. For those less familiar, here are some analogies in terms of Monarch Classic Summaries:

Pivot Table

Summary Equivalent

Report filter

Summary filter

Row labels

Down key in a summary

Column labels

Across key in a summary

Values

Measure in a summary

To add a pivot field or value, simply select it from the New.. drop-down list.

To further summarize any of the items you place in Row Labels or Values, right-click on an item and, from the options that display, select Edit. This action will launch the Pivot Field dialog for Row Labels and Pivot Values Dialog for Values.

Use the sorting button that displays beside each pivot field or value to specify a display order (i.e., ascending or descending).
Once you have selected the desired pivot fields and values, specify the desired settings using the Pivot Options tab. For example, you can define a new pivot table name. Note that pivot table names must follow the table naming guidelines specified in Naming Conventions.
Select Accept.
To run your newly-created export, refer to Running Exports.

Pivot Options tab

Field	Description
Name	By default, the Pivot Table is named “PivotTable”. You can assign a new name to it by entering it here..
Weight	This refers to the Pivot Table style weight that will be applied when the Pivot Table is rendered in Excel. There are 3 options: Light, Medium and Dark. The weight is used in conjunction with the style index.
Index	This is the number of the table format style that will be applied when the Pivot Table is rendered in Excel. Excel has 28 styles available in 3 weights. This style can be viewed in Excel by selecting the “Format as Table” menu item when viewing a Pivot Table.
Apply to row headers	Applies the style settings to the row headers of the Pivot Table.
Apply to column headers	Applies the style settings to the column headers of the Pivot Table.
Banded rows	Applies alternate color shading to the rows in the Pivot Table.
Banded columns	Applies alternate color shading to the columns in the Pivot Table.
Show grand totals for rows	Displays grand totals for each row of the Pivot Table.
Show grand totals for columns	Displays grand totals for each column of the Pivot Table.

Intellectual Property Rights Notice | Technical Support

Pivot Table	Summary Equivalent
Report filter	Summary filter
Row labels	Down key in a summary
Column labels	Across key in a summary
Values	Measure in a summary