Sign in
x
☰

Generate large crosstabs to Excel using Protobi’s SuperTables

Updated at 2022-02-09 13:08:01 UTC

Supertable view displaying a crosstab with two questions about Condition X patients crossed by S1 (GP/FN) columns and multiple Country columns (Australia, Germany, Greenland, etc.) showing percentage distributions.

Create a stylized crosstab table

Create the online table view

Add a new tab to your project. You can give it any name. In this example, the tab is “supertable_questions”.

Protobi interface with left sidebar showing project structure including Screener, Banners, multiple sections (Section 1, Section 2, Section 3), and supertable_questions element selected, with a blank canvas on the right displaying the supertable_questions heading.

In the JSON editor set the chartType to "SuperTable":

Element properties editor dialog titled 'Edit element properties: supertable_questions' displaying JSON configuration with key properties including roundby: null, key: 'supertable_questions', and chartType: 'SuperTable' highlighted on line 19.

After setting chartType:

Supertable_questions view with empty state showing two blue drop zones: 'Add banners by dragging elements here' at the top and 'Add stubs by dragging elements here' below it, with a basic table structure showing 'N' column header, 'Overall' row label, and values '100%' and '100'.

Drag the questions you want to include in the table into the grey box labeled "Add stubs...". Drag the banners you want to include to the blue header labeled "Add banners...".

Supertable builder showing 'supertable_questions by S1' with GP and FN columns in the banner, and left sidebar displaying elements including a highlighted 'Country' element with blue selection and a tooltip reading 'Protobi can use Google Translate...'

The result is a neatly stylized table:

  • Questions and values are on the left.
  • The crosstab banners show as columns to the right.
  • Press the red "x" on banners and stubs to remove them. 
  • To reorder the stubs, drag + drop.

 

Complete supertable with three blue instruction callouts overlaid: 'Press the x on a banner to remove it' pointing to the Country banner's x button, 'Press the x on a sub to remove the question' pointing to a question's x, and 'Drag stubs to reorder the questions' pointing to question stubs.

Export SuperTables to Excel

Add another tab to the project. You can give it any name-- in this example, the tab is “supertables”. 

Drag questions into the new export tab. Select “mirror” when the move dialog appears. This will add the question under Export, but also keep it in its original location.

In the JSON editor set the chartType to "SuperTables":

Element properties editor titled 'Edit element properties: supertables' displaying JSON configuration with key properties including roundby: 'auto', key: 'supertables', chartType: 'SuperTables' highlighted on line 15, and displayKey: 'export' on line 82.

The result is an element with "Generate" export button:

  • Specify the file name for your export in the text box.
  • There's a bar to show export progress.
  • The questions included are listed out.
Supertables export view displaying a text input field with 'Supertables.xlsx' as the filename, a blue 'Generate' button, a progress bar, and a grid layout showing placeholders for nine questions (Q1, Q2v1, Q3, Q4v1, Q8, Q9v1, Q14, Q15, Q16) arranged in a 3x3 grid.

Add the crosstab banners you want to export with the table. Press the generate button, the progress bar underneath will alert you when the Excel is ready to download.

Supertables export view with 'supertables by S1' and Country banners at the top, followed by filename field 'Supertables.xlsx', Generate button, progress bar, and a grid of nine small crosstab preview tables (Q1, Q2v1, Q3, Q4v1, Q8, Q9v1, Q14, Q15, Q16) showing S1 and Country column headers.

Export options

Open the “More chart options” dialog. The quickest way is to select the export element, then press SHIFT E + P on your keyboard.

Export properties dialog showing configuration for 'SuperTables_export.xlsx' with settings for continueSupernests, hideRedundantBasis, maxLabelLength (20), preferredWidthColumn (60), preferredWidthLabel (300), percentFormat (0%;0%;-@), printMaxColumns (24), printMaxRows (60), printRepeatColumns (5), and printRepeatRows (6).

Continue stats superscript letters 

  • Select continueSuperscripts if you don't want the first column of each banner to restart superscript letters at "a".

Show N size row under each question

  • Deselect the hidRedundantBasis if you want to show an N size row under each question.
  • Keep the option selected if you want to hide the N size row at each question when it is identical to the overall N size row of the export.

Hide/show Significance tests

  • Select showSignficance to see pairwise stats markers in the export.

Resulting Excel export

The export will be a stylized Excel that looks similar to the look and feel of the online SuperTable.

Excel file 'Supertables.xlsx' displaying formatted crosstab tables with blue column headers (OVERALL, S1, Country) spanning multiple countries, showing three questions about Condition X patients with percentage data organized in rows including age ranges and product therapies.

The export reflects the [NA] state of the export element. Missing values will be hidden if the export element has showMissing set to "No" in edit properties. 

The export also reflects the state of the global filters. If you save the project with a “complete” global filter, the export will show completes only.