I have a cube with several measures representing elapsed time in minutes. I
am presenting these measures in an pivot table office web component. I would
like to format elapsed time in the HH:MM:SS format. Can anyone recommend the
best way to tackle this problem? I presume that I'll need a combination of VB
script and MDX but I'm not sure how to go about it. Any tips or suggestions
would be greatly appreciated.
--
Thanks, Ken

One way would be to set up the "HH:MM:SS" format on the cube measures,
and set the "UseProviderFormatting" property of the OWC, so that it uses
cube formatted values:

http://support.microsoft.com/default...b;en-us;318122

The Microsoft Office XP PivotTable component supports two undocumented
properties: UseProviderFormatting and DisplayCellColor. These properties
allow you to use server formatting for cells and totals in a PivotTable
view.

• When you set the UseProviderFormatting property to True, the
PivotTable component applies number formats that are stored on the
server to the totals that appear in the PivotTable view. The default
setting for this property is False.
...

http://groups.google.com/group/micro...olap/msg/947b1
2d2b1929a08


http://groups-beta.google.com/group/...lserver.olap/b...
3c6da4180c10




Newsgroups: microsoft.public.sqlserver.olap
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 ***