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:-
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.
The Projects parameter controls which projects are included in the Query.
The snippet below shows the three ways this parameter can be used.
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.
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.
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.
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"
The Groupings for the above example are shown below.
The following list of Aggregate commands can be used
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)
Number and Date Filters (Values do not need to be surrounded by quotes)
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).