Supported String Functions

Function

Syntax and Examples

contains()

...?$filter=contains(<column_name>,'search string')

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=contains(action_summary,'check fire')

startswith()

...?$filter=startswith(<column_name>,'search string')

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=startswith(action_summary,'fire')

endswith()

...?$filter=endswith(<column_name>,'search string')

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=endswith(action_summary,'place')

tolower()

...?$filter=tolower(<column_name>) <op> 'search string'

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=tolower(action_summary) eq 'fix loose barricading in place'

toupper()

...?$filter=toupper(<column_name>) <op> 'search string'

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=toupper(action_summary) eq 'FIX LOOSE BARRICADING IN PLACE'

substring()

...?$filter=substring(<column_name>,[offset]) <op> 'search string'

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=substring(action_summary,4) eq 'loose barricading in place'

This will find records with the string "loose barricading in place" once the first 4 characters have been removed.

length()

Returns records of a specified length.

...?$filter=length(<column_name>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=length(action_summary) lt 20

indexof()

...?$filter=indexof(<column_name>,'search string') <op> #

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=indexof(action_description,'struck') gt 20

As OData will identify the first character in a string as 0, this will return all records containing the string "struck", starting at the 21st character or later.

concat()

...?$filter=concat(<string_1>,<string_2>) <op> <search string>

The concat function will string both parameter strings together, and it is possible to use a 'nested' concat.

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=concat(concat(action_source, ', '), completion_category) eq 'Hazard Management, Complete'

This will return all records where the Action Source = Hazard Management, AND the Completion Category = Complete.

Supported Date Functions

Function

Syntax and Examples

year()

also: month(), day(), hour(), minute(), second()

...?$filter=year(<column_name>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=year(completion_date) gt 2017

totaloffsetminutes()

...?$filter=totaloffsetminutes(<column_name>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/incident?$filter=totaloffsetminutes(bms_LastModified) eq 60

now()

...?$filter=<column_name> <op> now()

Example:

https://<Server_Name>/ReportingApi/odata/incident?$filter=bms_LastModified lt now()

Supported Datetime Functions

Function

Syntax and Examples

date()

...?$filter=year(<datetimefield>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/action?$filter=date(bms_LastModified) gt 2019-01-01

Supported Math Functions

Function

Syntax and Examples

ceiling

...?$filter=ceiling(<column_name>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/moc_change_review?$filter=ceiling(total_cost_estimate) ge 1500

List all records, where the total_cost_estimate is greater or equal to 1500, when rounded UP.

floor

...?$filter=floor(<column_name>) <op> #

Example:

https://<Server_Name>/ReportingApi/odata/moc_change_review?$filter=floor(total_cost_estimate) ge 1500

List all records, where the total_cost_estimate is greater or equal to 1500, when rounded DOWN.