Multitables 🔗

If a “table” is a rows-variable crosstabbed with a columns-variable, a multitable is a rows-variable crosstabbed with multiple columns-variables. The crosstab for each columns-variable is “tiled” side-by-side with the other columns-variable crosstabs such that a single contiguous visual table results. Strictly speaking, a multitable is simply the list of columns-variables to be used, but a multitable can only be rendered when a rows-variable is chosen to complete the set of crosstabs.

This analysis format allows the relationship between multiple variables and the rows-variable to be conveniently compared, side-by-side.

A tabbook is an (Excel) export format where one or more rows-variables are selected to be crosstabbed agains the set of columns-variables defined in a multitable. Each resulting table is displayed (by default) in a separate “tab” (worksheet) of the Excel workbook, producing a (work) book of cross-tabs, hence the name tabbook.

Catalog 🔗

/datasets/{dataset_id}/multitables/

GET 🔗

{
    "element": "shoji:catalog",
    "self": "/api/datasets/123/multitables/",
    "specification": "/api/specifications/multitables/",
    "description": "List of multitable definitions for this dataset",
    "index": {
        "/api/datasets/123/multitables/7ab1e/": {
            "is_public": false,
            "owner_id": "/api/users/b055/",
            "name": "Basic Demographics",
            "id": "7ab1e",
            "team": "/api/teams/56789/"
        }
    }
}

GET on this resource returns a Shoji Catalog with the collection of multitables available to the current user on this Dataset.

This index contains two kinds of multitables: those that belong to the dataset, denoted by the is_public tuple attribute; and those that belong to the current user. Personal multitables are those created by the authenticated user, and they cannot be accessed by other users. Dataset multitables are available to all users who are authorized to view the dataset. Only the current dataset editor can create a public multitable.

POST 🔗

POST a Shoji Entity to this catalog to create a new multitable definition. Entities must include a name and template; the template must contain a series of objects with a query and optionally transform (see Transforming analyses for presentation). If omitted, is_public defaults to false. In similar fashion, team will default to null unless a specific team URL is provided.

A successful POST yields a 201 (Created) response with a Location header containing the URL of the newly created multitable.

All users with access to the dataset can create personal multitable definitions; however, only the current dataset editor can create public multitables (is_public: true) which everyone with access to the dataset can see. Attempting to create a public multitable when not the current dataset editor results in a 403 (Forbidden) response.

Copying Multitables between datasets 🔗

A multitable can be copied from one dataset to another when permissions allow.

The copy operation requires that all variables present in the template of the origin multitable exist on the target dataset and have the same type.

To copy a multitable, POST a shoji entity to the catalog indicating the URL of the source multitable:

{
    "element": "shoji:entity",
    "body": {
        "name": "Name of my copy",
        "multitable": "/api/datasets/123/multitables/7ab1e/"
    }
}

As shown in the example, it is possible to assign a new name to the copy. By default, the copy is private unless "is_public": "true" is specified in the body. Only the current dataset editor can make a public copy.

PATCH 🔗

There are no elements of the catalog that can be changed via PATCH.

Entity 🔗

/datasets/{dataset_id}/multitables/{multitable_id}/

GET 🔗

GET on this resource returns a Shoji entity containing the requested multitable definition.

{
    "element": "shoji:entity",
    "self": "datasets/123/multitables/7ab1e/",
    "views": {
        "tabbook": "/datasets/123/multitables/7ab1e/tabbook/"
    },
    "specification": "https://app.crunch.io/api/specifications/multitables/",
    "description": "Detail information for one multitable definition",
    "body": {
        "name": "Basic Demographics",
        "user": "/api/users/b055/",
        "template": [
            {
                "query": [
                    {
                        "variable": "/datasets/123/variables/abc/"
                    }
                ]
            },
            {
                "query": [
                    {
                        "variable": "/datasets/123/variables/def/"
                    }
                ]
            }
        ],
        "is_public": false,
        "id": "7ab1e",
        "team": "/api/teams/56789/"
    }
}

PATCH 🔗

PATCH the entity to edit its name, template, team or is_public attributes. A successful PATCH request yields a 204 (No Content) status. As with POSTing new entities to the catalog, only the dataset’s current editor can alter is_public.

The template attribute must contain a valid multitable definition.

Tab Book Exports 🔗

Exporting a multitable entity produces a “tab-book”, by default an Excel (.xlsx) workbook containing each variable in the dataset cross-tabbed with the specified multitable.

/datasets/{dataset_id}/multitables/{multitable_id}/export/

POST 🔗

A successful POST request to /datasets/{dataset_id}/multitables/{multitable_id}/export/ returns a download location to which the exporter will write the generated file (this may take some time for large datasets).

The server returns a 202 (Accepted) response to a successful POST, indicating the export job has started. The response includes the download URL in its Location header. The response body contains a progress URL which can be accessed (as often as necessary) to monitor the export job status.

Clients should note the download URL, monitor progress, and when complete, GET the download location. See Progress for details.

Requesting the same job, if still in progress, will return the same 202 response with the same progress URL; the job is not restarted. If the export is finished, the server responds with 302 (Found), redirecting to the download location.

If there have been changes on the dataset attributes, a new tab book will be generated regardless of the status of any other pending exports.

POST /api/datasets/a598c7/multitables/7ab1e/export/ HTTP/1.1

HTTP/1.1 202 Accepted
Location: https://s3-url/filename.xlsx
{
    "element": "shoji:view",
    "self": "https://app.crunch.io/api/datasets/a598c7/multitables/{id}/export/",
    "value": "https://app.crunch.io/api/progress/5be83a/"
}

Alternatively, you can request a JSON output for your tab book by adding an appropriate Accept request header.

POST /api/datasets/a598c7/multitables/7ab1e/export/ HTTP/1.1
Accept: application/json
{
    "meta": {
        "analyses": [
            {
                "filters": null,
                "format": {
                    "decimal_places": 0,
                    "pval_colors": False,
                    "show_empty": False,
                },
                "measures": ["col_percent"],
                "name": "x",
                "weight": "z"
            },
            ... (one entry for each tab of tab-book)
        ],
        "dataset": {
            "name": "weighted_simple_alltypes",
            "notes": ""
        },
        "doc_layout": {
            "toc": False,
            "variable_sheets": "many_sheets",
        },
        "template": [
            {
                "query": [
                    {
                        "args": [
                            {
                                "variable": "000002"
                            }
                        ],
                        "function": "bin"
                    }
                ]
            },
            {
                "query": [
                    {
                        "args": [
                            {
                                "variable": "00000a"
                            },
                            {
                                "value": null
                            }
                        ],
                        "function": "rollup"
                    }
                ]
            }
        ]
    },
    "sheets": [
        {
            "result": [
                {
                    "result": {
                        "counts": [
                            1,
                            1,
                            1,
                            1,
                            1,
                            1,
                            0
                        ],
                        "dimensions": [
                            {
                                "derived": false,
                                "references": {
                                    "alias": "x",
                                    "description": "Numeric variable with value labels",
                                    "name": "x"
                                },
                                "type": {
                                    "categories": [
                                        {
                                            "id": 1,
                                            "missing": false,
                                            "name": "red",
                                            "numeric_value": 1
                                        },
                                        {
                                            "id": 2,
                                            "missing": false,
                                            "name": "green",
                                            "numeric_value": 2
                                        },
                                        {
                                            "id": 3,
                                            "missing": false,
                                            "name": "blue",
                                            "numeric_value": 3
                                        },
                                        {
                                            "id": 4,
                                            "missing": false,
                                            "name": "4",
                                            "numeric_value": 4
                                        },
                                        {
                                            "id": 8,
                                            "missing": true,
                                            "name": "8",
                                            "numeric_value": 8
                                        },
                                        {
                                            "id": 9,
                                            "missing": false,
                                            "name": "9",
                                            "numeric_value": 9
                                        },
                                        {
                                            "id": -1,
                                            "missing": true,
                                            "name": "No Data",
                                            "numeric_value": null
                                        }
                                    ],
                                    "class": "categorical",
                                    "ordinal": false
                                }
                            }
                        ],
                        "measures": {
                            "count": {
                                "data": [
                                    0.0,
                                    0.0,
                                    1.234,
                                    0.0,
                                    3.14159,
                                    0.0,
                                    0.0
                                ],
                                "metadata": {
                                    "derived": true,
                                    "references": {},
                                    "type": {
                                        "class": "numeric",
                                        "integer": false,
                                        "missing_reasons": {
                                            "No Data": -1
                                        },
                                        "missing_rules": {}
                                    }
                                },
                                "n_missing": 5
                            }
                        },
                        "n": 6
                    }
                },
                {
                    "result": {
                        "counts": [
                            1,
                            0,
                            0,
                            0,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            1,
                            1,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0,
                            0
                        ],
                        "dimensions": [
                            {
                                "derived": false,
                                "references": {
                                    "alias": "x",
                                    "description": "Numeric variable with value labels",
                                    "name": "x"
                                },
                                "type": {
                                    "categories": [
                                        {
                                            "id": 1,
                                            "missing": false,
                                            "name": "red",
                                            "numeric_value": 1
                                        },
                                        {
                                            "id": 2,
                                            "missing": false,
                                            "name": "green",
                                            "numeric_value": 2
                                        },
                                        {
                                            "id": 3,
                                            "missing": false,
                                            "name": "blue",
                                            "numeric_value": 3
                                        },
                                        {
                                            "id": 4,
                                            "missing": false,
                                            "name": "4",
                                            "numeric_value": 4
                                        },
                                        {
                                            "id": 8,
                                            "missing": true,
                                            "name": "8",
                                            "numeric_value": 8
                                        },
                                        {
                                            "id": 9,
                                            "missing": false,
                                            "name": "9",
                                            "numeric_value": 9
                                        },
                                        {
                                            "id": -1,
                                            "missing": true,
                                            "name": "No Data",
                                            "numeric_value": null
                                        }
                                    ],
                                    "class": "categorical",
                                    "ordinal": false
                                }
                            },
                            {
                                "derived": true,
                                "references": {
                                    "alias": "z",
                                    "description": "Numberic variable with missing value range",
                                    "name": "z"
                                },
                                "type": {
                                    "class": "enum",
                                    "elements": [
                                        {
                                            "id": -1,
                                            "missing": true,
                                            "value": {
                                                "?": -1
                                            }
                                        },
                                        {
                                            "id": 1,
                                            "missing": false,
                                            "value": [
                                                1.0,
                                                1.5
                                            ]
                                        },
                                        {
                                            "id": 2,
                                            "missing": false,
                                            "value": [
                                                1.5,
                                                2.0
                                            ]
                                        },
                                        {
                                            "id": 3,
                                            "missing": false,
                                            "value": [
                                                2.0,
                                                2.5
                                            ]
                                        },
                                        {
                                            "id": 4,
                                            "missing": false,
                                            "value": [
                                                2.5,
                                                3.0
                                            ]
                                        },
                                        {
                                            "id": 5,
                                            "missing": false,
                                            "value": [
                                                3.0,
                                                3.5
                                            ]
                                        }
                                    ],
                                    "subtype": {
                                        "class": "numeric",
                                        "missing_reasons": {
                                            "No Data": -1
                                        },
                                        "missing_rules": {}
                                    }
                                }
                            }
                        ],
                        "measures": {
                            "count": {
                                "data": [
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    1.234,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    3.14159,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0
                                ],
                                "metadata": {
                                    "derived": true,
                                    "references": {},
                                    "type": {
                                        "class": "numeric",
                                        "integer": false,
                                        "missing_reasons": {
                                            "No Data": -1
                                        },
                                        "missing_rules": {}
                                    }
                                },
                                "n_missing": 5
                            }
                        },
                        "n": 6
                    }
                },
                {
                    "result": {
                        "counts": [
                            1,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0,
                            1,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0,
                            1,
                            0,
                            0,
                            1,
                            0,
                            0,
                            0
                        ],
                        "dimensions": [
                            {
                                "derived": false,
                                "references": {
                                    "alias": "x",
                                    "description": "Numeric variable with value labels",
                                    "name": "x"
                                },
                                "type": {
                                    "categories": [
                                        {
                                            "id": 1,
                                            "missing": false,
                                            "name": "red",
                                            "numeric_value": 1
                                        },
                                        {
                                            "id": 2,
                                            "missing": false,
                                            "name": "green",
                                            "numeric_value": 2
                                        },
                                        {
                                            "id": 3,
                                            "missing": false,
                                            "name": "blue",
                                            "numeric_value": 3
                                        },
                                        {
                                            "id": 4,
                                            "missing": false,
                                            "name": "4",
                                            "numeric_value": 4
                                        },
                                        {
                                            "id": 8,
                                            "missing": true,
                                            "name": "8",
                                            "numeric_value": 8
                                        },
                                        {
                                            "id": 9,
                                            "missing": false,
                                            "name": "9",
                                            "numeric_value": 9
                                        },
                                        {
                                            "id": -1,
                                            "missing": true,
                                            "name": "No Data",
                                            "numeric_value": null
                                        }
                                    ],
                                    "class": "categorical",
                                    "ordinal": false
                                }
                            },
                            {
                                "derived": true,
                                "references": {
                                    "alias": "date",
                                    "description": null,
                                    "name": "date"
                                },
                                "type": {
                                    "class": "enum",
                                    "elements": [
                                        {
                                            "id": 0,
                                            "missing": false,
                                            "value": "2014-11"
                                        },
                                        {
                                            "id": 1,
                                            "missing": false,
                                            "value": "2014-12"
                                        },
                                        {
                                            "id": 2,
                                            "missing": false,
                                            "value": "2015-01"
                                        }
                                    ],
                                    "subtype": {
                                        "class": "datetime",
                                        "missing_reasons": {
                                            "No Data": -1
                                        },
                                        "missing_rules": {},
                                        "resolution": "M"
                                    }
                                }
                            }
                        ],
                        "measures": {
                            "count": {
                                "data": [
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    1.234,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    3.14159,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0,
                                    0.0
                                ],
                                "metadata": {
                                    "derived": true,
                                    "references": {},
                                    "type": {
                                        "class": "numeric",
                                        "integer": false,
                                        "missing_reasons": {
                                            "No Data": -1
                                        },
                                        "missing_rules": {}
                                    }
                                },
                                "n_missing": 5
                            }
                        },
                        "n": 6
                    }
                }
            ]
        },
        ... (one entry for each sheet)
    ]
}

POST body parameters 🔗

At the top level, you can choose one or more filters for the data, and which variables to include in the tab book. The choice of variables may be expressed either in a machine-friendly form (a where map) or in the more concise variables. Note that variables can take folder ids as a succinct specifier for groups of variables.


filter 🔗

Type Default Description
object null Filter by Crunch Expression. Variables used in the filter should be fully-expressed urls.

Example

"filter": [
  {
    "filter": "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/filters/5f14133582f34b8b85b408830f4b4a9b/"
  }
]

where 🔗

Type Default Description
object null Crunch Expression signifying which variables to use

Example

"where": {
  "function": "select",
  "args": [
    {
      "map": {
        "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/variables/000004/": {
          "variable": "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/variables/000004/"
        },
        "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/variables/000003/": {
          "variable": "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/variables/000003/"
        }
      }
    }
  ]
}

variables 🔗

Type Default Description
array null List of variables or folder urls to include. Use this as a simpler way to select the variables to include instead of building a where expression. A folder included in this list will include all variables in that folder and those in all its subfolders.

Example

"variables": [
   "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/variables/000004/",
   "https://app.crunch.io/api/datasets/45fc0d5ca0a945dab7d05444efa3310a/folders/abcdef/"
]

weight 🔗

Type Default Description
URL null The weight variable to be used when generating the tabbook. If weight is omitted from the request, the currently selected weight is used. If “null” is provided, the generated tabbooks will be unweighted.

options 🔗

Type Default Description
object {} Specifies tabbook layout and data display options.

Example

"options": {
  "doc_layout": {
    "toc": false,
    "variable_sheets": "many_sheets
  },
  "fields": [
    "col_percent",
    "z_score",
    "p_value",
    "count_weighted"
  ],
  "format": {
    "pval_colors": false,
    "decimal_places": 1,
    "show_empty": false
  },
  "page_layout": {
    "rows": {
      "alias": false,
      "description": true,
      "notes": false
    },
    "columns": {
      "alias": false,
      "description": false,
      "notes": false,
      "merge_ranges": true
    }
  }
}

Each of these top-level option containers is further described in the tables that follow:

Name Type Default Description
doc_layout object {} Define how overall document should be organized.
fields array [“col_percent”] One or more measures to be displayed in the body of the tab-book. Multiple measures are displayed side-by-side for each body column.
format object {} Define how individual value cells should be displayed.
page_layout object {} Define how individual pages of the document (e.g., worksheets in Excel format) should be displayed.

doc_layout

Options controlling overall organization of exported tab-book (workbook).

Name Type Default Description
toc boolean false Generate a Table of Contents for the tabbook, to appear as the first worksheet of the Excel workbook.
variable_sheets string “many_sheets” One of “many_sheets” or “one_sheet”. The value “many_sheets” causes each tab to appear on a separate worksheet. The value “one_sheet” causes each tab to be placed on the same worksheet, in a “stack”.

fields

An array of one or more strings, each specifying a measure to be displayed in the body of each tab. Multiple values are placed side-by-side in successive spreadsheet columns in the order specified.

Name Description
col_percent Proportion, expressed as a percentage, using the column total as its base.
row_percent Proportion, expressed as a percentage, using the row total as its base.
table_percent Proportion, expressed as a percentage, using the table total as its base.
z_score The standardized residual value of each cell from the expected value if rows and columns were independently distributed.
p_value The two-tailed probability of Z, assuming Z has an approximate standard normal distribution.
count_weighted The weighted count in each cell
count_unweighted The unweighted count in each cell
col_index The index percentage with respect to the column marginal percentage.
population The estimated population count if a dataset has a defined target population.
col_base_weighted Total weighted N for each cell, conditioned on the categories of the column variable. (The denominator of col_percent.)
col_base_unweighted Unweighted N for each cell with respect to the column.
row_base_weighted Total weighted N for each category of the row variable. (Denominator of row_percent.)
row_base_unweighted Unweighted N with respect to the row.
table_base_weighted Total weighted N for table percentages.
table_base_unweighted Unweighted N for each cell with respect to the total N in both the row and column.

format

Options controlling the display of individual cells in a tab.

Name Type Default Description
decimal_places number 0 number of decimal places to display
pval_colors boolean false indicate p-value for cell by lighter or darker green or red shading
show_empty boolean false display rows and columns having no data

page_layout

Options controlling the display of an individual tab “table”.

Name Type Default Description
alias boolean false Display variable alias. May appear in both the rows and columns container objects.
columns object {} container for column-related options
description boolean false Display row and/or column description. May appear in both the rows and columns container objects.
measure_layout string “wide” One of “wide” or “long”. Only operative for a multi-measure tabbook. Controls whether multiple measures are displayed side-by-side (wide) or stacked (long).
merge_ranges boolean true Display each columns superheading as a merged cell. Turning this option off may ease machine parsing of the Excel file.
notes boolean false Display variable notes in sheet header. May appear in both the rows and columns container objects.
rows object {} container for row-related options