When you reference a set alias that is referenced in a calculation in SQL Server 2005 Analysis Services, you may receive unexpected results

This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you reference a set alias that is referenced in a calculation in Microsoft SQL Server 2005 Analysis Services, you may receive unexpected results. This issue occurs when you reference the set alias outside the original alias context.
CAUSE
This issue occurs because a set alias that is referenced in a calculation should only be used in the context of its definition.
RESOLUTION
To avoid incorrect results, use set aliases in the context of their definition.
STATUS
This behavior is by design.
MORE INFORMATION
Set aliases that are defined in one calculation can be used in another calculation. However, these set aliases may not return correct results. In the following example, the four values that are returned for the [Sales Territory].[Sales Territory Group].[AliasName] calculated member are all “Pacific." However, the expected values of the set alias are as follows:
  • Europe
  • NA
  • North America
  • Pacific
Note The following sample code requires that you install the Adventure Work sample database in SQL Server 2005 Analysis Services.
WITH MEMBER [Sales Territory].[Sales Territory Group].[AliasName] AS     '[AliasSet].ITEM(0).NAME' SELECT        GENERATE(                 [Sales Territory].[Sales Territory Group].[All Sales Territories].CHILDREN,                 GENERATE(                         {[Sales Territory].[Sales Territory Group].CURRENTMEMBER} AS [AliasSet],                         {[AliasSet],[Sales Territory].[Sales Territory Group].[AliasName]}                     ,ALL),         ALL)    ON AXIS(0)FROM     [Adventure Works] WHERE ([Measures].[Reseller Sales Amount]) 
Because the Generate function that is used in this scenario is data driven, the context of the [Sales Territory].[Sales Territory Group].[AliasName] calculated member changes across the nested Generate function calls.

In the first iteration of the outer Generate function call, the coordinate is [Sales Territory].[Sales Territory Group].&[Europe]. This is also the value of the [AliasSet] set alias.

In the second iteration of the outer Generate function call, the coordinate is [Sales Territory].[Sales Territory Group].&[NA]. This is also the value of the [AliasSet] set alias.

In the third iteration of the outer Generate function call, the coordinate is [Sales Territory].[Sales Territory Group].&[North America]. This is also the value of the [AliasSet] set alias.

In the fourth and final iteration of the outer Generate function call, the coordinate is [Sales Territory].[Sales Territory Group].&[Pacific]. This is the final value of the [AliasSet] set alias.

After the final iteration of the outer Generate function call, the value of the calculated member is solved.
Properties

Article ID: 932871 - Last Review: 12/09/2015 09:53:17 - Revision: 1.2

Microsoft SQL Server 2005 Analysis Services

  • kbnosurvey kbarchive kbtshoot kbexpertiseadvanced kbsql2005as kbprb KB932871
Feedback