Sunday, August 3, 2014

UNION

The UNION functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
UNION will give all the members of SET1,SET2…. SETn. By default it will remove any duplicates.
SELECT UNION   (
        [Date].[Calendar Year].[CY 2010]:[Date].[Calendar Year].[CY 2014] // SET 1
        ,
            {
                [Date].[Calendar Year].[CY 2012]
            } //SET 2
        ,
            {
                [Date].[Calendar Year].[CY 2013]
            } //SET 3
     ) ON COLUMNS,
[Measures].[Sales Amount] ON ROWS
FROM [Adventure Works]
This will give use:
image
If we add the ALL flag duplicates will be visible:
SELECT UNION   (
        [Date].[Calendar Year].[CY 2010]:[Date].[Calendar Year].[CY 2014] // SET 1
        ,
            {
                [Date].[Calendar Year].[CY 2012]
            } //SET 2
        ,
            {
                [Date].[Calendar Year].[CY 2013]
            } //SET 3
        , ALL
   ) ON COLUMNS,
[Measures].[Sales Amount] ON ROWS
FROM [Adventure Works]
image
Have Fun And Till Next Time

No comments:

Post a Comment