Supported Basic Operations

For the Task of ...

Use the following REST call (URL)

Returning a complete list of all reporting tables and views in your warehouse.

The following call will return a list of all reporting views/tables in your warehouse:

https://<Server_Name>/ReportingAPI/odata

This is also the call used when using the Reporting API with Microsoft Power BI.

Returning all records, and all fields from a table

To return a complete list of records in a particular table use the following:

https://<Server_Name>/ReportingAPI/odata/<component>

For example:

https://<Server_Name>/ReportingAPI/odata/hazard_register

Supported Operations

For the Task of ...

Use the following REST call (URL)

Only returning certain fields

To only return a $select list of columns,add the following to the base call:

...?$select=<column_name_1>,<column_name_2>,...

Example:

https://<Server_Name>/ReportingAPI/odata/action?$select=action_source,priority

Ordering the records

Without using the $order option, the Reporting API will order the records by the DWI key, ascending.

To order records on a different column, add the following to the base call:

...?$orderby=<column_name> [desc]

By default, ordering will be done ascending.

Examples:

https://<Server_Name>/ReportingAPI/odata/action?$orderby=action_source

https://<Server_Name>/ReportingAPI/odata/action?$orderby=action_source desc

Skipping the first x records

If you need to $skip a number of records, use:

...?$skip=<number>

For example, to skip the first 500 records:

https://<Server_Name>/ReportingAPI/odata/incident?$skip=500

Only returning the top x number of records

$Top is opposite of $skip, as it will only return the Top x number of records:

...?$top=<number>

Example:

https://<Server_Name>/ReportingAPI/odata/incident?$top=100

Filtering records - general

Users can use the OData $filter parameter to filter data. The supported operators are:

• eq, ne, gt, ge, lt, le
• and, or, not
• grouping()

The generic format is as follows:

...?$filter=<column_name> <operator> <value>

Examples:

https://<Server_Name>/ReportingAPI/odata/incident?$filter=potential_severity eq 'Major'

https://<Server_Name>/ReportingAPI/OData/incident?$filter=potential_severity eq 'Major' or potential_severity eq 'Severe'

NOTE:
The filter operator does not support operations on expanded, complex properties.

Filtering records - Date and Time Fields

To filter on Date, Time, and DateTime (e.g. bms_LastModified), OData prescribes a particular format to represent these fields.

Here are some examples:

• Date Fields:
  ...?$filter=date(entered_date) lt 2018-07-01
• Time Fields:
  ...?$filter=entered_time lt 13:00:00
• DateTime Field (for example bms_LastModified):
  ...?$filter=bms_LastModified gt 2018-11-30T18:00:00.00Z

Filtering records - using arithmetic operators

The Reporting API is supporting the following arithmetic operators on numeric fields for filtering records:

• add: for adding the left and right numeric operand
• sub: will subtract the right operand from the left operand
• mul: will multiply the left and right operand
• div: divides the left operand by the right operand
• mod (modulo)

The format is:

...?$filter=<column_name_left> <ar_op> <column_name_right> <op> <value>

This example will return all records where the ratio of contaminants recovered to contaminants released is less than 90%:

https://<Server_Name>/ReportingAPI/odata/incident_consequence_environmental?$filter=contaminants_volume_recovered div contaminants_volume_released lt 0.9

Searching for a string within the returned payload.

The $search parameter allows user to find records where a string, or strings, can be found anywhere within the JSON construct/payload:

...?$search=term

There are a variety of options:

• Search for an exact phrase:
  ...?$search="Exact String"
• Search for records which contain ALL of two or more words, anywhere in the payload:
  - Option 1: ...?$search=this that (Note: no quotes)
  - Option 2: ...?$search=this AND that (Note: AND must be in uppercase)
  
• Search for records which contain ANY of two or more words, anywhere in the payload:
  ...?$search=this OR that (Note: OR must be in uppercase)
  
• Search also supports the user of parentheses, for example:
  ...$search=(this AND that) OR "the other"
  
• Search for records which do NOT have an expression, or string anywhere in the payload:
  ...?$search=NOT "fire extinguisher"

Expanding the response to include related entities

The $expand operation will include a related entity inline in the response:

...?$expand=dwk_<RelationshipName>Navigation

Please note the keyword "Navigation" - this is required for the expand to function.

https://<Server_Name>/ReportingAPI/odata/action?$expand=dwk_completed_byNavigation

Limitations:

• Expand only currently supports one level deep.
• You only can expand on one relationship per call
• Cannot use the nested operators within the $expand operator, such as a $filter or $select

Selecting a single record by ID.

NOTE that the ID's are the dwi_ or dwk_ keys from the data warehouse.

The syntax for retrieving a record by ID will depend on the way these records are identified in a table.

Records are identified by their dwi_ key

This will be true for the vast majority of cases. To retrieve a single record by it's ID, simple add the ID in brackets - for example to retrieve the Incident record with the dwi_ key of 297:

https://<Server_Name>/ReportingAPI/OData/incident(297)

Records are identified by multiple dwk_ keys

The OData stream also includes entities which only can be identified by dwk_ keys, for example JOIN tables. Here is an example:

https://<Server_Name>/ReportingAPI/odata/hazard_register_hazard_category(dwk_hazard_register=4,dwk_hazard_category=34)

Counting the number of records which are returned

Option 1:

The $count parameter can be used to add a line, reporting the number of records, to the payload:

...$count=true

Note that $count will take any $filter into consideration, but will ignore $skip and $top.

Example:

https://<Server_Name>/ReportingAPI/odata/incident?$filter=actual_severity eq 'Not Applicable' &$count=true

Count will return an extra line in the JSON construct: "@OData.count": <count>

Option 2:

To return just the number of records in a table/view, and no other data, use the $count parameter as follows:

https://<Server_Name>/ReportingAPI/OData/incident/$count

Combining several parameters in a single call

With OData you can combine several types of parameters, using the &:

...&$<parameter>

Example:

https://<Server_Name>/ReportingAPI/odata/action?$filter=priority eq 'Urgent' &$orderby=action_source &$count=true