Periscope Data Documentation

Connecting to Periscope

First Time Connecting

Connection Definitions

Common Connection Errors

Private Network SSH Tunnels

Integrations

Overview

Getting Started

Keyboard Shortcuts

Charts & Dashboards

Chart Overview

Chart Options

Dashboard Overview

Refresh Mechanics

Organizing Charts

Organizing Dashboards

Table-like Charts

Table Overview

Cohort Grids

Pivot Tables

Graph-like Charts

Overview & General Settings

Chart Types and Settings

Mixing Chart Types

Map Charts

Map Chart Overview

Heatmaps

Clusters and Bubbles

Satellite Maps

GeoJSON

Other Charts & Customizations

Pie Charts

Image Charts

Chart Annotations

Site Administration

Adding Users

Removing Users

User Types

User Groups

Two-Factor Authentication

Managing User Permissions

Dashboard Permissions

Site Preferences

Query Management

Periscope Cache

Caching Strategies

Querying the Periscope Cache

Cross-Database Joins

CSV Upload

Materializing Views

SQL Formatters

Using SQL Formatters

Composing SQL Formatters

Dollars and Percentages

Time Zone Formatters

Date Aggregation

Date Parts

Basic Filters

Custom Filters

Date Range Filters

Aggregation Filter

Adding, Moving, and Hiding Filters

Advanced Filters

Default Filters

Drilldowns

Parent-Child Filters

Matching Filter Values

Time Zone Conversions

Direct Replacement

User Friendly Names

Views

SQL Views

Joining to Views

More SQL Tools

SQL Snippets

Parameterized SQL Snippets

SQL Alerts

Automatic Joins

Sharing and Embedding

Sharing Dashboards Externally

Email Reports

Easy Embedding

Embed API

Embed API Options

Public CSV URL

Slack API

Slack Linking Reports

Data Management

Adding and Deleting Datasource

Spaces: Data-Level Permissions

Improving Query Throughput

Revoking Table Permissions

Salesforce Datasource

Schema Browser

SSO Integrations

Overview

Okta

OneLogin

Google Apps

Pivot Tables

To view this documentation for the Periscope Classic UI, click here.

Pivot Table Overview

Pivot tables allow users to interact and transform datasets without changing a chart's SQL. Through dragging and dropping the columns and rows from a result set, pivot tables can display, filter, and aggregate data all at the dashboard level. Pivot tables are automatically included in shared and embedded dashboards, allowing external partners to further analyze result sets.

Creating a Pivot

Pivot tables are an extension of all table-like charts. Click the "Edit Pivot" option after choosing the hamburger icon in the upper right-hand corner of the chart.

This will bring up the Pivot Settings menu of options.

Columns and Rows

To assign a field as a row, drag it from the Fields list into the Rows box. To add columns, drag the field from Fields list to the Columns box.

Values

Data values can be added to a pivot table to show an aggregated value per column and row.

Fields used as values are aggregated for their respective row and column. Aggregation options include: Sum, Count, Average, Minimum and Maximum. To set the value's format, click the arrow next to the value to open its dropdown, and select a format option from the aggregation dropdown.

Aggregated values can be formatted within the pivot table. To set the value's format, click the arrow in the value to open its dropdown, and select an option from the format dropdown on the right side. Note: formatting applied in the Series Settings tab of the chart editor will apply to the pivot table values, while pivot table values formatting will supplant, but not alter, the underlying table's formatting.

Filters for Pivots

Data within a result set can be filtered within the pivot table to limit the data being displayed. To filter on a field, drag it from the Fields list to the the Filters box.

String and date values can be filtered by selecting one of the following options from the dropdown and entering a string: Equals, Does not Equal, Contains, Begins with, and Ends with.

Numeric values can be filtered by selecting one of the following operators: >, <, ==, !=, >=, and <=.

Fields selected as columns, rows, or values can be used as filters for pivot tables.

Note: filters used in pivot tables are separate from dashboard filters and only filter the data within that table's result set.

Modification and Formatting

Available Fields for Pivoting

The fields and data available for the pivot table come from the SQL query generating the underlying table. Fields for the pivot table can be adjusted by modifying the columns in the SQL query.

Combining dashboard filters with pivot filters allows for even more customization in the data available to pivot.

Formatting Modification

Pivot table columns automatically carry over numeric formatting from the table's Series tab. Additional formatting for Values used in the pivot table can be adjusted in the Values section of the pivot. However, these settings will not be kept once the pivot table is restored to the non-pivoted version as the underlying table.

Settings and Permissions

Customized pivot tables are similar to Periscope's custom filters -- the pivot table settings are user-specific views of the data. This means that two users can pivot the same table chart with different settings, and not affect each other's work. However, as with filters, one pivot setting can be set as the default setting for the dashboard for all users. Under this circumstance, new users to the dashboard will see the default pivot setting of the table.

Pivot Creation and Editing Permissions

Pivot tables can be created and edited at the dashboard level by all users who have View access to the dashboard. Since one user's pivot settings don't affect another user's pivot settings, read-only users cannot affect each other's pivots.

Default Pivot Table Settings for Dashboard

Similar to filter defaults, pivot settings can also be set as the default view for all users on the dashboard. In order to set a user's pivot table settings as the default setting, the user must be a write-access user and have edit access to the dashboard. To set one pivot settings as the default for all users of the dashboard, click on the Edit Chart icon of the table to open the Editron.

Inside the Editron of the chart, click the "Edit Pivot" icon to bring up the Pivot Settings menu for the pivot table.

Within the right hand side of the table's Pivot Settings, turn the toggle from Private to Default, which will save changes made as the default pivot settings for all users.

Once saved, all users will see the default pivot table settings when visiting the dashboard unless they have a different user-specific pivot setting.

Resetting to Default Settings

The Reset to Default link at the top right of the Pivot Settings menu will always reset pivot settings to the default pivot settings. The non-pivoted version of the table will act as the default only when no user has saved pivot settings as the Default Pivot Settings for all users on the dashboard.

However, if the chart has a pivoted table as the Default Pivot Settings, the Reset to Default link will only reset the pivot settings to the Default Pivot Settings. To change the pivot table to a non-pivoted version of the table, the user can either edit the pivot settings at the dashboard level by removing all fields from pivot, or the user can use the Edit Chart link at the top right of the table and remove all fields used for the pivot.

To make the non-pivoted version of the table the Default Pivot Settings of the table, a write-access user with edit access on the dashboard needs to click the Edit Chart icon at the top right of the chart. After removing all fields used for the pivot, the user can then turn the toggle from Private to Default and save this non-pivoted version as the default view for all users on the dashboard.

Sharing a Pivot Table

Pivot tables take on the default view that is configured from within the chart editor when shared with others using the dashboard link. The default view can appear as the non-pivoted version of the table or as a pivoted table depending on what is configure.

Similar to dashboard filters, once a user makes a change to a table, those settings are kept with that user until they are changed or reset to the default view.

Tables can be pivoted outside of Periscope within a shared or embedded dashboard. Pivot settings are maintained as long as the page is open and will reset to the default once the page is left or refreshed.

Notes and Tips

Turning off Pivotability

Pivot tables cannot be turned off for a table. However, since pivot table settings are user-specific, one user's pivot settings will not affect those of another user.

Pivot Table and Drilldowns

Drilldowns are not supported on pivot tables.

Pivot Tables in Shared & Embedded Dashboards

Shared and Embedded dashboards do not have user-designations. As such, pivot settings are reset as default settings when shared or embedded dashboard is refreshed.

Pivot Timeout

Pivots that take longer than 3 seconds to render will be automatically paused to prevent the browser from getting overloaded.