September 23, 2004 at 5:57 am
Answer – When it’s a calculated member in Analysis Services of course.
I’ve got a cube that among other measures are Sales (currency) and Item packs sold (Decimal). Therefore, Selling price per pack is Sales/ packs Sold, simple. We sell part packs, so it is possible to sell .5 of a pack.
In my example we sold half a pack for £2 meaning that a full pack = 2/.5 = £4, however, when the packs sold is less than 1 MSAS multiplies its answer by 10,000! and this happens every time, even in a single record single dimension cube, try it yourself (and let me know your result).
It turns out to be the data type Currency. Change it to Decimal and it works. MSAS seems to dislike dividing currency by values between 0 and 1.
Could anyone tell me if this is the same in MSAS 2005? Also, how do you go about telling Microsoft about these things?
September 27, 2004 at 8:00 am
This was removed by the editor as SPAM
September 27, 2004 at 9:25 pm
Is your underlying datatype (ie the datatype within the DB) currency for the Sales measure? What datatype have you selected for the measure itself? Even though your underlying data type can be currency, quite often (always?) AS uses double to store the values (and then just sets the display format to currency).
I think the issue is caused by using the currency datatype (for the data values & aggregations in AS). A quick look at the ado datatypes shows that it's stored as an int *scaled by 10,000* - see below
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp
| adCurrency | 6 | Indicates a currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an eight-byte signed integer scaled by 10,000. |
So, how to fix it? Choose a datatype other than currency for storage of the measure (make sure it's large enough to hold the aggregated values, which is prob why the default for currency values is double, keeping in mind that double may not be accurate enough depending on the accuracy requirements for dollars and cents or pounds and pence) and set the display format to currency.
Cheers,
Steve.
September 28, 2004 at 3:27 am
The underlying datatype and the AS datatype were both double.
I figured the same solution, it was just a reason why it was happening that was eluding me.
Cheers.
Howard.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply