1. Business Intelligence Connector
  2. Business Intelligence Connector Advanced Functions
  3. OData Query Options

OData Query Options

The OpenAir OData feed lets you use the following query options to return the information you need:

Add the query options as query string parameters at the end of the URL for the OData resource you want to access. To append query options to the end of the OData resource URL, add a question mark ? followed by query option and value pairs <$option>=<value> separated by an ampersand &.

OData query URL format

$filter Query Options

You can add the $filter query option at the end of the OData resource URL to return only results that match the filter expression specified.

Add the $filter query option at the end of the OData resource URL to return only results that match the filter expression specified.

https://<account-domain>/odata/v4/reports/report279?$filter=<expression>, where <expression> is a logical expression. The operators and functions supported for use with the filter query option are listed in the following tables.

  • Comparison Operators — The syntax for comparison operators is <column> <operator> <value>, where:

    • <column> is the column name

    • <operator> is the comparison operator

    • <value> is the comparison value

    Operator

    Description

    Example

    eq

    Equals

    The following example requests all expense receipts owned by employees whose name is ‘Smith’.

    https://<account-domain>/odata/v4/reports/report279?$filter=Name eq 'Smith'

    ne

    Not equal

    The following example requests all The following example requests all expense receipts owned by employees whose name is not ‘Smith’.

    https://<account-domain>/odata/v4/reports/report279?$filter=Name ne 'Smith'

    gt

    Greater than

    The following example requests all expense receipts with a total value greater than 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Total gt 1000

    ge

    Greater than or equal

    The following example requests all expense receipts with a total value greater than or equal to 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Total ge 1000

    lt

    Less than

    The following example requests all expense receipts with a total value less than 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Total lt 1000

    le

    Less than or equal

    The following example requests all expense receipts with a total value less than or equal to 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Total le 1000

  • String Comparison Functions — The syntax for string comparison functions is <function>(<column>,<string>), where:

    • <function> is the comparison function

    • <column> is the column name

    • <string> is the comparison string between single quotation marks

    Function

    Description

    Example

    startswith(<column>,<string>)

    Starts with

    The following example requests all expense receipts associated with projects with a name that starts with ‘Hardware impl’.

    https://<account-domain>/odata/v4/reports/report279?$filter=startswith(Project_Name,'Hardware impl')

    endswith(<column>,<string>)

    Ends with

    The following example requests all expense receipts associated with projects with a name that ends with ‘ware implementation’.

    https://<account-domain>/odata/v4/reports/report279?$filter=endswith(Project_Name,'ware implementation')

    contains(<column>,<string>)

    Contains

    The following example requests all expense receipts associated with projects with a name that contains ‘software’.

    https://<account-domain>/odata/v4/reports/report279?$filter=contains(Project_Name,'software')

  • Logical and Precedence Grouping Operators

    Operator

    Description

    Example

    not

    Logical not

    The following example requests all expense receipts associated with projects with a name that does not contain ‘software’.

    https://<account-domain>/odata/v4/reports/report279?$filter=not contains(Project_Name,'software')

    and

    Logical and

    The following example requests all expense receipts owned by employees whose name is ‘Smith’ and with a total value greater than or equal to 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Name eq 'Smith' and Total ge 1000

    or

    Logical or

    The following example requests all expense receipts owned by employees whose name is not ‘Smith’ or with a total value less than or equal to 1000.

    https://<account-domain>/odata/v4/reports/report279?$filter=Name ne 'Smith' or Total le 1000

    ()

    Precedence grouping

    The following example requests all expense receipts owned by employees whose name is ‘Smith’ and with a total value either greater than or equal to 1000 or less than 500.

    https://<account-domain>/odata/v4/reports/report279?$filter=Name eq 'Smith' and (Total ge 1000 or Total lt 500)

Important:

Review the following guidelines:

  • The query string parameter must be all lower case. If you add $Filter=<expression>, for example, the filter option will be ignored and all data will be returned.

  • The query string parameter must be immediately followed by an equal sign with no space in between. Adding $filter =<expression> will return an error.

  • Column names are case sensitive.

  • Column names cannot include spaces. You should enable the Replace Non-Alphanumeric Characters with Underscores in Column Titles and Metadata optional feature.

  • All string values in the logical expression must be between single quotation marks.

  • String values in the logical expression are case insensitive. The same results will be returned whether you add $Filter= Name eq 'Smith' or $Filter= Name eq 'smith'.

$select Query Option

Use select and filter expressions in OData requests to return only the information you need.

You can add the $select query option at the end of the OData resource URL to return only the columns specified.

https://<account-domain>/odata/v4/reports/report279?$select=<columns>, where <columns> is a comma separated list of column names.

$select example

The following URL returns only the Name and Total columns from report279.

https://<account-domain>/odata/v4/reports/report279?$select= Name, Total

Important:

Review the following guidelines:

  • The query string parameter must be all lower case. If you add $Select= Name, Total, for example, the select option will be ignored and all columns will be returned.

  • The query string parameter must be immediately followed by an equal sign with no space in between. Adding $select = Name, Total will return an error.

  • Column names are case sensitive.

  • Column names cannot include spaces. You should enable the Replace Non-Alphanumeric Characters with Underscores in Column Titles and Metadata optional feature.

  • It is not possible to specify the column order in the data returned using the $select parameter. Columns are always returned in the same default order, independently of the order of columns listed in the OData request.

$top and $skip Query Options

You can add the $top and $skip query options at the end of the OData resource URL to return to return specific numbers of records.

  • Use $top to specify how many records to return.

  • Use $skip to specify the number of records to ignore (or to skip) before returning records.

$top and $skip example

The following URL returns up to 20 records from report279. It skips the first 10 records and returns the next 20 records.

https://<account-domain>/odata/v4/reports/report279?$skip=10&$top=20

Important:

Review the following guidelines:

  • The query string parameter must be all lower case. If you add $Top=10, for example, the top option will be ignored and the maximum number of rows will be returned.

  • The query string parameter must be immediately followed by an equal sign with no space in between. Adding $top =10 will return an error.

Related Topics: