We have a column, SETUP_TIME varchar[20] in the fact table in SQL
Server 2000. This has values like '01:20:30.900', '03:30.800', etc.
The hour and minute parts are present only if they are non-zero. We
would like to create measures, in an OLAP cube in AS 2000, Total setup
time and Mean setup time, and would like to display them in the same
format as 'HH:MM:SS.FFF' with time components shown only if they are
present.
Can someone suggest how this can be done?

Thanks

This link discusses FORMAT_STRING options for cube measures, but there
is no mention of milliseconds:

http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agmdxadvanced_2aur.asp


This earlier post shows how to format hh:mm:ss

http://groups.google.com/groups?q=pu...r=&selm=3e1a01
c42a81%2406228590%24a501280a%40phx.gbl&rnum=2
Subject: Time format Measure
This is the only article in this thread
View: Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-04-24 21:53:31 PST


Try the following format, if time is < 32K seconds:

'TimeSerial(0,0,[Measures].[AvgSpeedAnswer])',
FORMAT_STRING = 'hh:nn:ss'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!