--- title: "Advanced Reporting using OData" canonical: "https://help.gruntify.com/space/GRUNTIFY/156073985/Advanced%20Reporting%20using%20OData" format: markdown --- [Professional] [Business] --- > ⚠️ **This article documents the deprecated Gruntify Reporting Service v4.3.** Version 4.3 is deprecated and will be retired in a future release. The material below is retained for workspaces that still run v4.3 reports awaiting migration. For anything you are building now, use v5 — see [OData Reporting Service V5](https://gruntify.atlassian.net/wiki/spaces/GRUNTIFY/pages/1447788545). > ⚠️ **Some entities on this page return data that does not update** > ⚠️ > ⚠️ Every URL below still resolves and still returns rows. For most entities, those rows are static: v4.3 does not report current data for them, and it signals this with no error, no warning and no empty result. A query that works is not a query that is current, and a report that refreshes cleanly is not a report you can trust. > ⚠️ > ⚠️ On v4.3, `Requests`, `Jobs`, and `Assets` report current data. Treat every other entity on this page as static until you have checked it against the Main Entities table in [Reporting using OData](https://gruntify.atlassian.net/wiki/spaces/GRUNTIFY/pages/123764748), which records the position for each entity. > ⚠️ > ⚠️ Moving a report to v5 resolves this — v5 reports current data for every entity. If you wish to generate reports in Excel or PowerBI using OData, see [Reporting using OData](https://gruntify.atlassian.net/wiki/spaces/GRUNTIFY/pages/123764748) 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. # MetaData The metadata is available from {{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/$metadata The format of the metadata will be similar to the following: ``` ..... ..... ..... ..... ``` # 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** | | --- | --- | | **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 | | 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](http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html#_Toc31360954). 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 | | --- | --- | | ### $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 the **Form** link field is nested, holding the form’s Id and Version: ``` "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 :. {{gruntify-reporting-server}}/odata/workspaces/{{workspace}}/Forms?$filter=Id eq 'e3fc3a56-3bad-480c-811c-79781304a255:1' ## Related Articles > Macro (contentbylabel)