I creating a cube measuring the speed of service of customers using our
drive-thru window. The data from the source shows the total number of
seconds (Ex. 200). I need to display in the cube the number in minutes
and second (Ex. 3:20). How do I do that?

This earlier thread provides some solutions:

http://groups-beta.google.com/group/...rver.olap/brow
se_frm/thread/eea666208373f897/86c73c6da4180c10?q=timeserial&rnum=1#86c7
3c6da4180c10
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com> Date: Sat, 05 Feb 2005
18:59:01 -0800
Subject: Re: How to format & display a measure which is in seconds to
HH:MM:SS

This MSDN link discusses FORMAT_STRING options for cube measures:

http://msdn.microsoft.com/libr*ary/d...y/e*n-us/olapd
ma
d/agmdxadvanced_2aur.asp

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

http://groups-beta.google.com/*group...serv*er.olap/m.
..

From: Deepak Puri (d... (AT) progressive (DOT) com)
Subject: Time format Measure
This is the only article in this thread
View: Original Format
Newsgroups: microsoft.public.sqlserver.ola*p
Date: 2004-04-24 21:53:31 PST

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


With Member [Measures].[FormattedASA] as
'TimeSerial(0,0,[Measures].[Av*gSpeedAnswer])',
FORMAT_STRING = 'hh:nn:ss'


Ohjoo Kwon Feb 6, 11:03 am show options

Newsgroups: microsoft.public.sqlserver.olap
From: "Ohjoo Kwon" <ojk... (AT) olap (DOT) co.kr> - Find messages by this author
Date: Mon, 7 Feb 2005 01:03:16 +0900
Local: Sun,Feb 6 2005 11:03 am
Subject: Re: How to format & display a measure which is in seconds to
HH:MM:SS
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Siva,


If you mean "elapsed time in seconds", its format is not so easy and you
have to cosider more things because it does not point just "a point in
time".


If the elaspsed time is less than 24 hours, the next format is
sufficinet.


WITH
MEMBER Measures.Elasped AS '<Seconds> * 1/60/60/24', FORMAT_STRING =
'hh:mm:ss'


If the elaspsed time is equal to or more than 24 hours, the elapsed days
must be also considered.


WITH
MEMBER Measures.hhmmss AS '<Seconds> * 1/60/60/24'
MEMBER Measures.days AS 'DateDiff("d", CDate(0),CDate(hhmmss))'
MEMBER Measures.Elapsed AS 'CStr(days) + " Days " + Format(hhmmss,
"hh:mm:ss")'


If your final format is more complicate, you also consider using your
own
User Defined Function.


Ohjoo Kwon
www.olapforum.com
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***