Hi, I tried to format a potential multiple days spanning duration like
this:

CREATE MEMBER CURRENTCUBE.[MEASURES].FormatedDuration
AS IIF( [MEASURES].[Days] > 1,
Format( [MEASURES].[Days], '# Day(s)') + Format(
[MEASURES].[Remainder], ' hh:mm:ss'),
Format( [MEASURES].[Remainder], " hh:mm:ss")
),
VISIBLE = 1 ;

while [days] is calculated from [hhmmss], which is storing the duration
in seconds as an int, like this

CREATE MEMBER CURRENTCUBE.[MEASURES].[Days]
AS
DateDiff("d", CDate(0),CDate([MEASURES].[hhmmss])),
VISIBLE = 1;

and [Remainder]

CREATE MEMBER CURRENTCUBE.[MEASURES].[Remainder]
AS
[MEASURES].[hhmmss] - DateDiff("d",
CDate(0),CDate([MEASURES].[hhmmss])),
VISIBLE = 1;

The problem:
looks so far fairly standard, but the output of the measure
[FormatedDuration] in any olap-client like excel, owc or reportportal
looks like this:

1 day(s) hh:mm:ss

Seemingly the hh:mm:ss part is just carried over as a string. Replacing
it with hh:nn:ss or using double quotation marks hasn't change the
result. Using " Short Time" or similar formating expressions give me
something like this:

1 day(s) 0,56787

The value in the [Remainder] member are always [0 < [Remainder] < 1] as
a float and in [Days] the range is 0 to x as ints. This is (not)
running on AS2005 on top of a german WinServer2003 with german Win XP
clients.

Any ideas? I really run out of ideas regarding this problem lately....


Ves, it is an repost of microsoft.public.sqlserver.datawarehouse. There
was not to much activity lately... sorry!

I think you will find that the hh:mm:ss format only works with date data
types. Try using CDate() on the remainder measure before formatting it.

eg

CREATE MEMBER CURRENTCUBE.[MEASURES].FormatedDuration
AS IIF( [MEASURES].[Days] > 1,
Format( [MEASURES].[Days], '# Day(s)') + Format(
CDate([MEASURES].[Remainder]), ' hh:mm:ss'),
Format( CDate([MEASURES].[Remainder]), " hh:mm:ss")
),
VISIBLE = 1 ;

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1145531233.562089.222820 (AT) j33g2000cwa (DOT) googlegroups.com>,
benjamin.kramm (AT) entory (DOT) com says...