Currency drops cents in BO but not in ISQL

I inherited a report that uses the following free hand SQL to return charges:

SELECT FE.description ‘StmtFeeType’,
Sum(SF.AmountCalc) ‘StmtFeeAmount’,
I.CompanyID,
I.StmtNum ‘InvoiceNumber’

When I run this query in BO, I get AmountCalc in even dollars (i.e., $94.00); when I run this query in SQL Server 6.5, I get AmountCalc in dollars and cents (i.e., $94.64). If I open the data cube and look at the raw data, it is in the dollar format ($94.00). There are two other fields that are defined as currency. One of them reports the cents correctly, the other drops the cents like in AmountCalc.

Has anyone seen anything like this before? Any help would be greatly appreciated.

Michael


Listserv Archives (BOB member since 2002-06-25)

Michael!

I had the same problem when i was working in DB2. The problem is, in BO we are using the standard format or with 2 digit decimal format (0.00). But in DB2, it is not fixed format… The format will be like 0.00000. For e.g. the value of each record may be like 1.002. If u sum of this column in BO, the result will be with 2 digit decimal rounded value. In Sql server it will calculate all the decimal part. So the results will be exactly with cents.
I suggest u change the format. I hope it will be helpful for u.

Ravi.

I inherited a report that uses the following free hand SQL to return charges:

SELECT FE.description ‘StmtFeeType’,
Sum(SF.AmountCalc) ‘StmtFeeAmount’,
I.CompanyID,
I.StmtNum ‘InvoiceNumber’

When I run this query in BO, I get AmountCalc in even dollars (i.e., $94.00); when I run this query in SQL Server 6.5, I get AmountCalc in dollars and cents (i.e., $94.64). If I open the data cube and look at the raw data, it is in the dollar format ($94.00). There are two other fields that are defined as currency. One of them reports the cents correctly, the other drops the cents like in AmountCalc.

Has anyone seen anything like this before? Any help would be greatly appreciated.

Michael

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com


Listserv Archives (BOB member since 2002-06-25)

Ravi,

Thanks for the response. I did some more investigating and this is what I found out:

In SQL Server, SFAmountCalc is in money format – using ISQL, it consistently returns amounts accurate to 2 decimal places. When querying this object using BusinessObjects, the data that is returned to the cube is the integer part only; that is, if you open the data cube and examine the raw data, there are no decimal places. So, it is not a rounding issue within BusinessObjects, but appears to be a result of the communication between SQL Server and BO through ODBC.

Michael


Listserv Archives (BOB member since 2002-06-25)