BUG: Office XP PivotTable Query Fails With Member Property in Filter Expression of MDX (316763)



The information in this article applies to:

  • Microsoft Office XP Web Components

This article was previously published under Q316763

SYMPTOMS

When you apply a valid multidimensional expression (MDX) to the CommandText property of the PivotTable component, the query fails if the filter expression contains a member property. For example, the following MDX fails:
SELECT
       {[Measures].defaultmember} ON COLUMNS,
       {
           Filter( {[Store].[Store Name].Members},   
                    ([Store].CurrentMember.Properties("Store Manager") = "Maris"))
       }
       DIMENSION PROPERTIES [Store].[Store Name].[Store Manager] ON ROWS
   FROM sales
				

CAUSE

The PivotTable fails to process this MDX due to a problem with the parsing of the axis specification. When the axis specification contains the "Properties" keyword, the PivotTable assumes that it represents the start of the "DIMENSION PROPERTIES" section; this assumption is incorrect when a member property appears in the filter expression.

RESOLUTION

To work around this problem, remove the "Properties" keyword from the filter expression and instead use a calculated member for the condition. For example, you can use the following MDX in place of the MDX in the "Symptoms" section:
WITH
    MEMBER [Measures].[Filter Condition] AS
    'iif(Store.CurrentMember.Properties("Store Manager") = "Maris", 1, null)' 
 SELECT
     {[Measures].defaultmember} ON COLUMNS,
     {
         Filter( {[Store].[Store Name].Members},
                   [Filter Condition] =1)
     }
     DIMENSION PROPERTIES [Store].[Store Name].[Store Manager] ON ROWS 
 FROM sales
				

STATUS

Microsoft has confirmed that this is a problem in Office XP Web Components.

Modification Type:MajorLast Reviewed:1/30/2002
Keywords:kbbug kbDSupport kbOfficeWebPivot KB316763