locked
Sum Buildin function is not considering negative numbers! RRS feed

  • Question

  • Hi!

    i am using the SUM buildin function of AS2005.

    it is not considering the negative numbers.

    There are some settings am I  forgetting?

    I am just adding, under measures, a new measure with aggrergateFunction=SUM and the database field "AMOUNT".

    In the db the amount is negative as well....

    Thx

    mcrisf

    Monday, November 6, 2006 6:29 PM

Answers

  • Mcrisf,

    SQL Server 2000 is where I have seen this issue.  The data entered the table using an Oracle ODBC driver.

    You can test by executing the following query (replace "value" with your column name):

     

    select value, 1*value, cast(value as VARBINARY)

    from table

    An example follows:

    select top 5
            quantity,
            'Times1' = 1 * quantity,
            'Varbinary' = cast(quantity as varbinary)
            from tablename where quantity < 0

    quantity   Times1       Varbinary                                                      
    ---------- ------------ --------------------------------------------------------------
    2          -2           0x0800000202000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000

     

    The fourth byte in the above data is 02 which is undefined.  The software retrieving the data; such as Query Analyzer or Analysis Services, will interpret the results.

    If the fourth byte is anything other than 00 (negative) or 01 (positive), then you are seeing the same issue.

    The fix is to multiply your column by 1.  This won't change any column values but it will reset the sign bit in the table.

    Earl

     

    Monday, November 6, 2006 9:48 PM
  • Could it be that your cube does not contain the missing members?

    A fact table should link to the dimension table through the dimension table's primary key.  A primary key cannot be null.

    I suppose that you could have a hierarchy in the dimension with null members.  Is that the case?

    Earl

    Tuesday, November 7, 2006 1:07 AM

All replies

  • Did you copy the data from an Oracle db using an Oracle ODBC driver?

    I have experienced cases where the sign bit in SQL Server was not set correctly when the data was loaded.  The Query Analyzer recognized the negative values but Analysis Services did not.  Please see the following link if this could be your case:

    http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_thread/thread/34b86aa29aa27331/8b06a30d09ef9d07?lnk=st&q=218AE3EB-60D7-4E5E-A2F1&rnum=2#8b06a30d09ef9d07

     

    Earl

     

    Monday, November 6, 2006 7:24 PM
  • thank you Earl!

    anyway i am pulling data from SQL Server2000. the field type is decimal(9,2). In query analyzer the amount is positive and negative.In AS2005 the sum function is only considering the positive and ignoring the negative ones....

    what should i do??

    thx

    Mcrisf

    Monday, November 6, 2006 7:34 PM
  • Mcrisf,

    SQL Server 2000 is where I have seen this issue.  The data entered the table using an Oracle ODBC driver.

    You can test by executing the following query (replace "value" with your column name):

     

    select value, 1*value, cast(value as VARBINARY)

    from table

    An example follows:

    select top 5
            quantity,
            'Times1' = 1 * quantity,
            'Varbinary' = cast(quantity as varbinary)
            from tablename where quantity < 0

    quantity   Times1       Varbinary                                                      
    ---------- ------------ --------------------------------------------------------------
    2          -2           0x0800000202000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000
    1          -1           0x0800000201000000

     

    The fourth byte in the above data is 02 which is undefined.  The software retrieving the data; such as Query Analyzer or Analysis Services, will interpret the results.

    If the fourth byte is anything other than 00 (negative) or 01 (positive), then you are seeing the same issue.

    The fix is to multiply your column by 1.  This won't change any column values but it will reset the sign bit in the table.

    Earl

     

    Monday, November 6, 2006 9:48 PM
  • I find out that the sum is not summing the values (in this case negative) when the dimension has 'null'.

    How to include these values in the sum?

    Thx

    mcrisf

    Tuesday, November 7, 2006 12:16 AM
  • Could it be that your cube does not contain the missing members?

    A fact table should link to the dimension table through the dimension table's primary key.  A primary key cannot be null.

    I suppose that you could have a hierarchy in the dimension with null members.  Is that the case?

    Earl

    Tuesday, November 7, 2006 1:07 AM
  • yes, this is the case...i have a hierachy with null members...

    what can i do to include these in the sum?

    Thx again

    mcrisf

    Tuesday, November 7, 2006 4:29 PM