How to get the value of the hour part of datetime

[marcdan]

Hi,
I'm trying to calculate the total amount of the calls durations. I managed already to get sum of every call, and I get quite pretty result, for example:
2 days 3:43:23.

What I want to get, is to present the result in form:
51:43:23.

The way, I get the first result is:
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Test]
AS [MEASURES].[Call Dur],
FORMAT_STRING = IIF ([MEASURES].[Call Dur] < 1, 'hh:mm:ss',
'"' + cstr(int([MEASURES].[Call Dur])) + 'days "hh:mm:ss'),
NON_EMPTY_BEHAVIOR = { [Call Dur] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Call Entry' ;

I was trying using cint(hh) or cint('hh') inside the cstr expression, but this ends up with an error.

Does anybody has any idea, how to do this?
Thanks for any ideas.

 

[MDXer]

You need to invoke the VBA functionality Using VBA!Function().

 

[marcdan]

Could you tell me, what should I pass as a parameter to that function? The transformation is done in a format_string property, and I have no idea, how to get an unformatted value of the time.

 

[marcdan]

Thank for your response, but unfortunatelly I still can't get it working. Trying get the number of hours using VBA!Hour() function seems not work.
Could you paste here a sample usage of the function in that context?

Thank in advance
Marcin Danek

 

[MDXer]

How you would get the vallue greatly depends on what info is offered in the cube. If you have a call start and a call end you would be better served calculating call duration outside of the cube and then just having the call duration be a sum of your records.

 

[marcdan]

Hi,
thanks for your answer.
Its exactly, what I have - the only one column with duration time. It's in a database as a filed of type time.
During creating a cube, I created new calculated field, which is just this duration converted to a single, so that I could perform calculations on it.
And what I want to do, is to display the total sum of call durations in a report or during browsing a cube. And that's the point, where I have a problem.

 

[MDXer]

what is the underlying database?

 

[marcdan]

SQL Server 2008 Enterprise.