Content Type: Pivot Table

The Pivot Table is a dynamic content type that allows you to upload CSV data, and return a feature rich data table complete with facets and search.

Example using the Pivot Table Display Type

Our User Focus Backlog page is using the 'traditional' Pivot Table Display Type

Example Using the Advanced List Display Type

The STEM group was the first usage of the new Advanced List Display Type

picture of the pivot table metadata fields

Screenshot of the Display Option field in the edit tab of a pivot table node

Please Note

Additional tickets to enhance the styling and mobile responsiveness of the new Advanced List display option for Pivot Tables are pending. In the meantime, the CMS Support Team recommend users continue to leverage the Pivot Table display option for Published pivot tables.

How to Create a New Pivot Table:

Required Steps

Select Pivot Table under the Content - Add Content option of the shortcut bar
Enter the title of the node in the title field. Helpful Hint: A good descriptive title helps boost the search relevancy of the pivot table.
Enter the Summary Text. The summary text displays as the text to provide context within a search result.
Enter a title for the pivot table data. This title provides context for the pivot table data set.
Click Choose File to Attach the .csv data file from your local drive to the pivot table.
1. You can click Remove to remove the current .csv file and upload a replacement file.
2. When saving as .csv from MS Excel, the file can include ‘special’ characters, which do not work well when uploading the file. You can clean up the file by saving it as a .csv file from Google Sheets or cleaning up the .csv in a .txt file.
Enter the column title from the .csv file in the CSV Column Title field
1. Make sure the title matches exactly to the column title used in the .csv file
2. Column titles in the .csv cannot include spaces—use Analysis_Categories vs Analysis Categories
3. If spaces are included in the .csv column titles, the pivot table will not render
Select the type of data represented in the .csv column in the Data Type field.
1. Accept the default setting or select Date, if the .csv column is a date.
Select the Display Option
1. When configuring your pivot table, you will be given the option to display your content in the classic pivot table display format, or as an Advanced List. The Advanced List option will have a different look and feel, as well as additional colors that do not appear on the regular Pivot Table Display.
Click Save as draft for publish if desired.

Struggling with your Pivot Table?

Common pitfalls:

Trying to upload the wrong file type
Entering a Column Title that does not match the Column Title displayed in the .csv
Including spaces in the.csv Column Title or after content in cells
Failing to configure all desired columns in CMS
Including special characters.

Select File >> Save As >> CSV UTF-8

Screenshot of Excel Number format: Custom Date mm/dd/yyyy

Known Issues with Pivot Tables

Allowing Special Characters (non UTF-8) to display in table

Facet search for results LIKE the item selected

Recreate Pivot Table "Link To Node" functionality

Rendering Blank Rows

Keep up to date with the developer's Backlog list here.

Advanced Steps

Select Pivot Table under the Content - Add Content option of the shortcut bar
Enter the title of the node in the title field. Helpful Hint: A good descriptive title helps boost the search relevancy of the pivot table.
Optional: Enter the short title.
Enter the Summary text. The summary text displays as the text to provide context within a search result.
Optional: Add Thumbnail. You may provide an optional image that will be used as a thumbnail or background image when the photo gallery node is referenced.
Optional: Add a Banner image. The banner image displays as background behind the Article title and date. The minimum recommended size for a banner image is 1240x600px. If no Banner image is applied, background defaults to display the dark green texture behind the title and date text.
Optional: Add a Banner Color. This sets the text to either light or dark with optional light and dark gradient background. This helps you ensure the node title contrasts with the background image for legibility and accessibility.
Enter a title for the pivot table data. This title provides context for the pivot table data set.
Click Choose File to Attach the .csv data file from your local drive to the pivot table.
1. You can click Remove to remove the current .csv file and upload a replacement file.
2. When saving as .csv from MS Excel, the file can include ‘special’ characters, which do not work well when uploading the file. You can clean up the file by saving it as a .csv file from Google Sheets or cleaning up the .csv in a .txt file.
Optional: Use the results per page field to set the maximum number of records to display per page.
Add Columns
1. Enter the column title from the .csv file in the CSV Column Title field
  1. Make sure the CSV Column Title matches the .csv file
  2. Column titles in the .csv cannot include spaces—use Analysis_Categories vs Analysis Categories
  3. If spaces are included in the .csv column titles, the pivot table will not render
2. Enter the column location in the CSV Column Location field. The column location is the numerical placement of the column within the .csv file.
  1. The number designation of the first column in the table is 0. For example, in the table below, the Title column is 0, the Technologies column is 1, and so forth.
3. Optional: Enter a column label for display within the pivot table in the Pivot Table Column Label field. Otherwise it will default to the CSV Column Title
4. Optional: the type of data represented in the .csv column in the Data Type field.
  1. Accept the default setting or select Date, if the .csv column is a date.
  2. Please note Date format must be exactly MM/DD/YYYY
5. Optional: Set the width of the column in pixels in the Column Width (PX) field. Column width relates to the width of the table column in pixels (px) being rendered for display.
6. Note: Link to Node functionality is currently under reevaluation, and is not currently operational.
7. Optional: Select Not Visible If you do not want this field to display in the pivot table.
  1. Not Visible columns can still be searched or used as a facet.
  2. If there are extra columns in the .csv that you do not want searched or displayed, best practive would be to remove them from .csv
8. Optional: Select Not Searchable If you do not want a column to be searchable.
9. Optional: If you want to disable sorting on this field, select Not Sortable.
10. Optional: If you want to enable this field as a facet item, select Facet Item.
  1. The facet is built using unique keywords that are separated by semi colons (;) in the .csv file.
  2. Within the .csv file, ensure the keywords match exactly if you want rows of data to share the same keyword. For example: Merit Review and merit review are considered different keywords.
  3. Ensure: matching case and no trailing spaces.
11. Optional: Set the display order of the facet blocks by using the Facet Order field for each facet item.
  1. Start at 1 for the first facet. Using zero hides the facet.
12. Optional: If you want to overwrite the default facet label (.csv column title), you can enter a custom label in the Facet Label field.
13. Optional: You can enter hover text for the facet label in the Field Hover Text field.
14. To add another .csv column item, click Add Data Table Column and repeat step #9 including the bullet items, as appropriate.
  1. Use the sort icon to drag and drop column definitions to rearrange the column display.
  2. Make sure the Column Order and the CSV Column Location field all match the column order in the .csv for best results
Optional: Configure the default sort for the table using the Data Sort fields
1. Enter the column number to sort on in the Column to Sort On field.
2. Optional; Select the order of the sort (that is—ascending or descending) from the Column Order drop-down.
3. The number designation of the first column in the table is 0. For example, in the table below, the Title column is 0, the Technologies column is 1, and so forth.
Save, review the Active Draft, and publish if desired.

Step 1: Original_Table.csv (28.99 KB)

Step 2: Excal-formula-WIP.xlsx (34.75 KB)

Step 3: Replaced-HTML-Links.csv (36.78 KB)

Step 4: Replaced-HTML-Links-trimmed.csv (36.77 KB)

Cool "new" feature: link by Node ID

Use cell value "/node/12345" to automatically add the node title and link to fill that cell!

Creating links from /node/[nid]: Workaround

I’ll show you how I went about setting up the fix for the Pivot Table node: /management/acquisition-letters. I have attached all the files separately in this process, so you could see it at every stage, though you could just overwrite the same file and re-upload it.

I downloaded the file from the Pivot table and opened it in excel : Step 1: Original_Table.csv
I created an excel formula to combine the tile and link into HTML : Step 2: Excal-formula-WIP.xlsx
I Copy/pasted the VALUES using ctrl-ALT-V back into the Subject Column, deleted the last line and Saved As.. a .csv file:Step 3: Replaced-HTML-Links.csv
This file isn’t QUITE ready to go yet- as Excel puts an extra blank line at the bottom of .csv’s which in turn adds an extra blank item to the Facet Item Fields. This extra line doesn’t get created in Google Sheets, so you need to use a text editor like Notepad to remove the blank line at the bottom. Step 4: Replaced-HTML-Links-trimmed.csv