If I run the following there’s a whole section of Customers that are (null) internet sales amount for both 2007 and 2008 – why is this? How do I use EXISTS
to filter to just customers who have results for the years in the select
note: The choice of years could vary so InitialSet
needs to be context aware hence I've used [Date].[Calendar Weeks].CURRENTMEMBER
withing the EXISTS function.
WITH
SET [InitialSet] AS
EXISTS(
{[Customer].[Customer].[Customer].MEMBERS},
[Date].[Calendar Weeks].CURRENTMEMBER,
'Internet Sales'
)
SET [OrderedSet] AS
NONEMPTY
(
ORDER
(
[InitialSet],
[Measures].[Internet Sales Amount],
BDESC
)
,([Measures].[Internet Sales Amount],[Date].[Calendar Weeks].CURRENTMEMBER)
)
SELECT
{
[Date].[Calendar Weeks].[Calendar Year].&[2007],
[Date].[Calendar Weeks].[Calendar Year].&[2008]
} ON 0,
[OrderedSet] ON 1
FROM [Adventure Works]
WHERE (
[Measures].[Internet Sales Amount]
);
Sets are evaluated by Analysis Services by taking the WHERE clause into account, but not using any row or column settings. If you would like to evaluate the sets only for 2007 and 2008, you have to state that.
Actually, the [Date].[Calendar Weeks].CURRENTMEMBER
in your first set will refer to the default member of the [Date].[Calendar Weeks]
hierarchy, which is the All
member.