Additional Query Options for Data Export
The Data Export Service API supports standard OData parameters.
The tables below lists the additional supported parameters that you can use to filter out results when sending requests.
| Parameter | Type | Description | Example |
|---|---|---|---|
| $select Note Make sure it contains all key columns. You can get the key columns from the provider
metadata endpoint. |
String | Selects specific columns. | $select=MyColumn1,MyColumn2 |
| $top | Positive Integer | Returns top N values. | $top=15 |
| $skip | Positive Integer | Skips first N Values. | $skip=5 |
| pagesize | Positive Integer | Returns N number of entities in the page. | pagesize=5 |
| package | Positive Integer | Returns the Nth package. | package=2 |
| $orderby | String | Orders by columns specified. Default order is ascending (asc), can be changed to descending. (desc) | $orderby=MyMeasure1 desc |
| $filter - all logical operators (eq, ne, gt, lt, ge, le) | Matches with type of column operated on. | Applies logical OData filters. | $filter=Region eq 'REG0001' The Unassigned Value # value must be escaped using %23. $filter=Region eq '%23' |
| $filter - contains() | Matches with type of column operated on. | Returns rows where column contains defined value. | $filter=contains(Currency,'EUR') |
| $filter - startswith() | Matches with type of column operated on. | Returns rows where column starts with defined value. | $filter=startswith(Account,'6') |
| $filter - endswith() | Matches with type of column operated on. | Returns rows where column ends with defined value. | $filter=endswith(Region,'4') |
| $filter - and | Boolean | OData logical “and” | $filter=MyMeasure1 gt 0 and MyMeasure1 lt 10000 |
| $filter - or | Boolean | OData logical “or” | $filter=MyMeasure1 gt 0 or MyMeasure1 lt 10000 |
| $filter - not | Boolean | OData logical “not” | $filter=not contains(Currency,'EUR') and MyMeasure1 gt 0 |
| $filter - () | Boolean | Parenthesis for filter operations. |
$filter=(contains(Currency,'EUR') or contains(Currency,'USD')) and MyMeasure1 gt 0$filter=(Account eq '400000' or Account eq '400100') and Region eq 'REG0001' $filter=(Currency eq 'EUR' or Currency eq 'CAD') and (Product eq 'PRD0004' or Product eq 'PRD0003') |
| $count | Boolean | Returns the number of entities in the result set, with any filters (if specified). Will be annotated by @odata.count key. | $count=true |
| Parameter | Type | Description | Example |
|---|---|---|---|
| $filter=(Account eq '400000' or Account eq '400100') and$filter - all logical operators (eq, ne, gt, lt, ge, le) | Matches with type of column operated on. | Applies logical OData filters. | $filter=Region eq 'REG0001' The Unassigned Value # value must be escaped using %23. $filter=Region eq '%23' |
| $filter - contains() | Matches with type of column operated on. | Returns rows where column contains defined value. | $filter=contains(Currency,'EUR') |
| $filter - startswith() | Matches with type of column operated on. | Returns rows where column starts with defined value. | $filter=startswith(Account,'6') |
| $filter - endswith() | Matches with type of column operated on. | Returns rows where column ends with defined value. | $filter=endswith(Region,'4') |
| $filter - and | Boolean | OData logical “and” | $filter=MyMeasure1 gt 0 and MyMeasure1 lt 10000 |
| $filter - or | Boolean | OData logical “or” | $filter=MyMeasure1 gt 0 or MyMeasure1 lt 10000 |
| $filter - not | Boolean | OData logical “not” | $filter=not contains(Currency,'EUR') and MyMeasure1 gt 0 |
| $filter - () | Boolean | Parenthesis for filter operations. |
$filter=(Account eq '400000' or Account eq '400100') and Region eq 'REG0001' $filter=(contains(Currency,'EUR') or contains(Currency,'USD')) and MyMeasure1 gt 0 $filter=(Currency eq 'EUR' or Currency eq 'CAD') and (Product eq 'PRD0004' or Product eq 'PRD0003') |
| $filter - tolower | Matches with type of column operated on. | Changes the text to lowercase. | $filter=tolower(Description) eq 'name' |
| $filter - toupper | Matches with type of column operated on. | Changes the text to uppercase. | $filter=toupper(NamespaceID) eq 'SAC' |