The OpenGround.Query command collects the data from OpenGround and inserts it into a data object in Power BI.

An example use for the command is shown below. 

(Click on image to enlarge)

The command has seven parameters which are detailed below:-

1. Cloud – Name and details for the OpenGround Cloud to connect to.
2. Projects – Project, or list of projects, to query
3. Group – The Primary group to be queried.
4. Projections – The list of columns to return
5. Filters – Conditions that the data must meet to be returned
6. Ordering – the Order the data is to be returned.
7. Groupings – ways in which the data within each column is to be displayed

 

Cloud

The Cloud Variable comprises of three items, CloudID, Cloud Name and Region.  It is usually set up as a query variable within the report and then referenced as using the query name.  All the example reports are set up using this method and the Query and referenced parameters can be copied from any of these reports.

If the report is for a single cloud then the parameters can be hardcoded into each query.

Projects

The Projects parameter controls which projects are included in the Query. 

The snippet below shows the three ways this parameter can be used.

(Click on image to enlarge)

• Include a comma-separated text list
• Be left blank to include all projects
• Include reference to a Power BI Report Parameter (LINK XXX) so the user can change it using the interface

Group

The Group Variable defines the group name in the Query. This is the group name as it appears in the Model Manager in OpenGround.  Note the Groupname is likely to be different from the tab name as it appears in OpenGround so it is important to double-check the model manager to ensure you are using the right name. 

(Click on image to enlarge)

Projections

The Projections variable defines which columns to return. The column list a comma-separated list of groupname.header values as shown below.

The Heading name must be the same as it appears in the Model Manager in OpenGround and not the column name as it appears in the user interface. 

(Click on image to enlarge)

Projections can reference Parent tables or linked tables using the same construct, for example in the above example the LocationID is referenced from the LocationDetails Group.

The Projections variable should be left blank if Groupings variable is used.

Groupings

The Groupings parameter is a collection of Groupby values.  Each Groupby value has a Group, Header and Aggregate value.

Grouings Parameter has the syntax {[Group=””,Header=””, Aggregate=””],[Group=””,Header=””, Aggregate=””],etc). 

All headings must appear in the Groupings variable. Headings can appear more than once but must have different aggregate command

Column names returned are returned as "Header & Aggregate"

 

(Click on image to enlarge)

The Groupings for the above example are shown below.

(Click on image to enlarge)

The following list of Aggregate commands can be used

• None – Grouped By
• Avg – Average Value for each group
• Count – The number of rows per group
• Max – The maximum value for the heading per group
• Min – The Minimum value for the heading per group
• Sum – The total of all values for the heading per group

Filter

The Filter parameter is a collection of Filterby values.  Each FilterBy value has a Group, Header, Operator and Value.

Filters Parameter has the syntax {[Group=””,Header=””, Operator=””, Value=””],[Group=””,Header=””, Operator=””, Value=””],etc). 

 

Filters =

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

List of Filter Operators

Text  Filters (Values need to be surrounded by quotes)

• None
• In
• NotIn
• Like
• NotLike
• CaseInsensitiveLike
• NotCaseInsensitiveLike
• Equals
• NotEquals
• CaseInsensitiveEquals
• NotCaseInsensitiveEquals
• BeginsWith
• NotBeginsWith
• CaseInsensitiveBeginsWith
• NotCaseInsensitiveBeginsWith
• EndsWith
• NotEndsWith
• CaseInsensitiveEndsWith
• NotCaseInsensitiveEndsWith
• InSubQuery
• Contains
• NotContains

Number and Date Filters (Values do not need to be surrounded by quotes)

• LessThan
• NotLessThan
• LessThanOrEqualTo
• NotLessThanOrEqualTo
• GreaterThan
• NotGreaterThan
• GreaterThanOrEqualTo
• NotGreaterThanEqualTo

 

Orderings

The Orderings parameter is a collection of Orderby values.  Each OrderBy value has a Group, Header, and Ascending.

Orderings Parameter has the syntax {[Group=””,Header=””, Ascending= true],[Group=””,Header=””, Ascending=false],etc).