Thursday, July 31, 2014

Except

The Except functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).

Except will give all the members of SET1 which are not in SET2. By default it will remove any duplicates which are in SET1.

SELECT
{
    (    [Date].[Calendar Year].&[2013]
        ,[Date].[Month of Year].Children    )
}  ON COLUMNS,
{
    ( Except
        ( { [Product].[Product Categories].[All].Children }, //SET1
            { [Product].[Product Categories].[Components]} //SET2)
        ,[Measures].[Sales Amount]
    )
}  ON ROWS
FROM   [Adventure Works]

Will give us:

image

If we add a duplicate to SET1 we still get:

SELECT
{    (   [Date].[Calendar Year].&[2013]
        ,[Date].[Month of Year].Children    )}  ON COLUMNS,
{     (  Except
        (
            {  [Product].[Product Categories].[All].Children   
             , [Product].[Product Categories].[All].Bikes }, //SET1
            { [Product].[Product Categories].[Components]} //SET2
           )
        ,[Measures].[Sales Amount]
    )
}  ON ROWS
FROM
   [Adventure Works]

image

If we add the ALL flag duplicates will be visible:

SELECT
{
    (     [Date].[Calendar Year].&[2013]
        ,[Date].[Month of Year].Children)
}  ON COLUMNS,
{
    (
        Except
        (
            {  [Product].[Product Categories].[All].Children   
             , [Product].[Product Categories].[All].Bikes }, //SET1
            { [Product].[Product Categories].[Components]} //SET2
           ,ALL
        )
        ,[Measures].[Sales Amount]
    )
}  ON ROWS
FROM
   [Adventure Works]

image

Have Fun And Till Next Time

No comments:

Post a Comment