Advanced Selection on data
Product: GXplorer, GXquery
This version adds a new way of defining conditions over a query measure or data attribute: specifying as constant of a condition the result of another query. This new feature raises significantly our queries power and allows using data to obtain results that are much more suitable to our requirements.
Until now, to specify the constants that will make up the conditions specified for a measure or data attribute we could indicate fixed values or a parameter whose value was required the moment the query is executed.
Sometimes, we may not know the value of these constants beforehand, not even the moment the query is executed, since this value is a complex combination of other measures.
Taking into account this last case at the time of defining a query, this new version adds a new way to indicate a constant for a condition: Queries. It will be possible to set that the constant(s) that will make up a condition expression is/are the result of a query, which will be called subquery.
Let's see an example to illustrate the new feature, defining the following query step by step:
Sales by Country and City; but we only want those cases where Sales exceeded Purchases in January 2003.
We select the query measures and dimensions.
Then, we edit the Sales measure properties and add a condition with operator >= and the constant will be a query. For this, we must select the Query checkbox appearing under the box to enter the constant.
By pressing the Add/Edit query button a new window to edit the subquery corresponding to the constant will be displayed. There, we must select the subquery measures and filters. In our example, the measure will be Purchase and the filters will be the Year and Month dimensions.
The definition of subqueries for measures is a little different from the common queries definition. Subqueries can be seen as a query with just one measure, without axis dimensions and with filers by other dimensions. The measure can be anyone and we can apply any aggregation and Filters by the usually defined Dimensions and/or Conditions. We can also define filters with other dimensions, which can be of any type. The value of the selected measure applying the indicated filters is the one to be used as constant value in the conditions of the main query.
Considerations in attributes Metadatas
When using this new feature to define filters on data attributes in an attribute metadata, we must take into account the following considerations:
- The advanced condition of a subquery applies ONLY to the subquery.
- We cannot indicate the No Aggregation aggregation for a subquery data attribute.
This feature is related with the Advanced Selection on Axis.