SQL Server Performance

Calculated Member - Decimals being ignored

Discussion in 'Analysis Services/Data Warehousing' started by brian222, Feb 1, 2006.

  1. brian222 New Member

    Hello,

    I have a calculated member that composed of dividing two other calculated members. The calculation is incorrect and is ignoring the decimal places in both of the source measures of the equation.

    Example:

    Calculated Member 1 = 300.01
    Calculated Member 2 = 10.25
    Calculated Member 3 = (CM1 / CM2 ) = 30 INCORRECT!!!
    Answer should be = 29.269

    The MDX that I am using is as follows:

    Calculated Member 1 (Booked Amount YTD):
    Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Booked Amount])

    Calculated Member 2 (Units YTD)
    Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Units])

    Calculated Member 3 (Booked Amount / Unit)
    Measures.[Booked Amount YTD] / Measures.[Units YTD]

    This seems like a fairly common and straight forward type of calculation, taking the YTD amount / YTD units to come up with YTD amount per unit. When I create a calculated member with the hardcoded values of 30.01 / 10.25 I get the correct answer of 29.269. Am I missing something with formatting? Is this a bug in Analysis Services?

    Any help would be appreciated.

    Thanks in advance.

    Brian
  2. dineshasanka Moderator

    Declare @A float ,@B float, @c float
    Set @A = 300.01
    Set @B= 10.25
    Set @c = (@a / @b )
    select @c

    is giving me 29.269268292682927

    what is your data type of Calculated Member 1 and others



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  3. brian222 New Member

    The data types for both files are float in the RDMS. I've tried various formatting (includidng none) within Analysis Services for each and that doesn't seem to make a difference.
  4. ranjitjain New Member

    -Brian,
    Open the cube under Cube Editor (The cube in which you have those measures and created calculated member)

    Click on the both the measures and check the Data Type of them.
    Change them to Currency and then under data tab check the values of all three measures.

    Because the calculated Member takes its data type from the measures.

Share This Page