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 |
|---|---|
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 |
|---|---|
$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=2The 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.
Examples
Sample Response to the Users Call {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Users
Sample Response to the Requests Call: {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Requests
In the JSON above, you will notice that Form link fields are is
Sample Response to the Forms Call: {{gruntify-reporting-server}}/odata/workspaces/beautiful_homes/Forms
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'
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'