Try this in Criteria Tab->Column->fx
cast(REPLACE('1M', 'M', '') as int)*1000000
Column Properties->Data Format->Check for 'Override Default Data Format' and then Check 'Use 1000's Separator'
If helps pls mark
Edited by: Srini VEERAVALLI on Jan 23, 2013 7:18 PM
Then column/1000000 and then cast it to char and concat with M
cast(column/1000000 as char) || 'M'
If required use as below
replace(cast(1000000/1000000 as char)||'M',' ','')
this would avoid space between 1 and M
Edited by: Srini VEERAVALLI on Jan 23, 2013 7:55 PM
I did cast(ceiling(round(1520300/1000000, 0)) as char) got result as 2.00 not 2
I do not want to show 2.00M, I want to show 2M.
how comes when ceiling(round(1520300/1000000, 0)) , got result as 2
but when cast(ceiling(round(1520300/1000000,0)) as char) got result as 2.00
substring('2.00' ,1,POSITION('.' IN '2.00' )-1)||'M'
Replace '2.00' with your column something like 1520300/1000000 or with round or ceiling
Edited by: Srini VEERAVALLI on Jan 23, 2013 8:42 PM
it works, then another problem comes, I actually want to show data label in chart as number & 'M', but when format values this way in criteria, it cannot produce charts, is there anyway to show data label in the chart as number plus 'M'?
I found the correct way to do this:
In criteria tab, right click measure column, column properties, data format, tick 'override default format', choose treat numbers as 'custom', custom numeric format '##M'