I have two cubes that show incorrect values for a measure at all levels. For example, one customer shows 22 as a shipped quantity value in the AS cube, but I execute a SQL query for the same customer with a sum function and it shows a value of 10.
Let me give you the background please.
The measures are calculated with the aggregrate function of SUM. All fields that I am using as measures are Decimal(20,10) in SQL. To load the values into the SQL table I have a UDF that converts a varchar field to a decimal. The quantity conversion UDF code is below:
CREATE FUNCTION EuroQtyFormatting (@varqty varchar(10))
RETURNS decimal(20,10) AS
Declare @Qty decimal(20,10)
set @qty = Cast(Replace(Replace(Replace(@varqty, ',', '*'), '.',''), '*', '.') as decimal(20, 10))
The quantity field that I am converting is a varchar(20) field. I realize that that might be an issue, but when I query the table the varchar quantity field value appears to be the same as the decimal quantity field.
Any assistance would be greatly appreciated.