Please read the Part1 of the same series to get the continuity
Usual confusions
-
Exists and “Auto exists” are nearly same only difference is Exists function don’t display the second set .
Actually no. One set the context other doesn’t
SELECT [Measures].[Reseller Sales Amount] ON 0
, Exists( [Product].[Subcategory].[Subcategory].Members
,[Product].[Category].[Clothing]
)on 1
FROM [Adventure Works]
where [Product].[Category].[Bikes]Above query will produce no results because we are slicing by two different members(Bikes & Clothing) at the same time. But the the query allows you to use the same dimension hierarchy in both rows Axis and slicer axis.
But the below query will produce errors, because it sets the context
SELECT [Measures].[Reseller Sales Amount] ON 0
, [Product].[Subcategory].[Subcategory].Members *
[Product].[Category].[Clothing]
on 1
FROM [Adventure Works]
where [Product].[Category].&[1]
![image_thumb[22]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXn3xr4K38ezUjUT6qzec-kEv86QWYpWxqvaB9rfHcAj7KOZOckEcd_tQCIyVdQxnLfB6pierwHqaPg7NpfOBnPGn3jlQn8uh8HZE7n5J-VaBRNKG1Yd0HiG5AisY6L4b5lkERd6dOeXU/s1600-h/image_thumb222.png "image_thumb[22]")
Existing keyword is just an extension of Exists, we can achieve the similar results with exist and currentmember function
Yes, But use of existing will improve the maintainability of code
Both the below codes will produce the same results
with member countofsubcategory as
count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]
with member countofsubcategory as
count( exists( [Product].[Subcategory].[Subcategory].Members
,[Product].[Category].currentmember ))
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]
DO we have not exists function ?
No. But this can be simulated with except function
The below query produces 8 rows of data
SELECT [Measures].[Reseller Sales Amount] ON 0
, Exists( [Product].[Subcategory].[Subcategory].Members ,
[Product].[Category].[Clothing]
)
on 1
FROM [Adventure Works]
With the except function in the below query it produces the opposite set with 30 rows
SELECT [Measures].[Reseller Sales Amount] ON 0
,EXCEPT([Product].[Subcategory].[Subcategory].Members,
Exists( [Product].[Subcategory].[Subcategory].Members ,
[Product].[Category].[Clothing]
)
)
on 1
FROM [Adventure Works]
![image_thumb[24]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw0neVX5I3-7woPCzs0zc1VtY6Xzp1v_BRxCSjy2NTh1xaO8MvlIry8c6Tl97bhGuDUF6lvfmOH7Vcn-Pr1cKt1sDjqectIJzqpfeKX1E76zUDPBLY81RbuvpLoN-YERyg5m-wdL-3Djc/s1600-h/image_thumb242.png "image_thumb[24]")
![image_thumb[26]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyE06uV8IeKYDEJu_j56RWoif305UJjc_JPrd4wggXfRE13o2PN-cbOQbjvlILzeC4dqzBHO3esSJrTlE4-fMAkAj3n9vNHz8TId890hycIqFuFTXN2ArCYOm9M9pts_93veJmv4qj_vk/s1600-h/image_thumb262.png "image_thumb[26]")
Generate function forces it’s own context against existing keyword
Check the below query. It is clear that existing keyword uses the generate function’s context and that is why we got 3 subcategory against all category in the result set
with member countofsubcategory as
Generate ([Product].[Category].[Bikes]
, count( existing [Product].[Subcategory].[Subcategory].Members)
)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]This is an another proof that existing keyword is simply an extension of Exists function
![image_thumb[28]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyttpm1vX3Nias-ZUmmV_nzOzJ7Btg7tdNmVQ8sJW-hUHaqvZabdqJ5IpdYN9lfIewJ8aPGAkmEdEOgQbLVvoUfoM_qYSFmpVvHWKlF6ML7qQPYWwLxi3SJTaOizpjBaxeEtcBX37mFWs/s1600-h/image_thumb282.png "image_thumb[28]")
Happy Reading
