Permissions: Any Data Central user may create, save, and edit a visualizations privately for personal use. Saving a visualization as public or editing an existing public visualization requires the Data Central Designer privilege.

A Pivot Table summarizes and organizes datasets by grouping and aggregating data based on selected categories. In a standard table, each row represents a single record, and columns contain specific details. 

In a Pivot Table, rows and columns group data based on the selected categories, such as ethnicity, gender, and causality, while values are aggregated rather than listing every individual record. Pivot Tables support various calculations, such as counts, sums, and averages, helping to transform data into a structured, meaningful format.

Create a Pivot Table

1. From Visualizations in the left navigation, click the New Visualization (+) icon.

   

2. Click Pivot Table to open the Pivot Table Designer window.

3. In the Fields section, click the arrows, Data Store names, or Domain names to expand the data. Utilize the Search feature or scroll down to view all available domains and fields.

   

Tip: Search also supports dot notation, e.g. DataStore.Domain.Field.

Note: System Tables contain operational data that can be used in pivot tables. Users cannot view or query these tables directly, but can request pivot tables that use their data. Any table created from System Tables is visible and usable by all dashboard viewers when saved.    

Tables available for request include: Issue, Query, Review Objectives, Review Objectives Domains and Fields, Review Objectives Review Materials, Review Objectives Reviewer Roles, Review Status Metrics by Domain and Role, Review Status Metrics by Domain and Role and Subject, and Subject.

Define Columns

Note: Users can add multiple fields from any domain in the same data store for Columns, Rows, Values, and Filters, but the total combined should not exceed 6. Additionally, the total number of combined domains used must not exceed 5.

1. Select two fields where a hierarchy exists for drill-down functionality. Drag the two fields into the Columns section. Make sure to drag the top level field first and then the second field which are sub-groups of the first field. The preview refreshes. By default, the first field placed in the Columns section is added to the Values section as a count. (For more details, see the Define Values section.) 

   

   Tip: When selecting fields for columns, avoid free-text fields, unique identifiers, or fields with many distinct values that produce too many columns.

2. When additional columns are added, they break down the groups into sub-groups. 

    

Define Rows

While columns arrange data groupings horizontally, rows group data vertically by organizing records into distinct categories. Select fields that naturally group records vertically and support drill-down.

1. Select an appropriate field that groups records, and drag onto the Rows section.

2. The pivot table refreshes, and each row represents a value from the selected field.

   

Define Values

1. The first field that is added to the Columns section is also added to the Values section with a Count aggregate. Click the 3-dot icon next to the field name to open a configuration popout.
   
2. Select an aggregate from the Aggregate drop-down:
   • Count of Subjects
   • Count Distinct
   • Count (default)
   • Sum
   • Average
   • Minimum
   • Maximum
   • Standard Deviation
   • Variance
3. Select the calculation from the Calculation drop-down:
   • No Calculation (default)
   • % of Grand Total
   • % of Column Total
   • % of Row Total
4. Optionally: Update the Label. By default, it displays the selected aggregate of the selected field.
5. Verify settings for the Value. Click OK.

6. To add a second Value, locate and drag an appropriate field to the Values section. Select the Aggregate and Calculation for the Values field.

   

7. Click OK and table refreshes.

Apply Filters to Columns / Rows

1. Click the Filter icon to the right of the field name to open the popout list.

2. The multi-select list displays the unique values in that field. Select values from the list or choose to Select All.

   

3. Click OK. The pivot table opens filtered by the selected values.

Note: Column and Row filters are static and do not support Dynamic Filters. 

Apply Filters to a Value

1. Click the Filter icon to the right of the selected field to open the popout. 
   
2. Use the default setting, Include Selections, or change to Exclude Selections.
3. Click Filter Field to open the list and select the field to filter by.
4. Click Operator to open list and select =, <, >, Null or Empty, or Between. 
5. Click Filter Value(s) to open list and select the values to filter by.
6. Click OK. The pivot table opens filtered by the selected values and the filter icon becomes shaded.

Tip: Click in the whitespace to close the popout if the OK button is not visible.

Note: To filter out NULL values for columns, rows, values, or the entire Pivot Table, select the Exclude Selections radio button and then choose the field to filter. Select the Null or Empty operator and click OK.

Note that the Dynamic Filter option is not allowed for the Null or Empty operator.

Apply Dynamic Filters

Dynamic Filters allow users to select predefined values from a popout menu located at the top of the finalized pivot table. When viewing the table, users can pick one or multiple values from the popout menu, which immediately updates the table to display the selected filter values.

Value and Pivot Table filters can be configured as dynamic.

1. Turn on Dynamic Filters by clicking the toggle switch.
   

2. Click the Optional Default Filter Value(s) and select the default values for the published chart. The list displays values that are defined in the original filter before it is converted to a dynamic filter. When opened, the chart is filtered using the selected defaults. 

   If Select All is checked, the chart opens unfiltered, allowing viewers to choose values in the dynamic filter; the pivot table updates as selections change. 

   

   If no default value(s) are configured, the table opens filtered by the first value in the list.

3. Click OK. 

Tip: To collapse or expand each section (Columns, Rows, Values, Filters), click the down / up arrows. Click the box icon to view only one section and collapse all other sections.

Apply Pivot Table Filters

The filters that are applied in this section affect the entire visualization and are not limited to a single domain like the filters for columns, rows, and values. This flexibility makes filter creation more versatile.

1. From the Fields section, select a field to filter the entire chart by and drag it into the Filters section and a popout opens. 
2. Use the default setting, Include Selections, or change to Exclude Selections.
3. Click Operator to open list and choose =, <, >, Null or Empty, or Between.

4. Click Filter Value(s) to open list to select the values to filter by.

   

5. Click OK. The chart opens filtered by the selected values. Up to 25 filters may be added. 
6. To apply Dynamic Filters to the chart, refer to the Apply Dynamic Filters section.

Note: Each Dynamic Filter, whether defined for a Value, or at the Pivot Table level, appears as a popout menu at the top of the published table, allowing users to select one or more values. When users hover over any filter control, a tooltip shows the current selections.

Reorder / Remove Fields 

Fields can be reordered or removed from any section in the designer window.

• Reorder Fields: Click and hold the 6-dot icon next to a field and drag it above or below another field.
• Remove Fields: Click the 'X' at the right of a field.

Add a Note

When text is entered in the Notes textbox above the preview in the pivot table designer, it becomes viewable to the end user by hovering over the Notes icon in the published table.

Export / Import Pivot Table Configuration

After creating or editing a pivot table, the table configuration can be exported or imported directly from the designer window. This allows users to easily replicate their pivot tables across different environments, eliminating the need to recreate them manually.

Export a Pivot Table Configuration

Click the Export icon in the upper right corner of the designer window. The configuration file downloads automatically.

Import a Pivot Table Configuration

1. Click the New Visualization (+) icon and select Pivot Table. 
2. After the designer window opens, click the Import icon in the upper right corner. 
3. In the popout, upload the configuration file.

Note: Users with the Data Central privilege can export or import thier own pivot table configurations. Users with the Data Central Designer privilege can also export and import pivot table configurations created by other users.

Create Chart from Pivot Table

Pivot Tables can easily be translated into a chart by using the Create Chart icon on the toolbar. Users can choose from options such as Create Combo Chart, Create Scatter Chart, or Show Inline Synchronized Chart. 

After creation, the pivot table name appears in the Data Central left navigation directly under the pivot table name in italicized text.

Pivot Table Toolbar Actions 

Pivot Tables have a unique panel toolbar and other functionality which is slightly different than other panels and listings in Data Central. Below are the icons available for a Pivot Table.

Icon	Icon Name	Description
	Edit*	Click to open designer window.
	Add Filter*	Click to open a popout window to enter filter parameters. This filter applies to the panel only and does not appear in the Filter Panel in the left navigation.
	Show / Hide Totals	Click for options to hide row, column, or population totals. Population totals are only available when more than one domain is added. Click to select from: Hide Row Totals, Hide Column Totals, or Population Totals > Hide population totals, Show population total from XX (domain name) table, Show population totals for matching records from XX (domain name) table(s). Population Totals is only available when more than one domain is added.
	Transpose Rows and Columns**	Click to flip rows and columns. This is only available when creating or editing a pivot table.
	Show / Hide Labels	Shows / Hides the row and column labels.
	Truncate	Click to wrap text to see more columns or click to truncate text onto one line and then scroll to see all columns.
	Create Chart	Click for options to create a Combo or Scatter chart based on the data in the pivot table.
	Notes*	Click to see Notes (Text is entered above the preview in the pivot table designer.)
	Export*	A pivot table can be exported to an Excel file. The Excel file for the table includes the data, as well as metadata, such as the data store(s), domain(s), filter(s) applied to the pivot table, and the timestamp of the export, providing comprehensive context surrounding the exported dataset.
	Expand / Collapse	Click to expand or collapse all rows and columns.
	Dock Item*	Click to dock the visualization to the worksheet.
	Maximize*	Click to maximize visualization.
	Restore*	Click to restore the visualization to previous size.
	Close*	Click to close.

* Available in published table only 
** Available in designer only

Note: When an icon is highlighted blue, it indicates that it is activated / on, whereas when an icon is gray it is deactivated / off.

Save / Save As 

When first creating a pivot table, only the Save button displays in the bottom right corner. After a visualization is saved, both the Save and Save As buttons display. 

The Save Pivot Table window opens when Save is clicked for the first time or when clicking Save As. 

Tip: Clicking Save As on an existing table allows users to duplicate the table with another name or save the table as a new one with different settings from the original table, whereas clicking Save updates the existing pivot table.

When the Save Pivot Table window opens, users can enter or edit Save options.

1. Enter the Name of the pivot table (maximum 100 characters allowed).

2. Select the folder where the table is to be saved.

   Note: If users have the Data Central Designer privilege, they may also add a new folder by hovering over a current folder and clicking the add folder icon. Remember, managing folders after they are created (editing and deleting) is done through the Manage icon in the top right on the master header of the Data Central Landing page.

3. Select the Public or Private radio button. Users with the Data Central privilege only see Private as an option; Public is also available for users with the Data Central Designer privileges.
4. Select the Roles (visible when saving as Public only; grayed out if the visualization is saved as Private). Users with the selected role(s) see and have access to the visualization.
5. Update the Scope: By default, the active study is selected. Click the drop-down next to Therapeutic Areas, Compounds, Programs, or Studies, and update as needed.
6. Click Save to confirm save settings or Cancel to cancel changes.

Edit a Pivot Table

Pivot tables can be edited for their content, which includes the data they represent, columns, rows, and filters. Pivot table settings (found in the Save window) can also be updated and saved under a new name, to a different folder location, changed from private to public (Data Central Designer privileges required), assigned different roles (if public), and adjusted with a new scope. 

Edit Content

1. Hover over the table to edit.

2. Click the Edit icon. The Pivot Table Designer window opens.
   

   Note: Users may also click the Edit icon in the published table's toolbar to open the designer.

3. Edit any field in the Columns, Rows, Values, or Filters sections as needed.
4. Edit the Note.
5. Click Save to update existing table OR click Save As to save as a new pivot table. 

Edit Settings

1. Hover over the visualization to edit the settings.
2. Click the Configure icon to open the Save Pivot Table window.
   
3. Make updates in the Save window.
4. Click Save to confirm changes or Cancel to discard changes.

Delete a Pivot Table

Saved Pivot Tables, Visualizations, Workspaces, Filter Sets, and Advanced Filters are deleted from the Manage icon in the master header. Users must have the Data Central Designer privilege to delete items saved as Public; however, all Data Central users can delete items they have saved as Private.

1. Click the Manage icon in the master header. The Manage window opens.
   

2. Hover over the name of the item to delete.

   Tip: Hover over the icon at the left of the item name to see a tooltip that identifies the type of item, for example, Saved Workspace, Advanced Filter, Pivot Table or type of Visualization.

3. Click the Delete icon.
   
4. In the Delete Item confirmation window, click Delete or click Cancel to cancel the action.