SQLTeam.com | Weblogs | Forums

Convert Char to Decimal in Analysis Cubes Calculated Measure



I am trying to create a calculated measure which is converting a char value to a decimal. I have the following expression:

([Measures].[Base Qty Sold - Detail]/9) * Val([Items].[Item Category5])

I would like to convert the value in Item Category5 which is a char data type to a decimal. The result appears as 0.

Help please.


If "Base Qty Sold - Detail" is an integer then the integer division by 9 will return 0 for any value less than 9. So if "Base Qty Sold" = 14 and Detail = 7 the result of subtraction is 7 and then 7 / 9 = 0 with remainder 7. Of course multiplying any value by 0 results in 0.
So I think you need to work on a different approach to the problem.


The base quantity sold is a varchar field and if the value being divided os less than 9 then it should return a decimal which should be multiplied by the user category value 5 field.