Bug Report B133441
Visible to All Users

OLAP - Calculated Member returns incorrect results when filtering

created 16 years ago

Hi
We have a cube that has a calculated measure: 'Cost Contribution' defined. Cost contribution is basically revenues from each employee divided by the cost of his/her group. We can track revenues for every employee whereas costs can only be allocated to managers.
In a simple example we have the following employee structure

  • Company
       + Jacobs
       + Miller
          + Gonzales
          + Stevens
    Cost contribution for Stevens expresses Stevens' revenues compared to the costs of Miller and the hard-coded MDX formula is this:
    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Contribution]
     AS (IIF(ISANCESTOR([Employee].[Employee].[Jacobs], [Employee].[Employee].CURRENTMEMBER) Or
                        [Employee].[Employee].[Jacobs] = [Employee].[Employee].CURRENTMEMBER,
                        [Measures].[Revenue] / ([Employee].[Employee].[Jacobs], [Measures].[Cost]),
                        [Measures].[Revenue] / ([Employee].[Employee].[Miller], [Measures].[Cost]))
        ),
    FORMAT_STRING = "Percent",
    NON_EMPTY_BEHAVIOR = { [Measures].[Revenue]},
    VISIBLE = 1 ;
    If no filter has been applied to the employee hierarchy the calculated measure works as expected. However if we filter for example for only Stevens, PivotGrid shows infinity instead of the correct value.
    In Excel 2007 this works fine so we assume that the problem is not in our calculated member definition.
    We believe this problem origins from your filter design. Somehow filtering for members can have an impact on the result of calculated measures (that might reference other member's measures in their formula).
    Steps to Reproduce:
  1. Restore Analysis Services backup
  2. Drag the 'Employee Hierarchy' to the rows area.
  3. Drag the 'Cost Contribution' measure to the data area
  4. Expand the employee hierarchy to the lowest level (employee level 2)
    -> Cost contribution for Stevens is 0.4 (6500 revenues against 16450 costs in Miller's unit).
  5. Filter only for Stevens
    -> Cost contribution for Stevens gets infinity and the value changes simply because we filtered the employee.
    Actual Results:
    Stevens' cost contribution gets infinity when filtering for member Stevens. This is wrong since it is 0.4.
    Expected Results:
    Filtering for members should not change the results of calculated members.
    Stevens' cost contribution should still be 0.4 also when filtered for Stevens only.
Comments (2)
KM KM
Konstantinos Manthos 16 years ago

    Here are the screenshots showing the problem.

    DevExpress Support Team 16 years ago

      Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

      Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.