Often, you want to limit one set to another by using the feature self-exists, but without the members of the second set present in the results, in these situations, the function Exists can be used.
Exists ({Set1}, {Set2})
Function Exists job self-exists to limit the members of the first set to those observed in combination with the members of the second based on relationships in their common dimension. Although the second set exerts an influence on the membership of a set, the returned set contains only members of the first set.
Consider the following example:
SELECT {([Date]. [Calendar]. [CY 2002]), ([Date]. [Calendar]. [CY 2003]), ([Date]. [Calendar]. [CY 2004])} * {( [Measures]. [Reseller Sales Amount]), ([Measures]. [Internet Sales Amount])} ON COLUMNS, {[Product]. [Category]. [Category] .Members} * {[Product]. [Color] . [Color] .Members} ON ROWS FROM [Step-by-Step] WHERE ([Geography]. [Country]. [United States], [Product]. [Subcategory]. [Mountain Bikes]);
Let's look at the results:
When the Mountain Bikes SubCategory member is assigned to the where clause, it limits the entire row axis to the Category and Color combinations associated with the Mountain bikes member. It also influences the produced tuples such that each returned cell is associated with the member of the Mountain Bike SubCategory.
To illustrate this, consider the first cell in the cell set, which is worth $ 3,927,415.56.
The query produces the following tuple to access this cell:
([Date]. [Calendar]. [CY 2002], [Measures]. [Reseller Sales Amount], [Product]. [Category]. [Bikes], [Product]. [Color]. [Black], [Geography ]. [Country]. [United States], [Product]. [SubCategory]. [Mountain Bikes])
Using the function Exists, you can use the Mountain Bikes member to limit all Category and Color members of the row axis to those associated with the Mountain Bikes member but without the presence of the Mountain Bikes member in the tuples formed by the query.
Now modifying the query to limit the entire row axis to member combinations associated with the Mountain Bikes SubCategory member, but without limiting the values returned to Mountain Bikes.
SELECT {([Date]. [Calendar]. [CY 2002]), ([Date]. [Calendar]. [CY 2003]), ([Date]. [Calendar]. [CY 2004])} * {( [Measures]. [Reseller Sales Amount]), ([Measures]. [Internet Sales Amount])} ON COLUMNS, EXISTS ({[Product]. [Category]. [Category] .Members} * {[Product]. [ Color]. [Color] .Members}, {([Product]. [Subcategory]. [Mountain Bikes])}) ON ROWS FROM [Step-by-Step] WHERE [Geography]. [Country]. [United States] ;
Let's run the query:
The cell set has the same structure as before, but notice that the values have changed. The first cell in the cell set now has a value of $ 6,314,977.53 whereas it had a value of $ 3,927,415.56 in the previous query. This is because the tuple associated with the cell no longer contains the member of the Mountain Bikes SubCategory.
([Date]. [Calendar]. [CY 2002], [Measures]. [Reseller Sales Amount], [Product]. [Category]. [Bikes], [Product]. [Color]. [Black], [Geography ]. [Country]. [United States])
You can check this by using the previous tuple to extract this cell from the cube.
The following query retrieves the cell using the tuple identified in the previous step.
SELECT FROM [Step-by-Step] WHERE ([Date]. [Calendar]. [CY 2002], [Measures]. [Reseller Sales Amount], [Product]. [Category]. [Bikes], [Product]. [Color]. [Black], [Geography]. [Country]. [United States]);
After the execution of the query, we see that the value corresponds to that of the first cell of the set of cells of the previous query.
Leave a comment