When I sum the 4 numbers (-550.83, 1690.65, 550.83, -1690.65), it should add up to zero. It displays correctly to 2 decimal places. I know that they are stored internally as a double. However, I use the number as a denominator in a calculated member and the test for zero fails. I have to use bounds such as > -00000001 and <0.00000001. The result is a very large number in billions if the filter is not used in the calculation.
Has anyone come across this or got a better suggestion? I can also use the VBA round function.
Thanks
If these are always going to be decimal numbers, as in the example above, does the changing the data type to "Currency" help?
http://msdn2.microsoft.com/es-es/library/ms129408.aspx
>>
SQL Server 2005 Books Online
DataType Element (ASSL)
Defines the data type of the associated element.
...
The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.
| Value | Description |
|---|---|
BigInt | A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB. |
Bool | A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB. |
Currency | A currency value ranging from -263 (or -922,337,203,685,477.5808) to 263-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB. |
...
>>
|||Thanks for the info and a possible solution. I have to bring it in as a double.
The source fact table is in account dimension format with only one measure DECIMAL (24,12). The measure can be pounds shipped, gross sales, or some marketing dollar allocation to 12 places. If I used currency, I would lose significant precision due to rounding to 4 decimal places.
I think the structure of my client's source fact table is already determining how I manage this. I wish SSAS would allow you to set the source format such as DECIMAL (24,12). I have the calculated measure isolated in one area, but it may impact performance when the client drills down hierarchies. Here is the code I used for the calculated measure.
IIF(([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) > -0.000000001 AND
([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]) < 0.000000001,
0, ([Measures].[EXPENSE DTL AMT], [WATERFALL DIM].[Waterfall].&[1]))
