PROFESSIONAL BUSINESS

Reports can be created using the built-in export templates but if you want to export data to another system or do your own Microsoft® Excel reporting then some of your Gruntify data can be accessed via OData, a widely recognized open data protocol.

The easiest way to get started is to use software like Microsoft® Excel that has OData support built-in.

Reporting Service and Versioning

Gruntify publishes a Reporting Service that implements the OData 4.0 standard. It's important to understand the versioning:

Version Numbers (v4.3, v5) refer to the Gruntify Reporting Service version, NOT the OData standard version
v5 is the current supported version and should be used for every report you build
v4.3 is deprecated, will be retired in a future release, and does not update every entity — see the warning below
If you have existing v4.3 reports, plan to migrate them to v5
For the complete technical reference to the current API schema, see OData Reporting Service V5

v4.3 returns data that does not update for some entities

v4.3 does not report current data for every entity. Where it does not, the query still succeeds: you get an HTTP 200, a populated result set, and a report that refreshes without complaint. The rows are simply static, and v4.3 gives you no error, no warning and no empty result to tell you so.

A working query is not evidence of current data. The v4.3 updates column in the Main Entities table below is the only way to tell which entities are affected. Any report drawing on an entity marked No will drift further from your workspace with every refresh.

v5 reports current data for every entity. If a report matters, move it to v5.

In the instructions below, replace {{workspacename}} with your workspace name. The reporting URL format is:

https://reporting.gruntify.com/v5/odata/workspaces/{{workspacename}}

This URL can be found in Settings > Workspace in the Admin Portal, accessible to staff with the Owner role.

The URL will vary depending on whether you are using https://portal.gruntify.com or an Enterprise server. Log in using your usual Gruntify username and password.

Using Microsoft® Excel

Excel is probably the most widely used software that will access the data for you, using a visual query builder. Accessing the data using Excel does not require any programming. The following short tutorial will get you started.

This functionality is not available in Microsoft® Excel for Mac or in older versions of Excel under Windows. For now, we suggest you use Office 365 Excel desktop application on Windows.

Using Microsoft® PowerBI

PowerBI is a powerful business reporting tool. The following short tutorial will show you how to access the Gruntify data within PowerBI. More details on doing queries in PowerBI are given on the Data Analytics using PowerBI page. Finish reading through this page first before going on to the PowerBI page as we will outline which entities you can expect to find, and how they work together, on the current page.

This video tutorial is made using the desktop application available under Windows. Users on other platforms will need to use the cloud-based interface which may look a little different.

Main Entities

The starting point for a report is the main entities, such as User, Job, Request, and each entity has its own set of entities and details. To start to access the data, start with one of these entities, and work from there, joining other entities to build the full picture. Usually, you will link one entity to another (a join) using the key fields. If the matching field has a plural name (e.g. TeamIds), the values will be a String, which is itself a list of strings.

Note: The data feed definitions contain all the entities that may be returned by the Gruntify OData interface. As your account may not have access to all features within Gruntify, you may see some entities that are not using, such as Assets or Jobs. These entities can be safely ignored - if you query them you will get 0 records returned.

Entity Name	Key Field	v4.3 updates	Description

Entity Name	Key Field	v4.3 updates	Description
Users	id	No	System users and staff members
Teams	id	No	Work teams and groups
Accreditations	id	No	User certifications and qualifications
Regions	id	No	Geographic regions
Forms	formKey	No	Custom form definitions (composite key)
Requests	id	Yes	Service requests and work orders
Jobs	id	Yes	Scheduled jobs and assignments
Assets	id	Yes	Equipment and resources
JobTemplates	id	No	Job template definitions
Equipment	id	No	Equipment catalog entries
ResourceUsage	id	No	Resource consumption and tracking
Trips	id	No	Trip and travel records

v5 reports current data for every entity in this table. The v4.3 updates column applies only to the deprecated v4.3 service. No means a v4.3 query for that entity still returns rows, but those rows are static — see the warning under Reporting Service and Versioning above. Of the entities in this table, only Requests, Jobs and Assets report current data on v4.3.

On v4.3 the Regions entity is addressed as RegionSets. Its v4.3 URL is listed in Advanced Reporting using OData.

The Importance of Forms

The Form used to create a Request or an Asset set the Type of the Request or Asset, and hence determine which fields will exist in the Form Data. If you are interested in the data collected by your users, rather than the standard request or asset fields, then you should select the Requests/Assets by Form (see Form Id below) or the form data will not be consistent across records.

In a similar way, Jobs should be selected by Job Template as the template then determines the Forms used.

Form Id

In v5, Forms use a composite key called formKey which combines the form identifier and version information.

Each time you publish a form, the version number is incremented, to ensure data consistency and integrity. In Requests, Jobs and Assets you will find:

formId: The form identifier
formVersion: The form version number
These are now top-level fields rather than nested objects

Jobs may have a “null” form version, this is intentional as a Job will automatically set the version to match the latest version of the linked Form when the first team checks in.

In the Form entity:

{
  "formKey": "e3fc3a56-3bad-480c-811c-79781304a255:1",
  "id": "e3fc3a56-3bad-480c-811c-79781304a255",
  "version": 1
}

In Request, Job and Asset entities:

{
  "formId": "e3fc3a56-3bad-480c-811c-79781304a255",
  "formVersion": 1
}

The flatter structure in v5 makes it easier to query and join on form data.

Relationships between Entities

Note: Assets may be related to Requests or Jobs. If you are using Gruntify Professional, the first diagram below only applies. If you have Gruntify for Business, both of these diagrams apply and the Requests can be related to Assets in the same way that Jobs are related to Assets.

Gruntify Professional - Request Relationships

Gruntify for Business - Job Relationships

Job Status Flow

Connecting a GeoJSON Layer to OData

The instructions below should be helpful in connecting a GeoJSON Layer to OData. This is beneficial when you need to combine spatial data with other types of structured data such as Requests, Jobs and Assets.

Download the IconMap Visualization in PowerBI
1. Open PowerBI
2. In Visualizations, select the and click get more visuals
3. Search and Select 'Icon Map'
4. Click
Connect OData
1. The video tutorial above details step-by-step instructions on how to connect OData to PowerBI.
Import the GeoJSON that you would like to visualize in PowerBI
1. Place Icon Map in the report by clicking on the newly added Icon Map icon.
2. Fill out the required categories.
3. Go to Format Visual under Visualizations
4. Enable the GeoJSON by clicking on the toggle icon
5. Select the GeoJson column provided as part of the OData entity.

Further Information

For complete technical details on the supported URLs, query syntax, and OData conventions, see OData V5, which is the primary technical reference for the current API schema.

If you are working with the deprecated v4.3 interface, refer to Advanced Reporting using OData for v4.3-specific documentation.

Migrating from v4.3

If you have existing reports or integrations using the deprecated v4.3 service, see the Migrating from v4.3 section in the OData V5 technical reference for step-by-step guidance.

Troubleshooting

Q: I do not have an option for the Workspace Settings screen in the Admin Portal.

A: This option is only available to users with an Owner role. Talk to your Gruntify system administrators to check your role.

Q: I can access the Workspace Settings screen but cannot see the Reporting URL.

A: OData may not be enabled for your environment. Talk to your Gruntify support contacts.

Q: In Excel, my OData screens look different to the tutorial screens.

From Office 365:

If your version of Excel does not have this OData feed dialog then you will not be able to access OData. We suggest you upgrade your version of Excel or try PowerBI instead. PowerBI has many more features for data reporting than Excel.

Reporting using OData