Permissions: Any Data Central user can create, save, and edit a visualization, but the user must have the Data Central Designer privilege to save a visualization as public or edit public visualizations.

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 chosen categories, such as ethnicity, gender, and adverse event 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. Start from the left navigation, locate the Visualizations section, and click the '+' New Visualization icon.
2. Click Pivot Table to open the designer window.

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

   In the example below, the Search feature was used to locate the AEREL (Relationship to Drug) field in the Clinical data store and the Adverse Events domain by typing part of the field name.

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

Define Columns

Note: You 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. Drag the AE:AEREL and DM:SEX fields into the Columns area in the designer, and the preview will refresh. By default, the first field you place in the column area is added to the Values area as a count. (For more details see the Define Values section.) In the Preview below is a count of the records in the Adverse Events domain, grouped by AEREL.

   

2. When additional columns are added, they break down the groups into sub-groups. For example, when the SEX field was added, it divided each AEREL by male and female.

   

Define Rows

Columns arrange the data groupings horizontally, while the rows group the data vertically by organizing records into distinct categories.

1. Find the DM:ETHNIC field and drag it to the Rows area.

2. The pivot table will refresh, and each row will represent a value from the ETHNIC field.

   

   The columns display the AEREL and SEX fields. When viewing the pivot table, the AEREL column can expand to show the SEX dynamically. The rows represent the ETHNIC categories.

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

Add a Filter to Columns / Rows

1. To add a filter to a column or row, click the Filter icon to the right of the field name. A drop-down displays a list of unique values for the field. A checkbox appears to the left of each value in the list.

2. The Select All checkbox is selected by default. To filter the results in the pivot table, check the box to include the values or uncheck it to exclude them.

   

   In this example, we only want Hispanic or Latino records or Not Hispanic or Latino.

3. Click OK.

Define Values

1. The AEREL field was added to the Values area with a count aggregate, as it was the first field included in the Columns section. Click the 3-dots next to the field name to open a configuration pop-up.
    
2. Select an Aggregate from the drop-down:
   • Count of Subjects
   • Count Distinct
   • Count (default)
   • Sum
   • Average
   • Minimum
   • Maximum
   • Standard Deviation
   • Variance
3. Select the Calculation from 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 AEREL value:
   • Aggregate: Count
   • Calculation: No calculation.
   • Label: # of Adverse Event Records
   Click OK.
6. To add a second Value, locate and drag the DM:AGE field to the Values area with settings:
   • Aggregate: Average
   • Calculation: No calculation.
   • Label: Average Age of Subjects
   
7. Click OK and table will refresh.

Add a Dynamic Filter to a Value

1. Click the Filter icon next to the AGE field. The filter pop-up opens.

   
2. Include Selections is the default. 
3. Click the Filter Field textbox and select the field AGE from the drop-down.
4. Click the Operator field and select Between.
5. Click the Filter Value 1 field and select the lower bound value from the drop-down or enter your own value.
6. Click the Filter Value 2 field and select the upper bound value from the drop-down or enter your own value.
7. Click the Dynamic Filter toggle on. 
8. Click OK. The pop-up closes. There will be a drop-down at the top of the pivot table where the table can be filtered by the values entered.
   
   Note: For more details see the Dynamic Filters section.

Pivot Table Filter Section

1. From the Fields area, locate the DM:SITE (Site Name) field and drag it to the Filters area to open pop-up.
2. The default setting is Include Selections, which can be changed to Exclude Selections if preferred.
3. The SITE field is pre-populated in Filter Field textbox.
4. Open the Operator drop-down menu and choose from the available options: =, <, >, Null or Empty, or Between. In this example, '=' has been selected.

5. Select the values to filter to in the Filter Value drop-down menu. This example includes Mansfield Hospital, Mayo Clinic, Oncology Center of PA, Sacred Heart Medical Center, and St Mark's Hospital.

   

Note: The filter icon will become shaded after defining any filter for the table.

Dynamic Filters

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

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

2. Click the Optional Default Filter Value(s) textbox to select the default values that the published pivot table filters to when opened. In this example, all five values are set as defaults, which means the table will open filtered by all five selected values.

   

   If no default value(s) are configured, the table will filter by the first value in the list, opening with all records filtered by Mansfield Hospital. 

   Click OK. 

   Tip: Click in the whitespace to close the drop-down if the OK button is not visible.

3. When viewing the published pivot table, a drop-down menu with the filter values appears at the top. To apply the filter, click the drop-down arrow. Then, click the blue box labeled 5 selected to view the list of the five values available for filtering.

   

4. Select the values to filter, then click Apply, and the table will immediately reflect the applied filter.

   
   

   Note that now the blue box shows the number of values selected, which is 3 in this case. 

Reorder / Remove Fields

• Click and hold the 6-dots next to a field and drag it above or below another field.
• Click the 'X' at the right of a field to remove it

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 Table Configuration

After creating or editing a pivot table, the table configuration can be exported directly from the designer. This allows users to easily replicate their pivot tables across different environments, eliminating the need to recreate them manually. To export, click the Export icon in the upper right corner of the table's designer window and it will download the configuration file.

To import a Pivot Table configuration, click the '+' New Visualization icon and select Pivot Table. After the designer window opens, click the Import icon in the upper right corner. This will open a pop-up to upload the configuration file.

Note: Users with the Data Central Designer privilege can also export and import pivot tables created by other users.

Create Chart from Pivot Table

To create a chart based on the data in the pivot table, click the Create Chart icon on the toolbar. You can choose from options such as Create Combo Chart, Create Scatter Chart, or Show Inline Synchronized Chart. In the example below, the inline chart has been selected.

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 pop-up window to enter filter parameters. Click OK. This filter applies to the panel only and does not appear in the Filter Panel in the left navigation.
	Notes	Available when a note was added in the designer window. Click to see the note in the drop-down.
	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.
	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 your sheet.
	Maximize	Click to maximize visualization.
	Restore	Click to restore the visualization to previous size.
	Close	Click to close.

Note: When an icon is highlighted blue, it indicates that it is activated / on, whereas when 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. Once a visualization is saved, both the Save and Save As buttons will display. 

The Save Pivot Table window will open when you click Save for the first time or by clicking Save As. 

Tip: Clicking Save As on an existing table allows you 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, you 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 will be saved.

   Note: If you have the Data Central Designer privilege, you 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. Only selectable with Data Central Designer privileges.
4. Select the Roles (visible when saving public only, grayed out if the visualization is private). Users with the selected role(s) will see and have access to the visualization.
5. Update the Scope: By default, the study you are currently in 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 name you wish to edit.
2. Click on the Edit icon (the pencil).
   
   

   Note: You many also click the pencil (edit) icon in the published table's toolbar to open the designer.

3. The Pivot Table Designer window opens. 
   
4. Edits can be performed on all fields in the Columns, Rows, Values, or Filters areas in the same way as when creating the pivot table. 
5. Edit the Note.
6. Click Save to save edits to the table you are viewing OR click Save As to save as a new pivot table.
   

Edit Table Settings

1. Hover over the table name you wish to update.
2. Click the Configure (gear) icon to open the Save Pivot Table window.
   
3. Make updates in the Save window.
4. Click Save to confirm changes or Cancel to cancel.

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 located on the right in the master header. The Manage window opens.
   
2. Hover over the name of the item you want 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 your action).