I must be misinterpreting the function EXISTS
.
Why does the following return lots of customers with null
revenues?
What function should I be using or am I right to use EXISTS
but need to use it differently?
WITH SET [CustomerSet] AS
EXISTS(
[Customer].[Customer].MEMBERS,
(
[Date].[Date - Calendar Month].[Calendar Month].&[201312],
[Measures].[Revenues])
)
select
[CustomerSet] on columns
from [ourCube]
Ok - EXISTS
basically mimics auto-exists
behaviour without having to include two levels of a cross-join in the resulting cellset. If the two arguments are not from the same dimension then auto-exist behaviour does not occur - this is the reason for the failure of my original script. There is an optional third argument, which is very useful in this situation, and creates a context using a chosen measure group for auto-exist behaviour:
WITH SET [CustomerSet] AS
EXISTS(
[Customer].[Customer].MEMBERS,
[Date].[Date - Calendar Month].[Calendar Month].&[201312],
"Revenues Measure Group"
)
SELECT
[CustomerSet] ON COLUMNS
FROM [ourCube]