Pivot Table

The Nectari Pivot Table feature is a powerful way for you to easily pull out data in the form of a cube without extracting the raw data to Excel first. It only uses Nectari data models to get the required data.

To create a Nectari pivot table:

  1. Click Pivot Table in the Add-ins tab.

  2. The Pivot Table Editor window is displayed.

    1. Choose the Data Model and the Environment .

    2. Enter the Dimension Settings to filter the resulting data (including the List, From.. To and Add selection to filter fields) the same way as a Formula.

  3. Select the columns to be used with the > and < arrows.

    Tip

    If you set up a cell for the Reporting Tree Node (refer to Reporting Tree), the Pivot Table feature will automatically use the Reporting Tree as a filter.

    Important

    Please note that changing the Node in the Reference Cell (the one containing the Reporting Tree Node) will not trigger automatically the Pivot Table Refresh feature. You have to click on Pivot Table Refresh to update the table based on your new selection of the Reporting Tree Node.

  4. At the bottom of the Pivot Table Editor window, choose if you want the result to be displayed in a new worksheet or in the same worksheet If you select existing worksheet, you need to specify the starting cell in the field.

  5. Click OK for the resulting cube to be made available in the workbook.

  6. You can then use it as a regular Excel Pivot Table by adding Values, Filters, Rows and Columns to the Report area.

    You can use wildcards to filter values in a pivot table.

    Wildcards can only be used on text strings (not on numbers). Only one logic must be used at a time; for example, you cannot use 4???;[4000:4999].

    • ? replaces one character

    • * replaces multiple characters

    • ! excludes characters

    • ; defines a list

    • [ ] defines a range

    Example of filtering values Description
    4000 Single value
    4000;5000;6000 Single list
    4* Must starts with 4
    4??? Must start with 4 and have a length of 4 characters
    [4000:4999] Range between 4000 and 4999
    !4000 Must not be 4000
    !4* Must not start with 4
    !4???

    Must not start with 4 and must not be 4 characters long
    ![4000:4999] Must not be between 4000 and 4999
    4???-???-10

    Must start with 4, followed by - and any 3 characters, followed by - and any 3 characters, must finish with 10, and must be 11 characters long
    !1000;2000;7000 Must not be in the list: 1000;2000;7000

Pivot Table Refresh

The Pivot Table Refresh feature is used to refresh the Pivot Table results with up-to-date data from the database.