Pivot tables (SM208010)

The window toolbar

Button	Description
View pivot	Builds the pivot table designed or selected in the window. This button becomes available after you save changes in the pivot table parameters.
Edit inquiry	Opens the Generic inquiry (SM208000) window for the generic inquiry that is selected in the Window ID field. This button becomes available after you select a generic inquiry in the Window ID field.

The top part

In this area, you can create a new pivot table or select an existing pivot table to view and edit its parameters.


Element	Description
Screen ID	Required. The unique ID of the generic inquiry that is used for building the pivot table.
Sitemap location	The location of the new pivot table in the Visma Net site map, where it is available for users to open. You should specify the site map location in this field if you want to display the created pivot table in the site map; otherwise, leave this field blank.
Pivot table ID	Required. The unique ID of the pivot table. For a new pivot table, leave this field blank; once the pivot table is saved, the system will insert the value of the Name field in this field as an ID. For an existing pivot table, you can select a value in this field only after the screen ID has been selected.
Shared filter to apply	The name of a shared filter that is used with the pivot table. The default value of this field is All records.
Name	The name of the pivot table. For a new pivot table, enter a name that describes the type of data that is shown with this pivot table.
Is shared	A check box that you select to share the pivot table between all users of your Visma Net instance. By default, this check box is cleared.

The Fields pane

This pane contains the list of fields from the selected generic inquiry that are not used in the pivot table settings.
Initially, it contains the full list of fields from the selected generic inquiry.
You can move fields from this pane to other panes of the window in the following ways:

Select fields in this pane, and then click on the pane where you want to move the field.
Drag and drop fields to other panes in the window.

The pane toolbar includes only standard buttons.

The Filters pane

This pane contains a list of fields from the selected generic inquiry that are used as filters in the pivot table.
You can filter pivot table data by multiple fields.

You can add fields to this pane and remove fields from the pane by dragging and dropping them or by using the toolbar buttons.

The pane toolbar includes only standard buttons.

The Columns pane

This pane contains a list of fields from the selected generic inquiry that are displayed as columns in the pivot table.
You can use multiple fields in the columns of the pivot table.

You can add fields to this pane and remove fields from the pane by dragging and dropping them or by using the toolbar buttons.

The pane toolbar includes only standard buttons.

The Rows pane

This pane contains a list of fields from the selected generic inquiry that are displayed as rows in the pivot table.
You can use multiple fields in the rows of the pivot table.

You can add fields to this pane and remove fields from the pane by dragging and dropping them or by using the toolbar buttons.

The pane toolbar includes only standard buttons .

The Values pane

This pane contains a list of fields from the selected generic inquiry whose values are displayed in cells of the pivot table.
You can add multiple fields in this pane.

You can add fields to this pane and remove fields from the pane by dragging and dropping them or by using the toolbar buttons.

The pane toolbar includes only standard buttons.

The Properties pane

This pane contains the display properties for a field that is selected in the Filters, Columns, Rows, or Values pane.
The following table describes the properties.

Property	Description
Caption	The caption of the field that is displayed in the pivot table. You change the caption in this field if you want to see a different field name than the field name in the generic inquiry in the pivot table. By default, the property value is equal to the field name in the generic inquiry.
Aggregate	An aggregate function that is applied to the field values. Select one of the following functions: Sum: Returns the sum of the field values (for numeric fields) Count: Returns the number of the field values (for all types of fields) Average: Returns the average value of the field values (for numeric fields) Min: Returns the smallest value among the field values (for date and numeric fields) Max: Returns the largest value among the field values (for date and numeric fields) Median: Returns the middle value of the field values (for date and numeric fields) The default value of this property is Sum for numeric fields and Count for non-numeric fields.
Sort order	The order in which the field values are sorted in the pivot table. Select one of the following values: Ascending: That is, A to Z for text, lower to higher for numbers, or newest to oldest for dates Descending: That is, Z to A for text, higher to lower for numbers, or oldest to newest for dates The default value of this parameter is Ascending.
Show percent	A check box that you select if you want to display value of the field as a percentage of the total value. This check box should be used with fields from the Values pane only. By default, the check box is cleared.
Show total	A check box that you select if you want to display the total field for the set of fields. By default, the check box is selected.
Total label	The name of the total field in the pivot table. The field is displayed only when the Show total check box is selected. By default, the field is empty and the field label is Total.
Width	The width (in pixels) of the cells in the pivot table where the field values are displayed. The default value of this parameter is 100 pixels.
Format	The format of the field value that is displayed in the pivot table. You can use the standard formats defined for the format function in .Net. By default, the field is empty, which means that the field is not formatted.
Round to	The part of the date field that is used in the pivot table. You can analyse data by using either the full date or a particular part of it (for example, a month or a year). This property appears only for fields of the date type. Select one of the following values: None (default): The full date is used in the pivot table. Minutes: Only minutes from the selected date field are used in the pivot table. Hours: Only hours from the selected date field are used in the pivot table. Days: Only days of month from the selected date field are used in the pivot table . Months: Only months from the selected date field are used in the pivot table. Quarters: Only quarters are used in the pivot table. The system calculates the quarter based on the date in the selected date field. Years: Only years from the selected date field are used in the pivot table.
Date part	The part of the date field by which the system aggregates data in the pivot table. For example: If the date field contains the month and the year (such as July 2017), you can configure the system so that it aggregates data by month for multiple years. If you want to display only the year in the headers of the pivot table, you need to select the Year value in the Round to field. You can select one of the following values: None (default): The data by full date is displayed in the pivot table. Hour: The system aggregates data by the hour. (Only the hour is displayed in the pivot table.) Day: The system aggregates data by the day. (Only the day is displayed in the pivot table.) Month: The system aggregates data by the month. (Only the month is displayed in the pivot table.) Quarter: The system aggregates data by the quarter. (Only the quarter is displayed in the pivot table.) This property appears for only fields of the date type. You can specify either a part of the date in this box or the rounding specified in the Round to field. For example: If you specified a value in the Round to field and then selected a value in this field, the system automatically resets the value of the Round to field to None.
Segment	The segment of the field value that the system uses to aggregate data in the pivot table. In this field, you can select one of the segments specified in the Segment keys (CS202000) window for the segment key. This property appears for only fields whose values are defined by a segment key (such as General ledger subaccounts).

The pivot table title bar

The title bar of the window that contains the generated pivot table (which appears when you click View pivot from the window) includes standard and window-specific buttons.

Buttons	Description
Save as	Creates a copy of the pivot table with a different name. You click this button when you change the pivot table settings and want to save it as a new table for the same general inquiry.
Save	Saves changes to the pivot table. You click this button when you change the settings of the pivot table and want your changed version to replace the previous version of pivot table.
Customise	Opens the Pivot tables (SM208010) window, where you can modify the pivot table settings.