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
| Value | Where to get it |
|---|---|
surveyCode | code 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 |
| Statistics | GET /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:
axisType | Holds | Typical use |
|---|---|---|
VARIABLES | variables: survey variables (VAR), user variables (CUSTOM_VAR), targets (TARGET_VAR) and totals (TOTAL) | Rows, columns or planes |
STATISTICS | statistics to calculate | PLANES or COLUMNS |
CATEGORIES | categories of a split VARIABLES axis, which it references with parentId | Split tables |
SOURCES, DIFFERENCES | Survey waves to compare, and difference statistics between them | TREND and REACH_TREND tables |
CARRIERS | carriers: 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_EDGE | Diary edges | Reach 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
tableTypedefaults toTABULATION. The supported values areTABULATION,TREND,BREAKDOWN_CARRIERS,DUPLICATIONS,REACH_TABLE,REACH_DUPLICATIONandREACH_TREND. Each one requires an application module: Tabulation forTABULATION; Study forTREND,BREAKDOWN_CARRIERSandDUPLICATIONS; Reach for the Reach typesREACH_TABLE,REACH_DUPLICATIONandREACH_TREND. Without the module the request is rejected with403(AE_ERR_408) before the axes are checked.BREAKDOWN_SUPPORTS, the name used by earlier versions, is a deprecated alias ofBREAKDOWN_CARRIERS.- A
STATISTICSaxis is required. - For a categorical variable, omit
categoriesto use all of them, or list the ones you want:CATEGORYfor a category of the variable, orJOINTwithidsto merge several categories into one. selectedpicks the slice inPLANES. When no item of aPLANESaxis is selected, the first valid one is used. When several items of onePLANESaxis are selected, the response has one matrix per selected item; to select several categories of a plane variable, mark the variable itself asselectedtoo. OutsidePLANES, an axis can have at most one selected item. ASOURCESaxis has its own rule: see Survey waves.- A
TARGET_VARvariable takes a saved target (id) or a TGL expression (filterTgl). If both are sent,filterTglis used andidis ignored. - The
CUSTOM_CATcategories of aCUSTOM_VARwithsubtypeSINGLEorMULTIPLEaccepttgl(the TGL condition of the category) andhidden, asGET /api/v2/workspace/{workspaceId}/element/{elementId}returns them withresolve=TGL. When everyCUSTOM_CATcategory of the variable has atgl, the variable is calculated from them and is not looked up byid, so the key's user does not need to see it. Otherwise it is looked up byid, with no error.tglorhidden: trueanywhere else returnsAE_ERR_416. targetfilters the whole table by a saved target (id) or by a TGL expression (filterTgl). If both are sent,filterTglis used; atargetwith neither returnsAE_ERR_405.targetBaseis the population base, for anytableType: it fixes the reference universe of the percentages. It takes a saved target (id) or a TGL expression (filterTgl) with the same rule astarget. A baseidis not looked up beforehand: if it cannot be resolved, the calculation engine returns its error, except in aTRENDtable, which leaves the base out and reports it indiagnostics. Without it, the percentages are calculated over the whole universe of the survey.dataType,localeanddecimalsset the format of the cells (see Response).extraParamspasses advanced switches to the calculation engine; onlyshowBasesand, in a trend,trendCategoriesModeare documented. A switch with an invalid value returnsAE_ERR_400.- Unknown fields at the top level, in
weight, in dimensions, in axes, in split categories, in the survey waves of aSOURCESaxis and in theirweightare rejected withAE_ERR_411. Unknown fields inside variables, categories, statistics,targetandtargetBaseare ignored, so check the spelling (for example,categories, notitems, andtgl, notTGL: a misspelledtglis dropped and the variable is looked up byid). - The
dimensionsof a response can be posted back to recalculate the same table.PLANESthen carries only the slices that were returned.
Weeks and weighting of the table
weeks(a list of week idsYYYY/WW) andweight(a weighting) set the period and the weighting of the whole table. Get the valid values fromGET /api/v2/survey/{surveyCode}/weeks-and-weights: an item of itsweightscan be sent asweightas it is, and only itsidis read. AweightwithoutidreturnsAE_ERR_412.- Only
TABULATIONandREACH_TABLEtake them. With any othertableType,REACH_TRENDincluded, they returnAE_ERR_400and the message names the field: aTRENDorREACH_TRENDtakes 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_400and the message names the value. An emptyweeksis the same as not sending it, and a week sent twice returnsAE_ERR_400. - With several weeks, the weighted figures are the mean of the weeks;
Sampleand 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_TABLEthey 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:
| Field | Use |
|---|---|
id | Code of the survey, as returned in code by GET /api/v2/users/surveys. |
label | Name to show. Ignored when sent; returned in responses. |
selected | The wave shown when the axis is in PLANES. |
weeks | Weeks of the wave, with the ids of GET /api/v2/survey/{surveyCode}/weeks-and-weights. |
weight | Weighting 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
weightreturnsAE_ERR_411, and aweightwithoutidreturnsAE_ERR_412. An emptyweeksis the same as not sending it, and a week sent twice returnsAE_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_107diagnostic of levelWARNING, and the other waves are calculated. With no valid wave left, the request returnsTR_ERR_105. A survey without weeks ignoresweeks, and a survey without weightings ignoresweight. A wave withoutweightkeeps the behaviour of the engine: the API adds no weighting. - In
PLANES, at most one wave can beselected; with more, the request returnsAE_ERR_404, naming the axis and the waves. Without any, the first wave is shown. InROWSorCOLUMNS,selectedis 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. AbsFirstandPrcFirstcompare each wave with the first wave of the axis, andAbsPrevandPrcPrevwith the wave in the previous position. The first wave shows its own value in them, and a wave that follows one without values has noAbsPrevorPrcPrev.- The
sourcesof a response carryselected,weeksandweightwhen they have a value, so a response or a saved element can be sent again.
Trend tables (TREND, REACH_TREND)
- A
TRENDneeds aSOURCESaxis (without it,TR_ERR_101) and aDIFFERENCESaxis (without it,TR_ERR_103). AREACH_TRENDalso needs aCARRIERSaxis; without one of the three it is rejected withAE_ERR_412before calculating. SeePOST /api/v2/reach/{surveyCode}for the other rules of aREACH_TREND. - The
DIFFERENCESaxis takes the ids ofGET /api/v2/statistics?tableType=TREND. TheSTATISTICSaxis of aTRENDtakes the ids ofGET /api/v2/statistics?tableType=TREND&axis=STATISTICS. extraParams.trendCategoriesModechooses the categories that enter each wave. The base categories always enter:
| Value | Categories that enter |
|---|---|
ONLY_ACTIVE_SOURCE_CATEGORIES | The ones valid in the survey of the path. The default. |
ALL | The ones valid in at least one wave. |
VALID_IN_ALL_SOURCES | The ones valid in every wave. |
- Any other value of
trendCategoriesModereturnsTR_ERR_106. The key has no effect outside aTRENDor aREACH_TREND, and a saved element returns it inextraParams.
Reach fields
targetMediaanddiaryFiltersmean what they mean inPOST /api/v2/reach/{surveyCode}, and apply with a ReachtableType(REACH_TABLE,REACH_DUPLICATIONorREACH_TREND). With any othertableTypethey returnAE_ERR_400and 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/htmlvariant or toPOST /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.
| Status | errorCode | When |
|---|---|---|
| 400 | AE_ERR_401 | Invalid 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. |
| 400 | AE_ERR_400 | decimals 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. |
| 400 | AE_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. |
| 400 | AE_ERR_404 | More than one selected item or category in an axis outside PLANES, or more than one selected survey wave in a SOURCES axis in PLANES. |
| 400 | AE_ERR_405 | target or targetBase has neither a non-blank id nor a non-blank filterTgl. |
| 400 | AE_ERR_409, AE_ERR_410 | axisType 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. |
| 400 | AE_ERR_411 | A 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. |
| 400 | AE_ERR_412 | A 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. |
| 400 | AE_ERR_413 | dimensions is absent or empty, or a dimension has no axes. An empty or null body too. |
| 400 | AE_ERR_414 | A CATEGORIES axis whose parentId is not a split VARIABLES axis of the request. |
| 400 | AE_ERR_416 | Unsupported 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. |
| 400 | T_ERR_104, T_ERR_106, VA_ERR_101, RC_ERR_100 | Rejected 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. |
| 400 | TB_ERR_104 | The table has no variable to calculate: for example, its only variable is a TOTAL, or it has only a STATISTICS axis in PLANES. |
| 400 | VA_ERR_102 | A category that the variable does not have, or a JOINT category without ids. |
| 400 | CV_ERR_109 | A CUSTOM_VAR that cannot be resolved: it does not exist, is empty or was deleted. A TREND table reports it in diagnostics instead. |
| 400 | TG_ERR_113 | target or targetBase has an id that does not identify a saved target. A TREND table leaves it out and reports it in diagnostics instead. |
| 400 | TG_ERR_115 | A TARGET_VAR variable has an id that does not identify a saved target. |
| 400 | TGL_ERR_101 | A 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. |
| 400 | SP_ERR_101 | A carrier id does not exist in the survey. |
| 400 | RC_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. |
| 400 | RC_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. |
| 400 | TR_ERR_101, TR_ERR_103 | (TREND) No SOURCES axis, or no DIFFERENCES axis. |
| 400 | TR_ERR_105 | (TREND, REACH_TREND) No survey wave is valid: none is available to the user, or their weeks or weighting are not valid. |
| 400 | TR_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. |
| 400 | TR_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. |
| 400 | SO_ERR_201 | The survey is disabled or being loaded. |
| 400 | E400 | The body is not valid JSON. |
| 401 | E401 | The X-API-Key header is missing or empty. |
| 401 | SO_ERR_203 | The survey is not in your profile or does not exist. |
| 403 | AE_ERR_408 | The user's profile does not include the module required by the table type: Reach for REACH_TREND. |
| 422 | TGL_INVALID | A 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). |
| 500 | GEN_ERR_500 | Unexpected error. |
TR_ERR_107 is not an error: a discarded survey wave is a WARNING diagnostic of a 200 response.
Request
Responses
- 200
- 400
- 401
- 403
- 422
- 500
The table calculated. matrixList holds one matrix per calculated plane item.
The body cannot be turned into a table or the table cannot be calculated. AE_ERR_401: invalid value of an enumerated field. AE_ERR_404: more than one selected item outside PLANES. AE_ERR_405: target or targetBase has neither id nor filterTgl. AE_ERR_409 and AE_ERR_410: axisType is missing, unknown or 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. AE_ERR_411: a field that the object does not accept. AE_ERR_413: no axes, or no body. AE_ERR_414: a split axis that is not coherent. A variable of a VARIABLES axis that cannot be converted returns AE_ERR_412 (missing subtype, id or filterTgl) or AE_ERR_416 (unsupported combination); both locate the axis, dimension, variable, field and offending value (if any). AE_ERR_400: decimals is negative, an extraParams switch has an invalid value, a value has the wrong JSON type, or the time of a TIME_DIARY_EDGE axis cannot split the day. AE_ERR_400 also covers weeks or weight sent with a table type that does not take them, a week or a weighting the survey does not have, and targetMedia or diaryFilters with a table type that is not Reach. AE_ERR_411 covers an unknown field in a survey wave of a SOURCES axis or in its weight, and AE_ERR_412 a weight without id. AE_ERR_404 covers more than one selected survey wave in a SOURCES axis in PLANES. A REACH_TREND without its SOURCES, DIFFERENCES or CARRIERS axis returns AE_ERR_412, a statistic outside the catalog of its axis returns AE_ERR_402, and a survey of the path without Reach returns RC_ERR_100, all before calculating. T_ERR_104, T_ERR_106, VA_ERR_101 and RC_ERR_100 are raised by the calculation engine. The calculation engine also returns TG_ERR_113 (a target or targetBase id that does not identify a saved target), TGL_ERR_101 (a TGL expression with a variable or category the survey does not have) and SP_ERR_101 (a carrier the survey does not have). So do TB_ERR_104 (no variable to calculate), VA_ERR_102 (a category the variable does not have, or a JOINT without ids), CV_ERR_109 (a CUSTOM_VAR that cannot be resolved) and TG_ERR_115 (a TARGET_VAR whose id does not identify a saved target). A TREND without its SOURCES or DIFFERENCES axis returns the code of the calculation engine, TR_ERR_101 or TR_ERR_103; a REACH_TREND gets AE_ERR_412 instead. A TREND or REACH_TREND also returns the codes of the calculation engine unchanged: TR_ERR_105 (no valid survey wave), TR_ERR_106 (invalid trendCategoriesMode) and TR_ERR_109 (no survey wave left after checking the variables of the table). A discarded survey wave is not an error: it is a TR_ERR_107 diagnostic of a 200 response. SO_ERR_201: the survey is disabled or being loaded. E400: the body is not valid JSON.
The X-API-Key header is missing or empty (E401), or the survey is not in the user's profile or does not exist (SO_ERR_203).
The user's profile does not include the module the table type needs (AE_ERR_408): Tabulation for TABULATION, Study for TREND, BREAKDOWN_CARRIERS and DUPLICATIONS, and Reach for REACH_TABLE, REACH_DUPLICATION and REACH_TREND.
A TGL expression of the request cannot be used (TGL_INVALID): it does not parse, or it uses a variable without a category or condition. An unknown variable or category returns 400 TGL_ERR_101 instead.
Unexpected error.