Skip to main content

Calculate a table

POST 

/api/v2/table/:surveyCode

Calculates a table on the fly from the definition in the request body.

You describe its dimensions, axes, variables and statistics, and the response returns the calculated figures together with the resolved structure. No saved element is needed, and nothing is stored.

Use it to build cross-tabulations from your own code, to recalculate a saved tabulation with changes (get its definition with GET /api/v2/workspace/{workspaceId}/element/{elementId} and post its table field), or to calculate the other supported table types. To read a saved tabulation as it is, use GET /api/v2/workspace/{workspaceId}/survey/{surveyCode}/tabulation/{elementId}. Reach tables (REACH_TABLE, REACH_DUPLICATION and REACH_TREND) can be calculated here too, with the same body as POST /api/v2/reach/{surveyCode}, targetMedia and diaryFilters included.

Before you call it​

ValueWhere to get it
surveyCodecode in GET /api/v2/users/surveys
Variables and their categories (VAR)GET /api/v2/survey/{surveyCode}/variables, or the code of nodes from POST /api/v2/survey/{surveyCode}/tree/query
User variables (CUSTOM_VAR)GET /api/v2/workspace/{workspaceId}/survey/{surveyCode}/user-vars
Saved targets (TARGET_VAR, target.id)GET /api/v2/workspace/{workspaceId}/survey/{surveyCode}/targets
Carriers (CARRIERS axes)GET /api/v2/survey/{surveyCode}/carriers
StatisticsGET /api/v2/statistics, with the tableType of your table; for the STATISTICS axis of a TREND, add axis=STATISTICS. For a Reach table, GET /api/v2/survey/{surveyCode}/reach-capabilities
Weeks and weightings (weeks, weight)GET /api/v2/survey/{surveyCode}/weeks-and-weights
TGL expressions (filterTgl)The TGL guide

Table model​

The body has up to three dimensions, identified by id: ROWS, COLUMNS and PLANES, in upper case (lower case is read as upper case; any other id returns AE_ERR_401). Each dimension holds one or more typed axes; axisType selects the kind of axis and the fields it accepts:

axisTypeHoldsTypical use
VARIABLESvariables: survey variables (VAR), user variables (CUSTOM_VAR), targets (TARGET_VAR) and totals (TOTAL)Rows, columns or planes
STATISTICSstatistics to calculatePLANES or COLUMNS
CATEGORIEScategories of a split VARIABLES axis, which it references with parentIdSplit tables
SOURCES, DIFFERENCESSurvey waves to compare, and difference statistics between themTREND and REACH_TREND tables
CARRIERScarriers: media carriers (CARRIER), joints of carriers (CARRIER_JOINT) and the total of all media (TOTAL_MEDIA)BREAKDOWN_CARRIERS, DUPLICATIONS and Reach tables
CUSTOM_DIARY_EDGE, TIME_DIARY_EDGEDiary edgesReach tables

MEDIA and MEDIA_PLANS appear only in responses. The axis id is optional: when it is omitted, the lowest unused positive integer is assigned. Give each axis a unique id when another axis or your own code refers to it.

Rules​

  • tableType defaults to TABULATION. The supported values are TABULATION, TREND, BREAKDOWN_CARRIERS, DUPLICATIONS, REACH_TABLE, REACH_DUPLICATION and REACH_TREND. Each one requires an application module: Tabulation for TABULATION; Study for TREND, BREAKDOWN_CARRIERS and DUPLICATIONS; Reach for the Reach types REACH_TABLE, REACH_DUPLICATION and REACH_TREND. Without the module the request is rejected with 403 (AE_ERR_408) before the axes are checked. BREAKDOWN_SUPPORTS, the name used by earlier versions, is a deprecated alias of BREAKDOWN_CARRIERS.
  • A STATISTICS axis is required.
  • For a categorical variable, omit categories to use all of them, or list the ones you want: CATEGORY for a category of the variable, or JOINT with ids to merge several categories into one.
  • selected picks the slice in PLANES. When no item of a PLANES axis is selected, the first valid one is used. When several items of one PLANES axis are selected, the response has one matrix per selected item; to select several categories of a plane variable, mark the variable itself as selected too. Outside PLANES, an axis can have at most one selected item. A SOURCES axis has its own rule: see Survey waves.
  • A TARGET_VAR variable takes a saved target (id) or a TGL expression (filterTgl). If both are sent, filterTgl is used and id is ignored.
  • The CUSTOM_CAT categories of a CUSTOM_VAR with subtype SINGLE or MULTIPLE accept tgl (the TGL condition of the category) and hidden, as GET /api/v2/workspace/{workspaceId}/element/{elementId} returns them with resolve=TGL. When every CUSTOM_CAT category of the variable has a tgl, the variable is calculated from them and is not looked up by id, so the key's user does not need to see it. Otherwise it is looked up by id, with no error. tgl or hidden: true anywhere else returns AE_ERR_416.
  • target filters the whole table by a saved target (id) or by a TGL expression (filterTgl). If both are sent, filterTgl is used; a target with neither returns AE_ERR_405.
  • targetBase is the population base, for any tableType: it fixes the reference universe of the percentages. It takes a saved target (id) or a TGL expression (filterTgl) with the same rule as target. A base id is not looked up beforehand: if it cannot be resolved, the calculation engine returns its error, except in a TREND table, which leaves the base out and reports it in diagnostics. Without it, the percentages are calculated over the whole universe of the survey.
  • dataType, locale and decimals set the format of the cells (see Response).
  • extraParams passes advanced switches to the calculation engine; only showBases and, in a trend, trendCategoriesMode are documented. A switch with an invalid value returns AE_ERR_400.
  • Unknown fields at the top level, in weight, in dimensions, in axes, in split categories, in the survey waves of a SOURCES axis and in their weight are rejected with AE_ERR_411. Unknown fields inside variables, categories, statistics, target and targetBase are ignored, so check the spelling (for example, categories, not items, and tgl, not TGL: a misspelled tgl is dropped and the variable is looked up by id).
  • The dimensions of a response can be posted back to recalculate the same table. PLANES then carries only the slices that were returned.

Weeks and weighting of the table​

  • weeks (a list of week ids YYYY/WW) and weight (a weighting) set the period and the weighting of the whole table. Get the valid values from GET /api/v2/survey/{surveyCode}/weeks-and-weights: an item of its weights can be sent as weight as it is, and only its id is read. A weight without id returns AE_ERR_412.
  • Only TABULATION and REACH_TABLE take them. With any other tableType, REACH_TREND included, they return AE_ERR_400 and the message names the field: a TREND or REACH_TREND takes them in each survey wave.
  • They are checked against the survey before calculating. A week or a weighting the survey does not have, or any value in a survey without weeks or weightings, returns AE_ERR_400 and the message names the value. An empty weeks is the same as not sending it, and a week sent twice returns AE_ERR_400.
  • With several weeks, the weighted figures are the mean of the weeks; Sample and the statistics calculated on the sample come from the latest of them.
  • Without them, the platform uses the first week of the survey and its default weighting, the one marked isDefault.
  • In a REACH_TABLE they are validated but do not change the Reach figures: the Reach engine does not receive them.
  • The labels of the weightings are the survey's own and are not translated.

Survey waves (SOURCES axis)​

A TREND or REACH_TREND compares survey waves. Each item of sources is one wave:

FieldUse
idCode of the survey, as returned in code by GET /api/v2/users/surveys.
labelName to show. Ignored when sent; returned in responses.
selectedThe wave shown when the axis is in PLANES.
weeksWeeks of the wave, with the ids of GET /api/v2/survey/{surveyCode}/weeks-and-weights.
weightWeighting of the wave, an item of weights of the same endpoint. Only its id is read, and it is required.
  • A survey wave is strict: an unknown field in it or in its weight returns AE_ERR_411, and a weight without id returns AE_ERR_412. An empty weeks is the same as not sending it, and a week sent twice returns AE_ERR_400.
  • The API does not check the weeks and the weighting of a wave: the calculation engine does. A wave of a survey with weeks that sends none, or one the survey does not have, or a weighting the survey does not have, is discarded with a TR_ERR_107 diagnostic of level WARNING, and the other waves are calculated. With no valid wave left, the request returns TR_ERR_105. A survey without weeks ignores weeks, and a survey without weightings ignores weight. A wave without weight keeps the behaviour of the engine: the API adds no weighting.
  • In PLANES, at most one wave can be selected; with more, the request returns AE_ERR_404, naming the axis and the waves. Without any, the first wave is shown. In ROWS or COLUMNS, selected is accepted on any number of waves and does not change the figures.
  • In a REACH_TREND, the weeks and the weighting of a wave decide whether the wave is calculated, but they do not change the figures: two waves of the same survey that differ only in them give identical columns.
  • AbsFirst and PrcFirst compare each wave with the first wave of the axis, and AbsPrev and PrcPrev with the wave in the previous position. The first wave shows its own value in them, and a wave that follows one without values has no AbsPrev or PrcPrev.
  • The sources of a response carry selected, weeks and weight when they have a value, so a response or a saved element can be sent again.

Trend tables (TREND, REACH_TREND)​

  • A TREND needs a SOURCES axis (without it, TR_ERR_101) and a DIFFERENCES axis (without it, TR_ERR_103). A REACH_TREND also needs a CARRIERS axis; without one of the three it is rejected with AE_ERR_412 before calculating. See POST /api/v2/reach/{surveyCode} for the other rules of a REACH_TREND.
  • The DIFFERENCES axis takes the ids of GET /api/v2/statistics?tableType=TREND. The STATISTICS axis of a TREND takes the ids of GET /api/v2/statistics?tableType=TREND&axis=STATISTICS.
  • extraParams.trendCategoriesMode chooses the categories that enter each wave. The base categories always enter:
ValueCategories that enter
ONLY_ACTIVE_SOURCE_CATEGORIESThe ones valid in the survey of the path. The default.
ALLThe ones valid in at least one wave.
VALID_IN_ALL_SOURCESThe ones valid in every wave.
  • Any other value of trendCategoriesMode returns TR_ERR_106. The key has no effect outside a TREND or a REACH_TREND, and a saved element returns it in extraParams.

Reach fields​

  • targetMedia and diaryFilters mean what they mean in POST /api/v2/reach/{surveyCode}, and apply with a Reach tableType (REACH_TABLE, REACH_DUPLICATION or REACH_TREND). With any other tableType they return AE_ERR_400 and the message names the field. An empty list is the same as not sending it.
  • A Reach table read with GET /api/v2/workspace/{workspaceId}/element/{elementId} can be sent unchanged here, to the /html variant or to POST /api/v2/reach/{surveyCode}.

Deprecated names​

Earlier versions used other names for carriers. They are still accepted in requests and are never returned: SUPPORTS for the axisType CARRIERS, supports for the carriers property, and SUPPORT, SUPPORT_JOINT and TOTAL_MEDIA_SUPPORT for the carrier type CARRIER, CARRIER_JOINT and TOTAL_MEDIA. Use the current names.

Response​

matrixList holds one matrix per calculated plane item. Identify each matrix by its items (one entry per PLANES axis, with the axis id and the item of the slice), never by its position. matrixList[i].table[r][c] is the value for the r-th item of ROWS and the c-th item of COLUMNS, in the order of dimensions. For example, in the response to the minimal tabulation, table[0][1] is the value for SEX.D1 (Male) by EDA1.D2 (20 to 24). Cells are numbers (dataType NUMBER, the default), formatted strings (STRING) or { "value", "formatted" } objects (FORMATTED_CELL). With dataType NUMBER, a cell that cannot be calculated (for example, when the target selects nobody) is returned as the string "NaN", not as null. In dimensions, PLANES lists only the selected items. diagnostics appears only when the calculation has something to report, for example a survey wave of a trend that was discarded, or a user variable or target that could not be resolved in a wave of a trend; its level is the worst level of its diagnostics. In a table that is not a TREND, a user variable or a target that does not exist is an error (CV_ERR_109, TG_ERR_113, TG_ERR_115), not a diagnostic. Labels come in the language of the user.

Errors​

errorMessage is meant for people and may be in the user's language. Base your error handling on errorCode.

StatuserrorCodeWhen
400AE_ERR_401Invalid value of an enumerated field: tableType, dataType, the id of a dimension, the type or subtype of a variable, or the type of a category, including the categories of a CATEGORIES axis. The message lists the valid values.
400AE_ERR_400decimals is negative, or an extraParams switch has an invalid value. weeks or weight with a tableType other than TABULATION or REACH_TABLE, or a week or a weighting the survey does not have. targetMedia or diaryFilters with a tableType that is not Reach. A value of the wrong JSON type; the message names the field. In a TIME_DIARY_EDGE axis without ranges, a time.step that is not positive, or a time.from or time.to that is not HH:mm.
400AE_ERR_402(REACH_TREND) A statistic outside the catalog of its axis: in STATISTICS, the statistics of a REACH_TABLE; in DIFFERENCES, the six differences of a trend.
400AE_ERR_404More than one selected item or category in an axis outside PLANES, or more than one selected survey wave in a SOURCES axis in PLANES.
400AE_ERR_405target or targetBase has neither a non-blank id nor a non-blank filterTgl.
400AE_ERR_409, AE_ERR_410axisType is missing or unknown, or the axis type is valid only in responses, or the CARRIERS axis of a REACH_TREND is in PLANES, or a SOURCES or DIFFERENCES axis comes in a table that is not a TREND or a REACH_TREND.
400AE_ERR_411A field that the object does not accept, such as carriers in a VARIABLES axis, or an unknown field in a survey wave or in its weight.
400AE_ERR_412A required value is not sent: the list of an axis (variables, statistics, carriers, categories), parentId, the subtype, id or filterTgl of a variable, or the id of a weight. A REACH_TREND without its SOURCES, DIFFERENCES or CARRIERS axis. In a TIME_DIARY_EDGE axis without ranges, time.step, time.from or time.to.
400AE_ERR_413dimensions is absent or empty, or a dimension has no axes. An empty or null body too.
400AE_ERR_414A CATEGORIES axis whose parentId is not a split VARIABLES axis of the request.
400AE_ERR_416Unsupported variable: subtype TEXT, a CUSTOM_VAR with LOGIC, TOTAL or WEIGHTED, or tgl or hidden: true in a category that is not a CUSTOM_CAT of a CUSTOM_VAR with SINGLE or MULTIPLE.
400T_ERR_104, T_ERR_106, VA_ERR_101, RC_ERR_100Rejected by the calculation engine: no STATISTICS axis, a statistic that is not valid for the table type, an unknown variable, or a Reach table on a survey without Reach data.
400TB_ERR_104The table has no variable to calculate: for example, its only variable is a TOTAL, or it has only a STATISTICS axis in PLANES.
400VA_ERR_102A category that the variable does not have, or a JOINT category without ids.
400CV_ERR_109A CUSTOM_VAR that cannot be resolved: it does not exist, is empty or was deleted. A TREND table reports it in diagnostics instead.
400TG_ERR_113target or targetBase has an id that does not identify a saved target. A TREND table leaves it out and reports it in diagnostics instead.
400TG_ERR_115A TARGET_VAR variable has an id that does not identify a saved target.
400TGL_ERR_101A TGL expression of the request (filterTgl of target, targetBase or a TARGET_VAR, or tgl of a CUSTOM_CAT) uses a variable or category that the survey does not have.
400SP_ERR_101A carrier id does not exist in the survey.
400RC_ERR_100(REACH_TREND) The survey of the path does not support Reach. The API checks it before calculating, with the code and the message of a REACH_TABLE.
400RC_ERR_106, RC_ERR_107, RC_ERR_108(REACH_TREND) The survey of the path does not have the items of a CUSTOM_DIARY_EDGE or TIME_DIARY_EDGE axis, or the target media. It fails at once, as in a REACH_TABLE.
400TR_ERR_101, TR_ERR_103(TREND) No SOURCES axis, or no DIFFERENCES axis.
400TR_ERR_105(TREND, REACH_TREND) No survey wave is valid: none is available to the user, or their weeks or weighting are not valid.
400TR_ERR_106(TREND, REACH_TREND) trendCategoriesMode is not a valid value. A value that is not text is rejected by the API before calculating, with the same code.
400TR_ERR_109(TREND, REACH_TREND) No survey wave is left after checking the variables of the table and, in a REACH_TREND, the Reach checks of each wave.
400SO_ERR_201The survey is disabled or being loaded.
400E400The body is not valid JSON.
401E401The X-API-Key header is missing or empty.
401SO_ERR_203The survey is not in your profile or does not exist.
403AE_ERR_408The user's profile does not include the module required by the table type: Reach for REACH_TREND.
422TGL_INVALIDA TGL expression of the request cannot be used: it does not parse, or it uses a variable without a category or a condition (for example SEX).
500GEN_ERR_500Unexpected error.

TR_ERR_107 is not an error: a discarded survey wave is a WARNING diagnostic of a 200 response.

Request​

Responses​

The table calculated. matrixList holds one matrix per calculated plane item.