Slice on a Calculated Member that uses the Aggregate function
By
Published Mar 15 2019 12:01 PM 350 Views
Brass Contributor

# Slice on a Calculated Member that uses the Aggregate function

First published on MSDN on Jul 08, 2013

Applies to: SQL Server 2005 Analysis Services, SQL Server 2008 Analysis Services, SQL Server 2008 R2 Analysis Services, SQL Server 2012 Analysis Services Multidimensional Model

A user reported seeing two different behaviors by running two slightly different MDXs statement with a where clause on a calculated member that used the Aggregation function. In this post, we will try to look at the 2 queries in question and explain the difference in behavior with the Adventure works  Sample DB.

Query 1

```WITH
MEMBER [Product].[Category].[x] AS Aggregate({[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
SELECT
(
[Measures].[Internet Sales Amount]
,{
[Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]
}
) ON 0
,{
,[Product].[Product Categories].[Category].ALLMEMBERS
} ON 1
WHERE
[Product].[Category].[x];
```

Result:

 Internet Sales Amount Internet Sales Amount Internet Sales Amount Internet Sales Amount CY 2005 CY 2006 CY 2007 CY 2008 All Products \$3,266,373.66 \$6,530,343.53 \$9,791,060.30 \$9,770,899.74 Accessories (null) (null) \$293,709.71 \$407,050.25 Bikes \$3,266,373.66 \$6,530,343.53 \$9,359,102.62 \$9,162,324.85

Query 1 produced a meaningful and expected results.

Query 2

```WITH
MEMBER [Product].[Category].[x] AS
Aggregate(existing{[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
SELECT
(
[Measures].[Internet Sales Amount]
,{
[Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]
}
) ON 0
,{
,[Product].[Product Categories].[Category].ALLMEMBERS
} ON 1
WHERE
[Product].[Category].[x];
```

Result:

 Internet Sales Amount Internet Sales Amount Internet Sales Amount Internet Sales Amount CY 2005 CY 2006 CY 2007 CY 2008 All Products \$3,266,373.66 \$6,530,343.53 \$9,652,812.33 \$9,569,375.10 Accessories \$3,266,373.66 \$6,530,343.53 \$9,652,812.33 \$9,569,375.10 Bikes \$3,266,373.66 \$6,530,343.53 \$9,652,812.33 \$9,569,375.10 Clothing \$3,266,373.66 \$6,530,343.53 \$9,652,812.33 \$9,569,375.10 Components \$3,266,373.66 \$6,530,343.53 \$9,652,812.33 \$9,569,375.10

In query 2, the only thing the user added was the “existing” function (see the yellow highlight above).The behavior of the query 2 was dramatically different. The result values were repeated for each product category in any given year.

Explanation

If a calculated member expression was “aggregate (<static set>)”, it would act like replacing the where clause with that static set.

```With member
[Product].[Category].[c1] as  aggregate (A, B, C)
Select
X,
Y
From <cube>
Where [Product].[Category].[c1]
```

It acted like:

```Select
X,
Y
From <cube>
Where ({A, B, C})
```

In Query 1, the set {[Product].[Category].[Bikes],[Product].[Category].[Accessories]} was static. The query behaved like this:

```SELECT
(
[Measures].[Internet Sales Amount]
,{
[Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]
}
) ON 0
,{
,[Product].[Product Categories].[Category].ALLMEMBERS
} ON 1
WHERE
({[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
```

And it produced the same result as we saw in Query 1.

 Internet Sales Amount Internet Sales Amount Internet Sales Amount Internet Sales Amount CY 2005 CY 2006 CY 2007 CY 2008 All Products \$3,266,373.66 \$6,530,343.53 \$9,791,060.30 \$9,770,899.74 Accessories (null) (null) \$293,709.71 \$407,050.25 Bikes \$3,266,373.66 \$6,530,343.53 \$9,359,102.62 \$9,162,324.85

What if the set was not static? The replacement did not take place if the set was not static and the set would get computed into numeric values as shown below.

```WITH
MEMBER [Product].[Category].[x] AS Aggregate(existing{[Product].[Category].[Bikes],[Product].[Category].[Accessories]})
SELECT
(
[Measures].[Internet Sales Amount]
,{
[Date].[Calendar Year].[CY 2005] : [Date].[Calendar Year].[CY 2008]
}
) ON 0
,[Product].[Category].[x] ON 1