About pivot tables in Visma Net

Before you start configuring a pivot table you need to do the following:

• Create the generic inquiry that will be used as a data source for pivot tables if this inquiry doesn't already exist.
  For details, see: Design an inquiry based on inventory data.
• Plan which data from the generic inquiry you want to see in rows, columns, and the data area of the pivot table; also plan which data should be used for filtering.

You perform the following steps to configure a pivot table in Visma Net in the Pivot tables (SM208010) window:

1. Go to the Pivot tables (SM208010) window.
2. In the Window ID field, you select a generic inquiry from the list of existing generic inquiries.
3. By dragging the fields to the appropriate panes of the window, you select the generic inquiry fields that will be used in rows, in columns, and in the data area (the Values pane) of the pivot table; you also select the fields that should be used for filtering by dragging and dropping them to the Filters pane in the window.
4. Optional: You specify display properties for the generic inquiry fields that are used in the pivot table, such as a custom caption, sorting type, aggregate functions, and cell width.
5. Optional: In the Sitemap location field in the top part of the window, you specify the position of the created pivot table in the Visma Net site map if you want users of your Visma Net instance to access it in the site map.
6. Optional: You share the pivot table within yourVisma Net instance if you want other users of Visma Net to use it, by selecting the Is shared check box.
7. Optional: In the top part of the window, you select a shared filter that will be applied to the pivot table.

For the detailed procedure you should use to add a pivot table, see: Add a pivot table.

If you want to change the format of the fields to be displayed in a pivot table, you can specify the required format in the Format field of the Properties pane in the Pivot tables (SM208010) window. In this field, you can type one of the standard formats defined for the format function in .Net.

For date fields, in the Properties pane, you can configure the following extended format settings:

• Rounding data with the specified accuracy:
  You can select the accuracy of calculating data gathered by date.
  For example:Suppose that for each sales manager, you want to analyse sales amounts aggregated by quarters of the year.
  Suppose that the sales amounts are collected daily, which means that the date field in the generic inquiry with the required data contains the day, the month, and the year (such as 04/11/2016). To aggregate the sales amounts by quarter in the pivot table, in the Round to field, you select Quarters, and the date will contain only the year and the quarter (such as 2016 Q4) in the pivot table.
• Aggregating data by the particular date part:
  You can select the part of a date field by which you want to aggregate data in a pivot table.
  For example:Suppose that you want to analyse the seasonal demand on T-shirts for the last five years.
  To solve this task, you can build a pivot table that displays sales amounts aggregated by month.
  Suppose that the date field in the generic inquiry with sales amounts for T-shirts contains the month and the year (such as July 2017).
  To make the system aggregate data by month and display only months in the pivot table, in the Date part field, you select Month.
• Displaying hierarchical column or row headers:
  If you want to configure dates in columns or rows for a pivot table to meet your needs, you can set up a hierarchical structure of parts of the dates.
  For example:Suppose that you want to analyse the amount of shipped laptops by month for the last three years.
  Suppose that the date in the generic inquiry contains the day, the month, and the year (such as 04/11/2016).
  In the pivot table, you can display a one-level column, which will contain the following date values: Jan 2015, Feb 2015, and so on until June 2017.
  Alternatively, you can configure the system so that it displays two levels of column headers: In the first level, the system displays the years, and under each year, the system displays the months. To configure the system in this way, you add two copies of the date field to the Columns pane of the Pivot tables (SM208010) window.
  For the first copy, in the Round to field, you select Years; for the second copy, in the Date part field, you select Month.

For fields for which segmented keys are configured to have multiple segments, in the Properties pane, you can configure the following format settings:

• Aggregating data by the particular segment:
  You can select the segment by which the system will aggregate data in a pivot table. For example, suppose that your company sells fruit and vegetables to restaurants.
  Suppose that the item class values consist of the following segments: the item category (FR or VEG), the item type (APL, ORG, CBR, or PTO), and the country of origin (such as ES, CN, or MA). If you want to analyse the amounts of item types shipped to each customer by using a pivot table, in the Segment field, for the item class field, you select the segment that corresponds to the item type. With these settings, the system will aggregate sales data by the item type (such as apples, designated by APL).
• Displaying hierarchical column or row headers:
  If you want to analyse data by a combination of two segments of a field value, you can set up a hierarchical structure of the segments.
  Suppose that in the example described in the previous item, you also want to add to the pivot table the country of origin for each item type.
  To address this task, to the Columns pane, you add two copies of the Item class field.
  For the first copy, in the Segment field, you select the segment used for the item category; for the second copy, in the Segment field, you select the segment used for the country of origin.

In Visma Net, you use the data from generic inquiries to build pivot tables.
For a single generic inquiry you can create multiple pivot tables that will summarise data in different dimensions.
You use only one generic inquiry to build each pivot table.
If you need to build pivot tables with information stored in multiple generic inquiries, you must first create a single generic inquiry that includes all the necessary data.

When you modify a generic inquiry that pivot tables are based on (for example, when you add or remove fields), you may need to reflect these changes in the pivot tables.
To do this, you click Pivot tables in the generic inquiry title bar and click a pivot table name you want to open.
In the pivot table window that opens, you click Customise in the window title bar. In the Pivot tables (SM208010) window that opens, you modify the settings of the pivot table.

The data for pivot tables is stored separately from generic inquiries data in the system database.
This makes it possible for users to work with pivot tables even if the generic inquiry on which they are based is unavailable for some reason.
In addition, you can allow users to access only pivot tables, if security policies of your organisation require this.

Users of your organisation may open configured pivot tables as follows:

• Through the site map, if you added the pivot table there during configuration.
• From the generic inquiry window, by using the Pivot tables menu item in the window title bar, which appears when at least one pivot table is created for this generic inquiry.