OpenGround Query Guide


OpenGround Power BI Custom Query Guide

This guide explains how to use OpenGround's custom query functions in Power BI. It is intended for users who need to retrieve OpenGround data for reporting and analysis but may not be familiar with the underlying API or technical implementation details.

The examples in this guide focus on common reporting tasks such as connecting to OpenGround, retrieving project data, filtering results, and troubleshooting common issues.

Who Is This Guide For?

What You'll Learn

Getting Started

Before building reports, confirm that you can successfully connect to OpenGround and view available projects.

Requirements

First Test Query

let
    Cloud = OpenGround.GetCloud("BentleyUS", "eastus"),
    Projects = OpenGround.GetProjects(Cloud)
in
    Projects

Expected Result

A table containing the projects available to your OpenGround account.

If It Doesn't Work

How OpenGround Data Is Organized

Most OpenGround data is stored in related levels. Understanding these relationships makes it easier to build reports.

Project
 └─ Location
     └─ Sample
         └─ Test

For example, a project can contain many locations. Each location can contain many samples, and each sample can contain one or more tests.

Common Terms

Term Description
Project An OpenGround project.
Group

A set of data in OpenGround such as Location Details, Sample Information, or Field Geological Information. (ie. a table in OpenGround)
Grid

A predefined subset of Projections (i.e. Columns) from a Group.
Projections or Header An individual item of information such as Location ID or Sample Depth.  (May also be referred to as Fields in PowerBI, or Columns in a display sense).

Power Query Formats Used by OpenGround

OpenGround query functions use several common Power Query value types and structures. Understanding these formats will make it easier to build queries and troubleshoot errors.

The formats below are used throughout the examples in this guide.

Format

Description

Example
Text Words and Characters, enclosed in double quotation marks. "This is a text Example"
Number Numeric Values. 3.14159
List A collection of values, enclosed in curly brackets { }. {"A", "B", "C"} or {1, 2, 3}
Record A collection of Named Properties, enclosed by square brackets [ ]. 
    [
        Group = "LocationDetails",
        Header = "LocationType",
        Operator = "Equals",
        Value = "Geotech BH"
    ]
Table A collection of rows and columns. Most OpenGround query functions return data as a table. N/A
Function A reusable operation that accepts inputs and returns a result. OpenGround.QueryProject(...)


Choosing the Right Query Function

OpenGround provides several query functions. The best choice depends on what data you need and how much control you want over the results.

QueryProjectGrid

Use when:

OpenGround.QueryProjectGrid(
    Cloud,
    ProjectId,
    "Sample Information"
)

This is the recommended starting point for most users.

QueryProject

Use when:

OpenGround.QueryProject(
    Cloud,
    ProjectId,
    GroupName,
    Projections
)

Query

Use when retrieving data from multiple projects.

OpenGround.Query(
    Cloud,
    Projects,
    GroupName,
    Projections,
    Filters,
    Groupings,
    Orderings
)

Finding Available Data

Before building a report, it is often useful to see what projects, grids, and fields are available.

List Projects

OpenGround.GetProjects(Cloud)

List Available Grids

OpenGround.GetProjectGrids(
    Cloud,
    ProjectId
)

Using these discovery functions can save time and help avoid typing errors when building reports.

Returning Specific Fields

When creating reports, only return the fields you actually need.

Example

Projections = {
    "LocationDetails.LocationID",
    "LocationDetails.LocationType"
}

This query returns only the Location ID and Location Type fields.

Why This Matters

Grouping Results

Groupings allow you to combine rows together and perform calculations such as totals, averages, minimum values, and maximum values.

A Grouping is defined using a collection of records containing a Group, Header, and Aggregate value.

Grouping Syntax

Groupings = {
    [
        Group = "",
        Header = "",
        Aggregate = ""
    ]
}

Example

Groupings = {
    [
        Group = "LocationDetails",
        Header = "LocationType",
        Aggregate = "None"
    ],
    [
        Group = "SampleInformation",
        Header = "DepthBase",
        Aggregate = "Avg"
    ]
}

Aggregate Options

Aggregate Description
None Groups rows by the selected field.
Avg Returns the average value for each group.
Count Returns the number of rows in each group.
Max Returns the highest value in each group.
Min Returns the lowest value in each group.
Sum Returns the total value for each group.

Things to Remember

Filtering Results

Filters allow you to return only the rows that match specific criteria.

A filter is defined using a Group, Header, Operator, and Value.

Filter Syntax

Filters = {
    [
        Group = "",
        Header = "",
        Operator = "",
        Value = ""
    ]
}

Example

Filters = {
    [
        Group = "LocationDetails",
        Header = "LocationType",
        Operator = "Equals",
        Value = "Geotech BH"
    ]
}

Filter Example - Null Values

Filters = {
    [
        Group = "LocationDetails",
        Header = "Remarks",
        Operator = "Equals",
        Value = null
    ]
}

Text Filter Operators

These operators are typically used with text values. Text values should be enclosed in quotation marks.

Number and Date Filter Operators

These operators are used for numeric and date values. Numbers and dates do not need quotation marks.

Recommendations

Sorting Results

Orderings control the order in which rows are returned.

An ordering is defined using a Group, Header, and Ascending value.

Ordering Syntax

Orderings = {
    [
        Group = "",
        Header = "",
        Ascending = true
    ]
}

Example

Orderings = {
    [
        Group = "LocationDetails",
        Header = "LocationID",
        Ascending = true
    ]
}

Sort Direction

Value Description
true Sort in ascending order (A-Z, 0-9).
false Sort in descending order (Z-A, 9-0).

Multiple Sorts

More than one ordering can be supplied. Orderings are applied in the order they appear in the list.

Orderings = {
    [
        Group = "LocationDetails",
        Header = "LocationType",
        Ascending = true
    ],
    [
        Group = "LocationDetails",
        Header = "LocationID",
        Ascending = true
    ]
}

Complete Example

The example below connects to OpenGround, filters location data, and returns the results.

let
    Cloud =
        OpenGround.GetCloud(
            "BentleyUS",
            "eastus"
        ),

    Columns = {
        "LocationDetails.LocationID",
        "LocationDetails.LocationType"
    },

    Filters = {
        [
            Group = "LocationDetails",
            Header = "LocationType",
            Operator = "Equals",
            Value = "Geotech BH"
        ]
    },

    Results =
        OpenGround.QueryProject(
            Cloud,
            ProjectId,
            "LocationDetails",
            Columns,
            Filters,
            null
        )
in
    Results

Tips for Better Performance

Troubleshooting

Cannot Sign In

No Data Returned

Grid Not Found

Field Not Found

Query Runs Slowly