How to refer to a Grid by Attribute when using TQL
When creating Projects, Project Templates, and smaller elements of complex calculation models, it is a common occurence that an Excel Indicator references the results of a Grid Overlay. However, depending on the situation, it is easy to encounter situations where the common GRID_IS_ syntax of a TQL clause is not flexible enough. This can happen for example when the Excel Indicator is used in a different Project in which the relevant Grid Overlay has a different ID. Or where multiple such Indicators exist which may want to use the same Grid Overlay for their information, but may also need to look at different ones.
To allow for more flexible connections between Excel Indicators (or Excel Panels), the GRID_WITH_ATTRIBUTE_ syntax can be used. This will use a "connecting Attribute" to define which Grid Overlay provides the intended input to the Excel. This will require a small amount of mutual configuration but will in most cases allow for (re)wiring Grid Overlays and Excel-based data quickly and in a comprehensible manner.
The following steps will assume the Attribute connecting the Grid Overlay and the TQL statement is CONNECTING_ATTRIBUTE.
- Add the connecting Attribute to the Overlay by adding an Attribute with the following configuration:
Name: The name of the Attribute ( CONNECTING_ATTRIBUTE)
Value: Any positive value ( 1 ) - In the Query Tool:
Create a query of the form SELECT_GRIDAVG_WHERE_GRID_WITH_ATTRIBUTE_IS_..., where the elipses are the desired Attribute ( CONNECTING_ATTRIBUTE )
Execute the query, and confirm the result is the average of all grid values of the Overlay with the Attribute. - In the Excel (or other location of the TQL statement), ensure:
All TQL statements referring to the Overlay use the GRID_WITH_ATTRIBUTE syntax.
- Add the connecting Attribute to the Overlay by adding an Attribute with the following configuration:
Notes
- Only one Grid Overlay should have the "connecting Attribute". Multiple Grid Overlays with the same "connecting Attribute" would create uncertainty about which input it intended, and will trigger a warning instead.
- Determining an effective name for the "connecting Attribute" is important, and subject to the following considerations:
- It should be readible/recognizable to the theme of model it is related to. For example, relating to sight on trees it would make sense to include the words SIGHT_ON_TREES in the Attribute name.
- It should be unique across functionalities so as to prevent undue collisions. For example, naming the Attribute "SURFACE_WATER" would make sense for an Overlay showing/computing water, but is already an Attribute automatically added to a Water Overlay, which would then either automatically connect with the TQL statement, or collide when multiple exist.
- A general guideline is to name the attribute "OVERLAY_[calculation_model]". E.g. OVERLAY_SIGHT_ON_TREES, OVERLAY_AVERAGE_SHADE, etc.