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

Article translations Article translations
Article ID: 932871 - View products that this article applies to.
Expand all | Collapse all

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: March 22, 2007 - Revision: 1.2
APPLIES TO
  • Microsoft SQL Server 2005 Analysis Services
Keywords: 
kbtshoot kbexpertiseadvanced kbsql2005as kbprb KB932871

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com