Excel PivotTable Field List

From the drop-down list, select all Vision fields available for reports or a subset of all the Vision fields to display in the PivotTable Field List.

The field list displays the measures and dimensions included in the Vision OLAP data cube (external data) to which you connected after you opened Excel.

Vision provides the following data cubes with which you create Vision custom reports:

• Project OLAP cube

• General Ledger OLAP cube

Scroll through the list to find a Vision field to add to the report.

Vision fields are divided into measures (numeric values) and dimensions (categories for sorting and grouping).

In the Field list, you can identify the measures and dimension fields as follows:

• Measures have a Greek sigma symbol in front of them.

• Dimensions have a note pad icon in front of them.

Click the plus sign beside a measure or dimension to expand and display Vision fields. Measures and dimensions may also have additional folders, such as Filters and More Fields, which you can also expanded to select items within them.

Do any of the following in the Field list to add a measure or dimension field to the report:

• Select a field's check box.

• Right-click the field name in the list, and select the appropriate command from the shortcut menu to add it to the report as a column, row, value, or filter.

• From the field list, drag and drop a field into the appropriate report area (Column Labels, Row Labels, Values, and Report Filter) at the bottom of the PivotTable Field List.

When you select a Vision field from the list, it is automatically placed in a default location in the report layout area on the left side of the Excel worksheet as follows:

• Measures are placed as columns across the top of the report layout.

• Dimensions are usually placed on the left as rows in the report layout.

When you hover over a dimension in the list with your mouse, a down arrow appears to the right in the bar that highlights the dimension. Click the down arrow to open a popup screen that allows you to make specific selections for that dimension. For example, in the Expense Types dimension group, when you click the Expense Type check box and then click the down arrow, you can select which expense types you want to include on a report.

The fields that you select from the PivotTable Field List are also automatically placed in one of the default areas (Column Labels, Row Labels, Values, and Report Filter) at the bottom of the PivotTable Field List.

If you do not want a field located where Excel automatically places it, do either of the following to move it:

• Move and order fields

• Remove fields

This displays the filters that you applied to the entire report, not to specific dimensions or measures. Filters exclude data from a report.

Only dimensions can be selected as report filters.

To add report filters, from the list of fields in the PivotTable Field List, right-click a field name, and then on the shortcut menu, click Add to Report Filter.

The order of the report filters in the Report Filter area has no impact on the order of the data in the report.

By default, some measures that you select from the PivotTable Field List display in the Column Labels area. These fields also display as columns in the report layout area. For example, Transaction date measures in the Project OLAP cube display in the Column Labels area.

You can also click and drag an item from the Column Labels area to the Row Labels area to have it placed as a row instead.

The order of the fields in the Column Labels area is the order in which the fields display in the report layout. You can click and drag fields to modify their order.

You can use multistring dimensions as columns, but they are typically used as rows, with the exception of dates fields. A multistring dimension is a dimension that is comprised of several fields such as Employee by Company or Project by Organization.

By default, dimensions that you select from the PivotTable Field List are usually displayed in the Row Labels area. These fields also display as rows in the report layout area.

You can also click and drag an item from the Row Labels area to the Column Labels area to have it display as a column instead.

The order of the fields in the Row Labels area is the order in which the fields display in the report layout. You can click and drag fields to modify their order.

This displays numeric and calculated measures that you include in a report.

Dimensions cannot be values.

If more than one measure is added to a report, you see a Sum of Values label in the Column Labels area by default. This does not have to be the last row or column.

Earnings are measures that are calculated. If you included them in the Values area, they may require some formatting (for example, you add a thousands separator, add a currency symbol, and so on). To format them, right-click a report cell that contains a value, and from the shortcut menu, click Number Format.

The order of the measures in the Values area determines the order of the data in the report layout. You can click and drag fields to modify their order.

As you create a report and make modifications in the PivotTable Field List, the Excel worksheet is automatically refreshed so that you immediately see the modified data in the report layout area. You can see the modification instantly, but if you use a large amount of report data, the refresh process may take longer than an instant.

To turn off the automatic refresh so you can control when the refresh process occurs, select the Defer Layout Update check box; then when you want to refresh the data, click the Update button to the right of the Defer Layout Update check box.