Advanced Reporting using OData

 


If you wish to generate reports in Excel or PowerBI using OData, see Reporting using OData to get you started. This page describes the URLs used and OData conventions supported by Gruntify, which is needed if you will be making the OData calls directly.

Log in to the server using Basic Authentication and your existing Gruntify user account. Do not use a Microsoft Single Sign On account.

If you want to test some of the URLs using Postman, download the Sample collection file (on the right). You will need to fill in the appropriate values in the variable fields.

This is a sample collection to get you started with the OData calls and to assist you in viewing the type of data that you get back.

The Service Root URL is referred to on this page as “{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/”. Replace this with the OData URL found in Gruntify under Settings > Workspace.

 

 

MetaData

The metadata is available from {{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/$metadata

The format of the metadata will be similar to the following:

<?xml version="1.0" encoding="utf-8"?> <edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"> <edmx:DataServices> <Schema Namespace="Gruntify.Reporting.DataContracts.OData.User" xmlns="http://docs.oasis-open.org/odata/ns/edm"> <EntityType Name="ODataUser" OpenType="true"> <Key> <PropertyRef Name="Id" /> </Key> <Property Name="FirstName" Type="Edm.String" /> <Property Name="LastName" Type="Edm.String" /> <Property Name="Status" Type="Edm.String" /> ..... <NavigationProperty Name="Accreditations" Type="Collection(Gruntify.Reporting.DataContracts.OData.User.UserAccreditation)" /> </EntityType> <EntityType Name="UserAccreditation"> <Key> <PropertyRef Name="Id" /> </Key> <Property Name="Id" Type="Edm.String" Nullable="false" /> ..... <Property Name="Expiry" Type="Edm.String" /> </EntityType> <ComplexType Name="UserPreferences"> <Property Name="Units" Type="Edm.String" /> ..... <Property Name="MapPreferences" Type="Gruntify.Reporting.DataContracts.OData.User.UserMapPreferences" /> </ComplexType> ..... </Schema> </edmx:DataServices> </edmx:Edmx>

Entity URLs

Each of the main entities is queried with its own URL. To filter queries, say all versions of a single form, or for a particular job, use the standard OData format as shown below. You can filter on any simple field in an entity.

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/forms?$filter={{filterFieldName}} eq '{{value}}'

Entity Name

 

“Get All” URL

Entity Name

 

“Get All” URL

Users

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/users

Accreditations

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/accreditations

Depots

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/depots

Assets

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/assets

Teams

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/teams

Requests

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/requests

Jobs

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/jobs

Job - get an individual job

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/jobs

RegionSets

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/regionSets

JobTemplates

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/jobtemplates

Forms

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/forms

FormCategories

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/formcategories

ResourceUsage

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/resourceusage

URL Convention Support - Query String Options

The Gruntify OData interface supports the following conventions described by URL Conventions.

Unless specified elsewhere in the help, the various resource path options like $count are not supported. Many of these pathing options are not necessary given that we return very detailed entities (including sub-entities) so there is less need to drill down into the entity path.

Query String Option

Comment

Query String Option

Comment

$orderby

Not supported

$top, $skip

Supported - required for paging results. $skip may be dropped in the future so use the next link supplied in the response, not $skip.

$filter

Some operators are supported. More will be added in the future as needed.

Supported: eq, gt, get, lt, le, and, or,

Not Supported: ne (not equal), not, Arithmetic Operators, String Functions, Date Functions, Math Functions

OData Extension for Data Aggregation

Some extensions are also supported. Note: Data is returned in an expanded format so there is reduced need for the extensions. For example, a request contains the subfields such as Form, Location, and Assets directly so there is less need for expanding.

Paging

To ensure you are not bringing back huge amounts of data, if there may be a large number of records found. Please use $top to set the number of records to be returned and use the nextLink to get the next page of records.

If you do not use the paging and you issue a query that would return too many records, the system will return a truncated set but without the “next” link to indicate that there are more records to come. This may result in an error in your report as you assume there are no more records.

The format of the value in @odata.nextLink is subject to change, so do not code your own next links. Use the next link included in the response data.

If you were to call:

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/requests?$top=2

The response would start:

{ "@odata.context": "{{gruntify-reporting-server}}odata/workspaces/{{workspace}}/$metadata#Requests", "@odata.nextLink": "{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/requests?$top=2&$skip=2", "value": [ { .....

Calling the odata.nextLink would return you the next batch of records. This continues until there are no more records, at which point the odata.nextLink entry is no longer in the response.

{ "@odata.context": "{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/$metadata#Requests", "value": [ { .....

Examples

Sample Response to the Users Call {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Users

{ "@odata.context": "{{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/$metadata#Users", "value": [ { "FirstName": "Rey", "LastName": "Bello", "Status": "Active", "TeamIds": [], "Accreditations": [], "Roles": [ "PrimaryOwner" ], "RegionIds": [], "Preferences": { "Units": null, "DateFormat": null, "TimeZoneId": "E. Australia Standard Time", "MapPreferences": null }, "CustomProperties": [], "Properties": null, "Id": "auth0|5f8d08368226d8006f610b8f", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-02T02:07:31.8278289+00:00", "LastModifiedAt": "2020-11-02T02:07:31.8278289+00:00" }, { "FirstName": null, "LastName": null, "Status": "Invited", "TeamIds": [], "Accreditations": [], "Roles": [ "Controller" ], "RegionIds": [], "Preferences": null, "CustomProperties": [], "Properties": null, "Id": "green_teri@outlook.com", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-03T02:46:45.1436496+00:00", "LastModifiedAt": "2020-11-03T02:46:45.1436496+00:00" }, .... { "FirstName": null, "LastName": null, "Status": "Invited", "TeamIds": [ "3b4281f5-b21a-461b-90ee-061fd40d5c9b" ], "Accreditations": [], "Roles": [ "Worker" ], "RegionIds": [], "Preferences": null, "CustomProperties": [], "Properties": null, "Id": "bhg.flynn8@gmail.com", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-03T02:38:02.5000869+00:00", "LastModifiedAt": "2020-11-03T02:38:02.5000869+00:00" }, { "FirstName": "Mark", "LastName": "Flynn", "Status": "Active", "TeamIds": [ "765e72ae-6e2b-4890-b080-d7a98b0add8b" ], "Accreditations": [], "Roles": [ "Worker" ], "RegionIds": [], "Preferences": { "Units": null, "DateFormat": null, "TimeZoneId": null, "MapPreferences": null }, "CustomProperties": [], "Properties": null, "Id": "auth0|5f8e7f7b69824b0079944aa4", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-03T02:56:04.4461851+00:00", "LastModifiedAt": "2020-11-15T03:59:03.3998457+00:00" }, { "FirstName": "Theresa", "LastName": "Green", "Status": "Active", "TeamIds": [], "Accreditations": [], "Roles": [ "Controller" ], "RegionIds": [], "Preferences": { "Units": null, "DateFormat": null, "TimeZoneId": null, "MapPreferences": null }, "CustomProperties": [], "Properties": null, "Id": "auth0|5f90cece01caa80068440643", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-03T02:55:57.4904997+00:00", "LastModifiedAt": "2020-11-03T02:55:57.4904997+00:00" }, ...... ] }

Sample Response to the Requests Call: {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Requests

{ "@odata.context": "{{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/$metadata#Requests", "value": [ { "Reference": "uo2x0203", "CreatedBy": "auth0|5f8d08368226d8006f610b8f", "Form": { "Id": "e3fc3a56-3bad-480c-811c-79781304a255", "Version": 1 }, "Status": "Accepted", "StatusReason": null, "Location": { "Type": "Point", "Coordinates": [ 153.073083, -27.542646 ] }, "Orientation": null, "GpsAccuracy": 0.0, "Device": { "DeviceId": null, "Os": "Mac", "OsVersion": "mac-os-x-15", "Type": "Browser" }, "Details": null, "Submitter": null, "FormData": { "TextField-8ubq": "Dirk Loggs", "Segmented-hv5u": "Yes", "Segmented-cp30": "Yes", "Segmented-vwzh": "No", "Segmented-u3pf": "Yes", "Segmented-4pm0": "Yes", "Segmented-8w6d": "Yes", "Segmented-mmwm": "Yes", "Signature-7vr8": "request-media/5efb7d7f-b7dc-4c85-9ea4-e2b5be1651cc/Signature-7vr8.png" }, "FeaturedImageFileId": null, "CaptureDateTime": "2020-11-15T05:38:22.875+00:00", "Media": { "Videos": [], "Images": [], "Audio": [], "Drawings": [] }, "Regions": [ { "RegionSetId": "7afe9596-06da-477d-9374-3ab2baa9c487", "RegionSetName": "sample", "RegionId": "d328e3c7-8c19-4b06-a4e9-64adcbca0ae8", "RegionName": "Metropolitan" } ], "RegionIds": [ "d328e3c7-8c19-4b06-a4e9-64adcbca0ae8" ], "IsReinspection": false, "HasTrip": false, "LinkedRequests": [], "AuthorizedTeamIds": [], "Assets": [], "Job": null, "SourceSystem": null, "FeatureCollectionId": null, "Properties": null, "Id": "76831baa-c803-4df3-a71c-f4d1dc2f0203", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-15T05:38:23.3511393+00:00", "LastModifiedAt": "2020-11-15T05:38:36.7103985+00:00" } ] }

In the JSON above, you will notice that Form link fields are is

"Form": { "Id": "e3fc3a56-3bad-480c-811c-79781304a255", "Version": 1 },

Sample Response to the Forms Call: {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Forms

{ "@odata.context": "{{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/$metadata#Forms", "value": [ { "FormId": "e3fc3a56-3bad-480c-811c-79781304a255", "FormVersion": 1, "Title": "Landscaping Maintenance checklist", "FormCategoryId": "fd206d0f-a96a-43e2-9091-3947d11e291d", "FormCategoryName": "Default", "Status": "Published", "FormType": "Request", "RequestOptions": { "Media": { "Photos": { "Allowed": true }, "PhotoGallery": { "Allowed": true }, "Audio": { "Allowed": true, "MaximumSeconds": 15 }, "Video": { "Allowed": true, "MaximumSeconds": 15 }, "Drawing": { "Allowed": true }, "MinimumRequired": 0 }, "EditOptions": { "EditMode": "ExistingData", "CloneMedia": false }, "TabOptions": { "ShowDetailsTab": false }, "TabOrder": [ "Media", "Form", "Location" ], "RequestTitle": { "Type": "FormTitle", "Value": null } }, "AssetOptions": null, "JobOptions": null, "Permissions": { "CreatePermissions": { "TeamIds": [] }, "UpdatePermissions": { "Nobody": false, "RequestCreator": false, "TeamIds": [] }, "ReadPermissions": { "TeamIds": [] } }, "Configurations": [ "Main" ], "Id": "e3fc3a56-3bad-480c-811c-79781304a255:1", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-15T05:34:17.6715857+00:00", "LastModifiedAt": "2020-11-15T05:34:20.3264233+00:00" }, { "FormId": "011ca04b-e187-4d73-aaab-36325ce6c4c4", "FormVersion": 1, "Title": "Form Name", "FormCategoryId": "fd206d0f-a96a-43e2-9091-3947d11e291d", "FormCategoryName": "Default", "Status": "Archived", "FormType": "Job", "RequestOptions": null, "AssetOptions": null, "JobOptions": { "Media": { "Photos": { "Allowed": true }, "PhotoGallery": { "Allowed": true }, "Audio": { "Allowed": true, "MaximumSeconds": 15 }, "Video": { "Allowed": true, "MaximumSeconds": 15 }, "Drawing": { "Allowed": true }, "MinimumRequired": 0 }, "TabOrder": [ "Media", "Form" ] }, "Permissions": { "CreatePermissions": { "TeamIds": [] }, "UpdatePermissions": { "Nobody": false, "RequestCreator": false, "TeamIds": [] }, "ReadPermissions": { "TeamIds": [] } }, "Configurations": [ "Pre", "Post" ], "Id": "011ca04b-e187-4d73-aaab-36325ce6c4c4:1", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-15T05:45:44.1949759+00:00", "LastModifiedAt": "2020-11-15T05:46:06.3669709+00:00" }, { "FormId": "011ca04b-e187-4d73-aaab-36325ce6c4c4", "FormVersion": 2, "Title": "General Job", "FormCategoryId": "fd206d0f-a96a-43e2-9091-3947d11e291d", "FormCategoryName": "Default", "Status": "Published", "FormType": "Job", "RequestOptions": null, "AssetOptions": null, "JobOptions": { "Media": { "Photos": { "Allowed": true }, "PhotoGallery": { "Allowed": true }, "Audio": { "Allowed": true, "MaximumSeconds": 15 }, "Video": { "Allowed": true, "MaximumSeconds": 15 }, "Drawing": { "Allowed": true }, "MinimumRequired": 0 }, "TabOrder": [ "Media", "Form" ] }, "Permissions": { "CreatePermissions": { "TeamIds": [] }, "UpdatePermissions": { "Nobody": false, "RequestCreator": false, "TeamIds": [] }, "ReadPermissions": { "TeamIds": [] } }, "Configurations": [ "Pre", "Post" ], "Id": "011ca04b-e187-4d73-aaab-36325ce6c4c4:2", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-15T05:46:04.5342588+00:00", "LastModifiedAt": "2020-11-15T05:46:06.3887256+00:00" } ] }

Sample Response to Forms Call to get all the versions of an individual form: {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Forms?$filter=FormId eq 'e3fc3a56-3bad-480c-811c-79781304a255'

{ "@odata.context": "{{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/$metadata#Forms", "value": [ { "FormId": "e3fc3a56-3bad-480c-811c-79781304a255", "FormVersion": 1, "Title": "Landscaping Maintenance checklist", "FormCategoryId": "fd206d0f-a96a-43e2-9091-3947d11e291d", "FormCategoryName": "Default", "Status": "Published", "FormType": "Request", "RequestOptions": { "Media": { "Photos": { "Allowed": true }, "PhotoGallery": { "Allowed": true }, "Audio": { "Allowed": true, "MaximumSeconds": 15 }, "Video": { "Allowed": true, "MaximumSeconds": 15 }, "Drawing": { "Allowed": true }, "MinimumRequired": 0 }, "EditOptions": { "EditMode": "ExistingData", "CloneMedia": false }, "TabOptions": { "ShowDetailsTab": false }, "TabOrder": [ "Media", "Form", "Location" ], "RequestTitle": { "Type": "FormTitle", "Value": null } }, "AssetOptions": null, "JobOptions": null, "Permissions": { "CreatePermissions": { "TeamIds": [] }, "UpdatePermissions": { "Nobody": false, "RequestCreator": false, "TeamIds": [] }, "ReadPermissions": { "TeamIds": [] } }, "Configurations": [ "Main" ], "Id": "e3fc3a56-3bad-480c-811c-79781304a255:1", "WorkspaceId": "beautiful_homes", "CreatedAt": "2020-11-15T05:34:17.6715857+00:00", "LastModifiedAt": "2020-11-15T05:34:20.3264233+00:00" } ] }

Note: If you want to get a particular version of a form use the following format. FormId changes to Id and the Id is made up of <formId>:<version number>.

{{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/Forms?$filter=Id eq 'e3fc3a56-3bad-480c-811c-79781304a255:1'