Reporting using OData
- Former user (Deleted)
- Igor Stjepanovic
Reports can be created using the built-in reporting and 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.
In the instructions below, replace {{gruntify-reporting-server}} with the reporting URL given on your Workspace settings page 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 on an Enterprise server and will contain your workspace name. 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.
Entity Name | Key Field | Appears in other Entities as |
|---|---|---|
Users | Id | CreatedBy |
Accreditations | Id | CombinedAccreditationIds |
Depots | Id |
|
Assets | Id |
|
Teams | Id | TeamId, TeamIds |
Requests | Id |
|
Jobs | Id |
|
RegionSets | Id | RegionSetId |
JobTemplates | Id | JobTemplateId |
Forms | Id | Form <Form Id:VersionNumber>. See note below. |
FormCategories | Id | FormCategoryId |
ResourceUsage | Id |
|
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
The Form Id field is a composite of the FormId and the Version from the Form entity.
Each time you publish a form, the version number is incremented, to ensure data consistency and integrity. Therefore to find data that matches a particular form, you should join on both the form id and the version.
In another entity appears the ids appear as:
"Form": {
"Id": "e3fc3a56-3bad-480c-811c-79781304a255",
"Version": 1
},
In the Form entity:
"FormId": "e3fc3a56-3bad-480c-811c-79781304a255",
"FormVersion": 1,
"Id": "e3fc3a56-3bad-480c-811c-79781304a255:1",
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.
Further Information
For technical detail on the supported URLs and OData conventions, see Advanced Reporting using OData.
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.
Related articles