Spanish Version
Advanced Selection on Axis
Scope
Product: GXplorer, GXquery
Introduction
A new option is added to this new version to be selected at the time of defining filters on a query dimension or axis attribute: filtering the dimension values with the result of other query (queries). This new feature increases significantly our queries power and allows for a data withdrawal producing results much more suitable for our requirements.
Description
Until now, there were several ways of specifying filters over dimensions and axis attributes, but they always had constant values (Year = 2006, Month between June and December, etc.) or semi-variables (Maximum, Minimum, Ranking). We call them semi-variables since possibilities are quite limited in the definition of these filters.
Frequently, we want to apply filters that are not constant and that available functions (Maximum, Minimum, Ranking) do not allow us specifying since they require a more complex search in the metadata. Summing up, what we need is another query or several queries to search for the filter values.
In this version, we add an option to the filter types that can be defined for a dimension or axis attribute: Queries. You will be able to indicate one or several queries to define the filter, which will be called sub-queries.
Let's use an example to illustrate this new feature by defining the following query step by step.
Taxes and Discounts per Country, Brand and Model, but for the countries that have sold more than $100000 in year 2005.
Step 1
Select the query measures and dimensions.
Step 2
Then, edit the properties of the Country dimension and indicate that you will be applying a Query type filter.
Sub-queries defined to obtain the filter values in the dimension will be listed in the box. If more than one sub-query is indicated, you can indicate if the values to be taken by the filter will be the result of the union or the intersection of the values returned for each sub-query.
Step 3
By pressing the Add button the window for the edition of a new sub-query will be opened. Here, you must select the sub-query measure and filters. In our example, the measure will be Sales and the filtering will be performed by the Year dimension.
Sub-queries definition differs a little from common queries definition. Sub-queries can be considered as a query with just one measure, one dimension and filters by other dimensions. The dimension of such sub-query is implicit and is the dimension for which we are defining the filter in the main query; therefore there is no need to select it. The measure many be anyone (provided it is related to the dimension) and you can apply any aggregation and apply the normally defined Filters by Dimensions and/or Conditions. Lastly, you can define filters with other dimensions. These filters may be any type, even Query type.
Step 4
Lastly, in the example you must setup the filters over the sub-query measure: Sales > $, and the values that the filter by Year will take: 2004.
Considerations in attributes metadatas
When using this new feature to define filters over axis attributes in an attributes metadata, you must take into account the following considerations:
- The advanced condition of the main query DOES NOT apply to the sub-queries.
- The advanced condition of a sub-query applies ONLY to the sub-query.
- You cannot indicate the No Aggregation aggregation for a sub-query data attribute.
This feature is related to Advanced Selection on Data.